case when from TSQL

Database
Enthusiast

case when from TSQL

This is part of a select statement that worked fine in TSQL. Any ideas for Teradata sql? CASE WHEN PROC1 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC2 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC3 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC4 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC5 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC6 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC7 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC8 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC9 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' WHEN PROC10 IN (SELECT CODE FROM USR_RACSTER.CHL_2017 WHERE VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' ELSE '0' END ChlamScreen
Tags (1)
18 REPLIES
Supporter

Re: case when from TSQL

Hi Irac4444,

 

I am not an expert in TSQL and so I am assuming PROC1, PROC2, PROC3 ..... are columns of a table. They could be variables as well  (which I am not use. If yes then you could use the same logic as below and create a stored procedure.) So I have created tables according to my understanding and also created a sql. You need to rewrite the query using outer joins. Below is piece of code may help you. This may not provide you the exact result. You may need to work on the join or creating stored procedure depending on requirement. Only if you could provide me further details on PROC1, PROC2, PROC3 and how they are created and used then I could rewrite the query for you:

--- Creating dummy tables to duplicate your scenario
DROP TABLE CHL_2017;
DROP TABLE PROC_TABLE;

CREATE MULTISET VOLATILE TABLE CHL_2017
(
  CODE VARCHAR(1000)
, VALUE_SET_NAME VARCHAR(100)
, CODE_SYSTEM VARCHAR(100)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO CHL_2017 VALUES ('PROC1','Chlamydia Tests','CPT');
INSERT INTO CHL_2017 VALUES ('PROC2','Chlamydia Tests','HCPCS');
INSERT INTO CHL_2017 VALUES ('PROC11','Chlamydia Tests','HCPCS');

CREATE MULTISET VOLATILE TABLE PROC_TABLE
(
  PROC1 VARCHAR(1000)
, PROC2 VARCHAR(1000)
, PROC3 VARCHAR(1000)
, PROC4 VARCHAR(1000)
, PROC5 VARCHAR(1000)
, PROC6 VARCHAR(1000)
, PROC7 VARCHAR(1000)
, PROC8 VARCHAR(1000)
, PROC9 VARCHAR(1000)
, PROC10 VARCHAR(1000)
)
ON COMMIT PRESERVE ROWS;

INSERT INTO PROC_TABLE VALUES ('PROC1','PROC2','PROC3','PROC4','PROC5','PROC6','PROC7','PROC8','PROC9','PROC10'); 
INSERT INTO PROC_TABLE VALUES ('PROC','PROC','PROC3','PROC4','PROC5','PROC6','PROC7','PROC8','PROC9','PROC10');

--- Your query
SELECT
  CASE
  	WHEN COALESCE(P.PROC1,P.PROC2,P.PROC3,P.PROC4,P.PROC5,P.PROC6,P.PROC7,P.PROC8,P.PROC9,P.PROC10) IS NOT NULL
  	THEN 1
  	ELSE 0
  END AS ChlamScreen
FROM
  PROC_TABLE P
FULL OUTER JOIN
  (
    SELECT CODE
    FROM CHL_2017
    WHERE 
      VALUE_SET_NAME IN('Chlamydia Tests')
    AND CODE_SYSTEM IN ('CPT','HCPCS')
   ) CHL
ON CHL.CODE IN (P.PROC1,P.PROC2,P.PROC3,P.PROC4,P.PROC5,P.PROC6,P.PROC7,P.PROC8,P.PROC9,P.PROC10);

Let me know if you need more information.

 

Thanks,

Rohan Sawant

Senior Apprentice

Re: case when from TSQL

Hi,

 

This may be very simple.

If PROC1, PROC2 etc., VALUE_SET_NAME, CODE_SYSTEM are all columns in a table then this code will probably run 'as is' on Teradata - it is 'just' a CASE expression.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Supporter

Re: case when from TSQL

Hi,

 

As far I understand subqueries dont work in case statement. May be in the newer versions they may work.

 

Thanks,

Rohan Sawant

Senior Apprentice

Re: case when from TSQL

Hi Rohan,

 

You can use scalar sub-queries which would normally have an aggregate function in them - which I'll admit these don't.

 

If those sub-queries only return a single row then they can be used. If they return more than a single row then you're correct in that they will fail.

 

So the original code may not work in TD and an alternative is required.

 

Cheers,

Dave

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: case when from TSQL

Hi Rohan, Thank you for your reply. The PROC1-10 are columns in a table. They are medical procedure codes. I need to evaluate each procedure code to see if they contain codes in my look up table (USR_LN.CHL_2017) and then create one column , 'chlamscreen' (1/0) that indicates if the record contains a screening for Chlamydia. The sub query will return multiple values. There are seven different procedure codes that indicate a screening for Chlamydia. I will work from your ideas to see if I can get something running in Teradata. Thanks again, Laura
Enthusiast

Re: case when from TSQL

I get this error when I run as is: Failed [3771 : HY000] Illegal expression in WHEN clause of CASE expression. It works perfectly in TSQL!
Enthusiast

Re: case when from TSQL

The sub query returns multiple rows. I ran this job through Ispirer and I gave me this as a translation, but it doesn't work either... CASE WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc1) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc2) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc3) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc4) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc5) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc6) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc7) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc8) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc9) > 0 THEN '1' WHEN(SELECT COUNT(*) FROM USR_LN WHERE (measure_id = 'CHL' and value_set_name = 'Chlamydia Tests' AND code_system in('CPT','HCPCS')) AND code = a.Proc10) > 0 THEN '1' ELSE '0' END
Senior Apprentice

Re: case when from TSQL

Hi Laura,

 

You should be able to use the CASE expression and then use a correlated scalar sub-query.

 

Assume that in your existing query the table containing coluns PROC1, PROC2 etc has an alias of t1.

 

The following is the code for the first 'when' clause - for column PROC1 - repeat the logic for the other PROCn columns.

WHEN t1.PROC1 IN (SELECT max(CODE) FROM USR_RACSTER.CHL_2017 WHERE t1.proc1 = code and VALUE_SET_NAME IN('Chlamydia Tests') AND CODE_SYSTEM IN ('CPT','HCPCS')) THEN '1' 

With this code the sub-query is returning a maximum of one value, which is the value found in PROC1, if that value is found in any row in table USR_RACSTER.CHL_2017.

 

I think that will work for you.

 

Cheers,

Dave

 

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Supporter

Re: case when from TSQL

Hi Dave,

 

I have a question here. If the query picks up max from the table it wont compare it to other codes. For example if the inner query gives values - 10,20,30,5 -- it will check for the value 30 which is max. But as I understand Laura wants it to see whether it PROC1-10 code is present in any of the values 10,20,30,5. Let me know if I am missing anything.

 

 

 

Thanks,

Rohan Sawant