QBSelect case with and

[Richard1521662995]

HI,

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.

[patrick]

I am having a bit of a hard time reading your code sample, but the basic idea would be this:

Code: Select all

//get a hold of your joins for better readability
var jPresentation = sqlQuery.joins.si_detail_to_presentation;
var jSiHeader = sqlQuery.joins.si_detail_to_si_header;
var jStock = sqlQuery.joins.si_detail_to_stock;

Code: Select all

//add the case
sqlQuery.result.add(
   sqlQuery.case
      .when(sqlQuery.and.add(jPresentation.columns.process_commoditycode.eq(1)).add(jSiHeader.columns.frozenorder.eq(1))).then(jStock.process_intrastatfrozen)
      .when(sqlQuery.and.add(jPresentation.columns.process_commoditycode.eq(1)).add(jSiHeader.columns.frozenorder.eq(0))).then(jStock.process_intrastatfresh)
      .when(sqlQuery.and.add(jPresentation.columns.process_commoditycode.eq(0)).add(jSiHeader.columns.frozenorder.eq(1))).then(jStock.intrastatfrozen)
      .else(jStock.columns.intrastatfresh)
);

Does this help or work?

To see the statement generated, go to servoy-admin and clear the performance data before this is fired and the check again after it is executed. Then you see the query actually fired towards the database.

[Richard1521662995]

Hi Patrick,

Thanks that works , just needed .columns after the JStock.

[patrick]

Oh, yes. Missed that. Forum is not a code editor.