Field created from case statement / SQL0312

deanclowe@nf.sympatico.ca · *nix forums beginner Joined: 18 Feb 2005 Posts: 2

Thanks Serge,

That's exactly what I was looking for.

Dean

Serge Rielau · *nix forums Guru Joined: 29 Apr 2005 Posts: 1583

deanclowe@nf.sympatico.ca · *nix forums beginner Joined: 18 Feb 2005 Posts: 2

Hi I have a query that is trying to use a field that is created "AS"
based on a case statement like this:

SELECT
CASE
WHEN IPOVH = 'Y' THEN '01'
WHEN IPHSI = 'Y' THEN '02'
WHEN IPVIS = 'Y' THEN '03'
ELSE ' '
END AS CRIT_CODE,

Func_Retreive_GenericTableDescription('CRIT', CRIT_CODE)
AS STAT_DESC

FROM ..... and so on...

But it doesn't seem to want to let me use CRIT_CODE in my function
call. (This runs on iSeries V5R3 and is part of a stored procedure).
Same thing happens when I try to use CRIT_CODE in a join. Is there
some way I can use the result of this case statement in
functions/joins?

Currently I get error SQL0312 Variable CRIT_CODE not defined or not
usable.

Google