I am trying get a QBSelect to work with an and in a case statement. if i look at the Sql that is created with databaseManager.getSQL() the and part is totally ignored
- Code: Select all
sqlQuery.result.add(sqlQuery.case
.when(sqlQuery.joins.si_detail_to_presentation.columns.process_commoditycode.eq(1)
.root.and.add(sqlQuery.joins.si_detail_to_si_header.columns.frozenorder.eq(1)))
.then(sqlQuery.joins.si_detail_to_stock.columns.process_intrastatfrozen)
.when(sqlQuery.joins.si_detail_to_presentation.columns.process_commoditycode.eq(1)
.root.and.add(sqlQuery.joins.si_detail_to_si_header.columns.frozenorder.eq(0)))
.then(sqlQuery.joins.si_detail_to_stock.columns.process_intrastatfresh)
.when(sqlQuery.joins.si_detail_to_si_header.columns.frozenorder.eq(1))
.then( sqlQuery.joins.si_detail_to_stock.columns.intrastatfrozen)
.elseValue(sqlQuery.joins.si_detail_to_stock.columns.intrastatfresh), 'commoditycode')
I am trying to reproduce the following
- Code: Select all
case
when presentation.process_commoditycode = 1 and si_header.frozenorder = 1 then stock.process_intrastatfrozen
when presentation.process_commoditycode = 1 and si_header.frozenorder = 0 then stock.process_intrastatfresh
when presentation.process_commoditycode = 0 and si_header.frozenorder = 1 then stock.intrastatfrozen
else stock.intrastatfresh
end as commoditycode
any help greatly appreciated.
Thanks.