sql variable, CASE

[juergenkemeter]

Hi,
I have the following SQL Statement which gives back the Actual Substance Quantity(DECIMAL), e.g. 33.00 [milligram]:

------------------------------------------------------------
Select X.REPOMEN_SUM - Y.REENTMEN_SUM
from
(Select RECHPFID, sum(REPOMEN) REPOMEN_SUM from HILFSSTO.REBEW group by RECHPFID) X,
(Select RECHPFID, sum(REPOMEN)REENTMEN_SUM from HILFSSTO.REBEW WHERE RECHPFID = [ChargenpflegeID] AND REENTNDAT is not NULL group by RECHPFID) Y
where X.RECHPFID = Y.RECHPFID
and X.RECHPFID = [ChargenpflegeID]
------------------------------------------------------------

Now I want to have a Notification in a Field if the Substance Quantity goes below or equals a certain lower boundary, say 20 [milligram]. The Field Output should be something like 'Substance Quantity OK', if the Actual Quantity is above 20, and 'Alert, Substance Quantity too low', if below or equal 20.

Therefore I think I best store the Output of the upper SQL Statement in a Variable and then compare it with a CASE- Structure.

Here is my try:
------------------------------------------------------------
Select X.REPOMEN_SUM - Y.REENTMEN_SUM
from
(Select RECHPFID, sum(REPOMEN) REPOMEN_SUM from HILFSSTO.REBEW group by RECHPFID) X,
(Select RECHPFID, sum(REPOMEN)REENTMEN_SUM from HILFSSTO.REBEW WHERE RECHPFID = [ChargenpflegeID] AND REENTNDAT is not NULL group by RECHPFID) Y
where X.RECHPFID = Y.RECHPFID
and X.RECHPFID = [ChargenpflegeID]

AS Kontrolle
CASE WHEN Kontrolle < 20
THEN 'Alert, Substance Quantity too low'
ELSE 'Substance Quantity OK'
END CASE
FROM HILFSSTO.REBEW
------------------------------------------------------------

which gives back the Message

"Keyword AS not expected. Valid tokens: FOR WITH FETCH ORDER UNION OPTIMIZE.
(SQL code = -199, SQL state = 42601)"


Thanks for any help,
Juergen

 

[PHV]

Have you tried this ?
Select X.REPOMEN_SUM - Y.REENTMEN_SUM AS Kontrolle,
CASE WHEN X.REPOMEN_SUM - Y.REENTMEN_SUM < 20
THEN 'Alert, Substance Quantity too low'
ELSE 'Substance Quantity OK'
END CASE
from
(Select RECHPFID, sum(REPOMEN) REPOMEN_SUM from HILFSSTO.REBEW group by RECHPFID) X,
(Select RECHPFID, sum(REPOMEN)REENTMEN_SUM from HILFSSTO.REBEW WHERE RECHPFID = [ChargenpflegeID] AND REENTNDAT is not NULL group by RECHPFID) Y
where X.RECHPFID = Y.RECHPFID
and X.RECHPFID = [ChargenpflegeID]


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244