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
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?
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"