[rbhancock Contributions: 0Level 1☆]

Can anyone help with the SQL syntax of this statement:

CASE WHEN {quantityavailable} = 0 and {outofstockbehavior} = ‘Remove item when out-of-stock’ THEN ‘discontinued’ WHEN {quantityavailable} = 0 and {outofstockbehavior} = ‘Allow back orders but display out-of-stock message’ THEN ‘pre-order’ WHEN {quantityavailable} > 5 THEN ‘In Stock’ ELSE ‘Out of Stock’ END

I am pounding sand, as it keeps erroring.

Many thanks,
This is a cached copy. Click here to see the original post.

[Olivier Gagnon NC Contributions: 0Level 1☆]

Try this:

CASE WHEN {quantityavailable} = 0 and {outofstockbehavior} = ‘Remove item when out-of-stock’ THEN ‘discontinued’ ELSE (CASE WHEN {quantityavailable} = 0 and {outofstockbehavior} = ‘Allow back orders but display out-of-stock message’ THEN ‘pre-order’ ELSE (CASE WHEN {quantityavailable} > 5 THEN ‘In Stock’ ELSE ‘Out of Stock’ END) END) END

Formula could be optimized more, but, meh.

[rbhancock Contributions: 0Level 1☆]

Unfortunately, this statement does not work.

[michoel Contributions: 0Level 1☆]

This part looks suspicious to me – you have a different style opening quotation mark to the closing one. In general I don’t think you can use curly style brackets in SQL.

‘discontinued’

Try this version:

Code:
CASE
WHEN {quantityavailable} = 0 AND {outofstockbehavior} = ‘Remove item when out-of-stock’ THEN ‘discontinued’
WHEN {quantityavailable} = 0 AND {outofstockbehavior} = ‘Allow back orders but display out-of-stock message’ THEN ‘pre-order’
WHEN {quantityavailable} > 5 THEN ‘In Stock’
ELSE ‘Out of Stock’
END

[Author]

Posts