Cache sql syntax and meaning

Question

Question

Swathi Chilukuri · Mar 19, 2020

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 !!

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 !!

Thank You in advance.

Discussion (12)1

Log in or sign up to continue

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 "!!".

1 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

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

score 0 · Answer 1 · 2020-03-19T11:00:40-04:00

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

score 0 · Answer 2 · 2020-03-19T11:06:46-04:00

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

score 0 · Answer 3 · 2020-03-19T11:51:37-04:00

Vitaliy Serdtsev Mar 19, 2020 to Swathi Chilukuri

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

0 0

score 0 · Answer 4 · 2020-03-19T11:52:52-04:00

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'

score 0 · Answer 5 · 2020-03-19T16:07:54-04:00

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

score 0 · Answer 6 · 2020-03-19T16:30:11-04:00

Swathi Chilukuri Mar 19, 2020 to Warlin Garcia

Thank You so much.

0 0

score 0 · Answer 7 · 2020-03-19T11:42:18-04:00

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.

score 0 · Answer 8 · 2020-03-19T11:45:17-04:00

Swathi Chilukuri Mar 19, 2020 to Kevin Chan

Hi Kevin,

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

0 0

score 0 · Answer 9 · 2020-03-19T11:52:34-04:00

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'