Servoy Forum

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.

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.

Hi Patrick,

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

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