Re: IF in sql -- RPG400-L

I think that's because in your case you haven't put an else condition.
Make the change and see what the results are.
SELECT VC00.VCNCV, TM00.TMNOML,VC00.VCDTCV, VC00.VCCDIV, case when
VCESPC='S' then 'P' else WHAT end FROM VC00, TM00, VC00E WHERE
VC00.VCESTA='A' and VC00.VCCCLI=TMCTER and VC00.VCCDIV='USD' ORDER
BY VC00.VCNCV

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill

rpg400-l-bounces@xxxxxxxxxxxx wrote on 03/18/2008 11:16:19 AM:

Thanks Alan, when I do a single table query, the result is ok:
SELECT VCNCV,VCDTCV,VCCDIV,
case when VCCDIV='USD' then '$' else
' ' end FROM VC00 WHERE VCESTA='A' and VCCDIV='USD'
ORDER BY VCNCV

RESULT:
Nº CV Im.total CV divisas C.Divisa Expresión CASE
30.413 238.207,10 USD $
30.541 175.527,00 USD $
30.566 88.299,40 USD $
30.582 111.939,50 USD $
30.585 72.144,04 USD $
30.604 70.966,07 USD $
30.605 120.401,90 USD $
30.620 95.722,00 USD $
30.621 95.722,00 USD $

But when include other table,

SELECT VC00.VCNCV, TM00.TMNOML,VC00.VCDTCV, VC00.VCCDIV, case when
VCESPC='S' then 'P' end FROM VC00, TM00, VC00E WHERE
VC00.VCESTA='A' and VC00.VCCCLI=TMCTER and VC00.VCCDIV='USD' ORDER
BY VC00.VCNCV

The querry returns a few lines with the same information.

RESULT:

Nº CV Nombre interno Im.total CV divisas C.DivisaExpresiónCASE
30.655 xxxxxxxxxxxxxxxx ,00 USD -
30.655 xxxxxxxxxxxxxxxx ,00 USD -
30.655 xxxxxxxxxxxxxxxx ,00 USD -
30.655 xxxxxxxxxxxxxxxx ,00 USD -
30.655 xxxxxxxxxxxxxxxx ,00 USD -
30.655 xxxxxxxxxxxxxxxx ,00 USD P
30.655 xxxxxxxxxxxxxxxx ,00 USD P
30.655 xxxxxxxxxxxxxxxx ,00 USD P
30.655 xxxxxxxxxxxxxxxx ,00 USD -

How I must to do it?

Regards.

Alan Shore escribió:

Actually you were very close
select VCEPIN,
case
when VCELIN=10 then 'P'
when VCELIN=11 then 'O'
else VCELIN
end
from VC00

Alan Shore

NBTY, Inc
(631) 244-2000 ext. 5019
AShore@xxxxxxxx
"If you're going through Hell, keep going" - Winston Churchill
rpg400-l-bounces@xxxxxxxxxxxx wrote on 03/18/2008 07:38:23 AM:

I need to include in a sql query a condition, example:

select VCEPIN, VCELIN(if VCELIN=10 then VCELIN='P') from VC00

or including the case sentence:

select VCEPIN, VCELIN(case when VCELIN=10 then VCELIN='P' when

VCELIN=11

then VCELIN='O') from VC00

Is posible?

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.

--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing

list

To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.