RE: IF in sql

I think the problem is no WHERE clause defining how VCOO, TMOO and VCODE are
related

SELECT VC00.VCNCV, TM00.TMNOML,VC00.VCDTCV, VC00.VCCDIV,
CASE WHEN VCESPC='S' THEN 'P'
END
FROM VC00, TM00, VC00E
WHERE VC00.<key> = TMOO.<key>
AND TMOO.<key> = VCOOE.<key>
AND VC00.VCESTA='A'
AND VC00.VCCCLI=TMCTER
AND VC00.VCCDIV='USD'
ORDER BY VC00.VCNCV

Rob
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of pepitovadecurt
Sent: March 18, 2008 11:16 AM
To: rpg400-l@xxxxxxxxxxxx
Subject: Re: IF in sql

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.