Joining a Temp Table to Actual Table

Question

I need to verify that each order has been acknowledged. The problem is that each order can have multiple codes. The query I had (utilizing a

CASE

statement) would check for blank fields or fields with the string "None" to verify the order has not been acknowledged. It would return the appropriate result, but multiple rows (once for each possible response) and I only need (1).

I'm attempting to create a temp table that will return the appropriate result and join (via an order unique ID) the two tables together hoping to correct the multiple row issue. Here is the code:

DROP TABLE staging_TABLE;



CREATE TEMP TABLE staging_TABLE(

ORDERID varchar(256) ,

CODE varchar(256) );

/*Keeping data types consistent with the real table*/



INSERT INTO staging_TABLE

SELECT ORDERID,

CASE CODE

  WHEN 'None' THEN 'No'

  WHEN '' THEN 'No'

  ELSE 'Yes'

END

FROM ORDERS

WHERE UTCDATE > SYSDATE - 10

AND CODE IS NOT NULL;



SELECT R.QUESTION,

R.ORDERNAME,

T.CODE

FROM ORDERS R

INNER JOIN staging_TABLE T

ON R.ORDERID= T.ORDERID

WHERE R.UTCDATE > SYSDATE - 10

AND R.CODE IS NOT NULL

AND R.CATEGORY IS NOT NULL

AND R.UTCDATE IS NOT NULL

GROUP BY

         R.ORDER,

         T.CODE,

         R.ORDERNAME,

         R.CODE

ORDER BY 

R.ORDERNAME,

R.ORDER;

Am I doing this correctly? Or is this even the right approach?

Answer 1

Am I doing this correctly? Or is this even the right approach?

No. You don't need a temp table for this. Your query might look like this:

SELECT question, ordername
     , CASE WHEN code IN ('None', '') THEN 'No' ELSE 'Yes' END AS code
FROM   orders
WHERE  utcdate > sysdate - 10
AND    code IS NOT NULL
AND    category IS NOT NULL
GROUP  BY question, ordername, 3, "order"
ORDER  BY ordername, "order";

ORDER is a reserved word. It's not possible to use it as column name unless double quoted. There is something wrong there.
AND R.UTCDATE IS NOT NULL is redundant. It can't be NULL anyway with WHERE R.UTCDATE > SYSDATE - 10
3 in my GROUP BY clause is a positional reference to the CASE expression. Alternatively you can spell it out again:

....
GROUP  BY question, ordername
        , CASE WHEN code IN ('None', '') THEN 'No' ELSE 'Yes' END
        , "order"

Joining a Temp Table to Actual Table

Sign up