Cashe-SQL cursor
the report generates statics for each ward and i want to divide specific ward 5c into two wards, the first 12 beds are for 5c - pediatric and the last 12 beds are for 5c-adults.
the report takes input from the user by selecting the wards so if the user chooses 5c, the report should show 2 wards.
the report declare cursor at the begining so how can i divide the ward.
SQL(DECLARE WARDL CURSOR FOR
SELECT distinct
%EXACT(Hospital_DR ),
HOSPDesc,
WARDRowID,
WARDCode,
case
WHEN WARDDesc='Ward 2D - Surgery' THEN '2D'
WHEN WARDDesc='Ward 2A - Medical' THEN '2A'
WHEN WARDDesc='Ward 5C - Surgey' THEN '5C'
END AS WardDesc,
LOCRowID ,
LOCCode ,
LOCDesc,
case
WHEN WARDDesc='Ward 2D - Surgery' THEN 'Surgery
'
WHEN WARDDesc='Ward 2A - Medical' THEN 'Medical
'
WHEN WARDDesc='Ward 5C - Surgery' THEN 'Surgery
'
END AS Service,
INTO :HRowId,:HDesc,:WARDId,:WARDCode,:WARDDesc,:LOCId,:LOCCode,:LOCDesc, :Service
FROM PBed
WHERE LOCType = 'W'
AND (({fn CONCAT( ',', {fn CONCAT((:Wardx),',')})} [ {fn CONCAT( ',', {fn CONCAT((WARDLocation ),',')})}) OR (:Wardx IS NULL))
)
&SQL(OPEN WARDL)
f &SQL(FETCH WARDL) q:SQLCODE'=0 d
.do TotalRegular
.do TotalOccupied
.do TotalReserved
&SQL(CLOSE WARDL)
do TotalOccupancy
How do you define "first 12 beds"? Is that based on some identifier or other field in the database? Otherwise %VID may help you.
FWIW: We're currently working on a more comprehensive implementation of window functions and ROW_NUMBER(), but that'll be for a release probably around this summer.
The name of first 12 beds ends with pediatric and the name of last 12 beds ends with adults so i can differentiate between them using name of bed