This topic contains 3 replies, has 0 voices, and was last updated by michoel 7 years, 9 months ago.

  • Author
    Posts
  • #4954

    rbhancock

    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.

  • #4955

    Olivier Gagnon NC

    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.

  • #4956

    rbhancock

    Unfortunately, this statement does not work.

  • #4957

    michoel

    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

You must be logged in to reply to this topic.