Question
raghad f · Jan 10, 2021

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

00
0 0 2 74
Log in or sign up to continue

Replies

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