Written by

Question Swathi Chilukuri · Mar 19, 2020

Cache sql syntax and meaning

#Caché #SQL

Hi ,

I have a code written in cache sql and trying to understand it, below is the code , can anyone help me understand what does that mean

ex - !! dosage_unit !!

Thank You in advance.

Discussion (12)0

Add reply

Comments

Warlin Garcia · Mar 19, 2020

Never seen that before. Are you sure those are ! and not ||? the latter makes sense as it's the concatenate operator.

0 0

Swathi Chilukuri Mar 19, 2020 to Warlin Garcia

Hi Garcia,

The code says !!, I have never seen it too so that is the reason not understanding what it means.

0 0

Vitaliy Serdtsev Mar 19, 2020 to Swathi Chilukuri

I think you didn't copy all the code. Take a screenshot.

0 0

Swathi Chilukuri Mar 19, 2020 to Vitaliy Serdtsev

full code

SELECT (CASE WHEN (order_description IS NULL OR (order_description='')) THEN ''
ELSE (order_description) END) !!
(CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN ''
ELSE (', ' !! dosage !! ' ' !! dosage_unit !!
(CASE WHEN (dosage_form IS NULL OR (dosage_form='')) THEN ''
ELSE (' ' !! dosage_form) END) !! '') END) !!
(CASE WHEN (administration_route IS NULL OR (administration_route='')) THEN ''
ELSE (', ' !! administration_route) END) !!
(CASE WHEN (frequency_description IS NULL OR (frequency_description='')) THEN ''
ELSE (', ' !! (CASE WHEN (quantity IS NULL OR quantity_unit IS NULL OR
(quantity='') OR (quantity_unit='')) THEN ''
ELSE (quantity !! ' ' !! quantity_unit !! ' ') END) !! frequency_description) END) !!
(CASE WHEN start_date IS NULL THEN '' ELSE (', ' !! 'Start Date: ' !!
CONVERT(SQL_DATE,start_date,101)) END) As MedicationOrder
FROM CWSOrderEntry.history_client_order
WHERE PATID='100068'
--AND FACILITY=?FACILITY
AND order_status_code='A'
AND order_type_category_code='P'

0 0

Vitaliy Serdtsev Mar 19, 2020 to Swathi Chilukuri

Symbols Used in Caché SQL

SELECT 
(CASE WHEN (order_description IS NULL OR (order_description='')) THEN '' ELSE (order_description) END) ||
(CASE WHEN (dosage IS NULL OR dosage_unit IS NULL OR (dosage='') OR (dosage_unit='')) THEN ''
ELSE (', ' || dosage || ' ' || dosage_unit || (CASE WHEN (dosage_form IS NULL OR (dosage_form='')) THEN '' ELSE (' ' || dosage_form) END) || '') END) ||
(CASE WHEN (administration_route IS NULL OR (administration_route='')) THEN '' ELSE (', ' || administration_route) END) ||
(CASE WHEN (frequency_description IS NULL OR (frequency_description='')) THEN '' ELSE (', ' || (CASE WHEN (quantity IS NULL OR quantity_unit IS NULL OR (quantity='') OR (quantity_unit='')) THEN '' ELSE (quantity || ' ' || quantity_unit || ' ') END) || frequency_description) END) ||
(CASE WHEN start_date IS NULL THEN '' ELSE (', ' || 'Start Date: ' || CONVERT(SQL_DATE,start_date,101)) END) As MedicationOrder
FROM CWSOrderEntry.history_client_order
WHERE PATID='100068'
--AND FACILITY=?FACILITY
AND order_status_code='A'
AND order_type_category_code='P'

Try changing the font, it's possible that you just have "||" displayed as "!!".

0 0

Vitaliy Serdtsev Mar 19, 2020 to Vitaliy Serdtsev

SELECT 

NVL(order_description,'') ||

CASE WHEN NVL(dosage,'')='' OR NVL(dosage_unit,'')='' THEN ''
     ELSE ', ' || dosage || ' ' || dosage_unit || CASE WHEN NVL(dosage_form,'')='' THEN ''
                                                       ELSE ' ' || dosage_form
                                                  END || ''
END ||

CASE WHEN NVL(administration_route,'')='' THEN ''
     ELSE ', ' || administration_route
END ||

CASE WHEN NVL(frequency_description,'')='' THEN ''
     ELSE ', ' || CASE WHEN NVL(quantity,'')='' OR NVL(quantity_unit,'')='' THEN ''
                       ELSE quantity || ' ' || quantity_unit || ' '
                  END || frequency_description
END ||

IFNULL(start_date,'',', ' || 'Start Date: ' || CONVERT(SQL_DATE,start_date,101)) As MedicationOrder

FROM CWSOrderEntry.history_client_order
WHERE PATID='100068'
--AND FACILITY=?FACILITY
AND order_status_code='A'
AND order_type_category_code='P'

0 0

Swathi Chilukuri Mar 19, 2020 to Vitaliy Serdtsev

I can definitely try that but what does

CASE WHEN NVL(quantity,'')='' OR NVL(quantity_unit,'')='' THEN ''
                       ELSE quantity || ' ' || quantity_unit || ' '
                  END || frequency_description
END || mean?

What does those symbols represent, I am new to cache so trying to learn. Thank You

0 0

Warlin Garcia Mar 19, 2020 to Swathi Chilukuri

The "||" is used to concatenate. So that's why it makes sense those are the ones used in the SQL you pasted instead of "!!".

0 0

Swathi Chilukuri Mar 19, 2020 to Warlin Garcia

Thank You so much.

0 0

Kevin Chan · Mar 19, 2020

Is this Dynamic or Embedded SQL?

I am trying to run this in the server management portal and it does not work with a stub table.

0 0

Swathi Chilukuri Mar 19, 2020 to Kevin Chan

Hi Kevin,

It is used as part of widget code in My Avatar.

0 0

Swathi Chilukuri Mar 19, 2020 to Swathi Chilukuri

full code

0 0