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

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

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

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

Hi Garcia,

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

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

Vitaliy Serdtsev · Mar 19, 2020

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

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

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'