Need Help With SQL

Posted in 2013

MOHAMMAD IRFAN — Sun, 31 Mar 2013 16:47:42 — source: IIUG Forums & Mailing Lists

Hi, I have an SQL statement: UPDATE informix.dv_billing dv SET dv.fee = CASE WHEN (( SELECT dc.mm_discount FROM dv_mm_disc dc WHERE dc.year = YEAR(dv.tdate) AND dc.quarter = quarter(dv.tdate) AND dc.bid=dv.bid AND dc.mm_code=dv.pcode and dc.ins=dv.ins)=0) THEN dv.fee ELSE 0 END WHERE MDY(MONTH('2012-04-05'), 1, YEAR('2012-04-05')) - 2 UNITS MONTH and last_day(DATE('2012-04-05')) ; The SQL returns: Result of a boolean expression is not of boolean type. What could be wrong? Thanks.

↪ replying to MOHAMMAD IRFAN

Art Kagel — Sun, 31 Mar 2013 17:07:08 — source: IIUG Forums & Mailing Lists

Try this: UPDATE informix.dv_billing dv SET dv.fee = CASE WHEN ((( SELECT dc.mm_discount FROM dv_mm_disc dc WHERE dc.year = YEAR(dv.tdate) AND dc.quarter = quarter(dv.tdate) AND dc.bid=dv.bid AND dc.mm_code=dv.pcode AND dc.ins=dv.ins)=0)::BOOLEAN) THEN dv.fee ELSE 0 END WHERE MDY(MONTH('2012-04-05'), 1, YEAR('2012-04-05')) - 2 UNITS MONTH and last_day(DATE('2012-04-05')) ; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Mar 31, 2013 at 12:47 PM, MOHAMMAD IRFAN <irfan199@yahoo.com> wrote: > Hi, I have an SQL statement: > UPDATE informix.dv_billing dv > SET dv.fee = CASE WHEN (( SELECT dc.mm_discount > > FROM dv_mm_disc dc > > WHERE dc.year = YEAR(dv.tdate) AND dc.quarter = quarter(dv.tdate) AND > dc.bid=dv.bid AND dc.mm_code=dv.pcode and dc.ins=dv.ins)=0) THEN dv.fee > ELSE 0 > END > WHERE MDY(MONTH('2012-04-05'), 1, YEAR('2012-04-05')) - 2 UNITS MONTH and > last_day(DATE('2012-04-05')) > ; > > The SQL returns: > Result of a boolean expression is not of boolean type. What could be wrong? > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01161a947dcd1a04d93b8833

↪ replying to Art Kagel

MOHAMMAD IRFAN — Mon, 01 Apr 2013 15:53:43 — source: IIUG Forums & Mailing Lists

The problem is I forgot to put column comparison on where clause. The statement is changed to: UPDATE informix.dv_billing dv SET dv.fee = CASE WHEN (( SELECT dc.mm_discount FROM dv_mm_disc dc WHERE dc.year = YEAR(dv.tradedate) AND dc.quarter = quarter(dv.tradedate) AND dc.boardid=dv.boardid AND dc.mm_code=dv.participantcode AND dc.instrument=dv.instrid)=0) THEN dv.fee ELSE 0 END WHERE dv.tradedate BETWEEN MDY(MONTH('2012-04-05'), 1, YEAR('2012-04-05')) - 2 UNITS MONTH AND last_day(DATE('2012-04-05')) ;