[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

[farrago Contributions: 0Level 1☆]

I need a text "availability flag" for a feed. I need it in a custom text item field. Any reason why this isn't working….CASE

WHEN {isdropshipitem} = 'T' THEN 'in stock'

WHEN type = 'Kit/Package' THEN 'in stock'

WHEN {quantityonhand} > 1 THEN 'in stock'

ELSE 'out of stock'

END

It always says ERROR: Field 'quantityonhand' Not Found.

[michoel Contributions: 0Level 1☆]

Originally posted by farrago

View Post

It always says ERROR: Field 'quantityonhand' Not Found.

"quantityonhand" is a search field not a record field. The record field is undocumented, but it's called "totalquantityonhand".

Also you are missing curly brackets around the type field.

Code:
CASE WHEN {isdropshipitem} = 'T' OR {type} = 'Kit/Package' OR {totalquantityonhand} > 1 THEN 'in stock' ELSE 'out of stock' END

[farrago Contributions: 0Level 1☆]

Using totalquantityonhand was the ticket. How do you find out about these undocumented fields? In here… I guess!

[khultquist Contributions: 0Level 1☆]

Originally posted by farrago

View Post

How do you find out about these undocumented fields

I use Michoel's Chrome extension Netsuite Field Explorer it's awesome and worth the price!

[michoel Contributions: 0Level 1☆]

Originally posted by khultquist

View Post

it's awesome and worth the price!

Thanks for the feedback, though I hope you are not paying for it!

[Author]

Posts