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.
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.
As far I understand subqueries dont work in case statement. May be in the newer versions they may work.
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.
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.
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.