Need Help With a Case Statement (1 Viewer)

[krazykasper]

Based on criteria in FIELD A {SalesOrderPartsSupplies.MaterialId}, I need to replace the value in FIELD B {SalesOrderPartsSupplies.PurchasePrice} with a fixed dollar amount. For some reason my report does not show new dollar amount.
My Case Statement follows:
SELECT {SalesOrderPartsSupplies.MaterialID}
CASE "B525001843"
: {SalesOrderPartsSupplies.PurchasePrice} = 9999.00
CASE "4511100305", "4511100306"
: {SalesOrderPartsSupplies.PurchasePrice} = 660.00
CASE "4511100301", "4511100302"
: {SalesOrderPartsSupplies.PurchasePrice} = 495.00
CASE "4511100300", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "4511100055", "4511100068", "4511100077"
: {SalesOrderPartsSupplies.PurchasePrice} = 330.00

 

[boblarson]

So Material ID (i.e. B525001843) is a text value and not a number?

 

[Mile-O]

You error is that you are trying to place a value into a field, where the result should be of the formula.

i.e. remove {SalesOrderPartsSupplies.PurchasePrice} = from each Case.

[COLOR="Blue"]Select[/COLOR] {SalesOrderPartsSupplies.MaterialID} 
    [COLOR="Blue"]Case[/COLOR] "B525001843":  9999.00
    [COLOR="Blue"]Case[/COLOR] "4511100305", "4511100306": 660.00
    [COLOR="Blue"]Case[/COLOR] "4511100301", "4511100302": 495.00
    [COLOR="Blue"]Case[/COLOR] "4511100300", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "4511100055", "4511100068", "4511100077": 330.00
    [COLOR="Blue"]default[/COLOR]: 0
;

 

[krazykasper]

TO: SJ McAbney

Not sure if I understand you suggestion. The fields "B525001843" are text. The field {SalesOrderPartsSupplies.PurchasePrice} is a numeric, which is where I want the new value (e.g., 660.00). If I remove the field {SalesOrderPartsSupplies.PurchasePrice} from the statement, how it know where to place the numeric amount?

Thanks

 

[krazykasper]

To Bob Larson:

Yes the Material ID is text. The Purchase Price field is numeric.

Thanks,

 

[krazykasper]

Thank You!!

Got it. Thank you very much.
I now realize that after using your solution, I needed to drag the new field onto the report.

Krazy Kasper

You error is that you are trying to place a value into a field, where the result should be of the formula.

i.e. remove {SalesOrderPartsSupplies.PurchasePrice} = from each Case.

[COLOR="Blue"]Select[/COLOR] {SalesOrderPartsSupplies.MaterialID} 
    [COLOR="Blue"]Case[/COLOR] "B525001843":  9999.00
    [COLOR="Blue"]Case[/COLOR] "4511100305", "4511100306": 660.00
    [COLOR="Blue"]Case[/COLOR] "4511100301", "4511100302": 495.00
    [COLOR="Blue"]Case[/COLOR] "4511100300", "4511100015", "4511100020", "4511100027", "4511100037", "4511100045", "4511100055", "4511100068", "4511100077": 330.00
    [COLOR="Blue"]default[/COLOR]: 0
;