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.

 SELECT distinct
            %EXACT(Hospital_DR ),
            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 ,
           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
 WHERE LOCType = 'W'
 AND (({fn CONCAT( ',', {fn CONCAT((:Wardx),',')})} [ {fn CONCAT( ',', {fn CONCAT((WARDLocation ),',')})}) OR (:Wardx IS NULL))

  .do TotalRegular
  .do TotalOccupied
  .do TotalReserved
 do TotalOccupancy

0 0 2 50


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