QBSelect case with and

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.

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.

Hi Patrick,

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

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