QBSelect case with and

Questions
1

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

	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

	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.

2

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

//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;

//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.

3

Hi Patrick,

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

4

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