Case Statement in Lobby Data Source Designer is not working

Hi,

Does it run as the same user in quick report mode as it does in the lobby?

What error does it actually fail with?

Thanks for the reply. It does run as the same user in quick report mode as it does in lobby data source designer. I don’t get an exact error message. It just says to make sure my columns are configured correctly

I’ll hazard a quick guess - the case statements would work as straight SQL because the column name is worked out on the fly, but the lobby designer may actually need a output field name to work.  Try changing the case statement so that you specify a column name for the output data e.g.

( SELECT
     CASE
         WHEN inventory_transaction_hist.transaction_code = 'OESHIP'
            AND part_catalog_cfv.part_main_group = 'DOMESTIC FG' THEN inventory_transaction_hist.quantity * part_catalog_cfv.cf$_proof_gallons
         WHEN inventory_transaction_hist.transaction_code = 'OESHIP'
            AND part_catalog_cfv.part_main_group = 'EXPORT FG' THEN inventory_transaction_hist.quantity * part_catalog_cfv.cf$_proof_gallons
         WHEN inventory_transaction_hist.transaction_code = 'SHIPTRAN'
            AND part_catalog_cfv.part_main_group = 'DOMESTIC FG' THEN inventory_transaction_hist.quantity * part_catalog_cfv.cf$_proof_gallons
         WHEN inventory_transaction_hist.transaction_code = 'SHIPTRAN'
            AND part_catalog_cfv.part_main_group = 'EXPORT FG' THEN 0
     END as value
   FROM
     inventory_transaction_hist,
     part_catalog_cfv
  WHERE
     inventory_transaction_hist.part_no = part_catalog_cfv.part_no
 )

here I’ve taken your code and specified a field name of “value”.

I thought the same thing at first as well. In the data source designer you define the column name beside the select statements. Thanks again!