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?

There is no `''` in sql here: `WHEN ''` try `WHEN IS NULL` instead, I mean RDBMs doesn't store empty strings — Jorge Campos, Oct 05 '15 at 23:09
@JorgeCampos, no. In *PostgreSQL* empty string and `NULL` are not the same, and it does store empty string. — MatheusOl, Oct 06 '15 at 00:47
What is supposed to happen if the same order has one row with 'Yes' and one row with 'No' (after your `CASE` statement)? Also, this question seems hardly related to temp tables at all ... BTW, your column cannot be named `ORDER` (without double quotes), that would be a syntax error, please clarify. — Erwin Brandstetter, Oct 06 '15 at 01:16
@MatheusOl Thanks for the info, I was taking oracle missleading `''` as `null` into consideration for all RDBMs, which of course is wrong! As discussed in this thread [Why does Oracle 9i treat an empty string as NULL?](http://stackoverflow.com/questions/203493/why-does-oracle-9i-treat-an-empty-string-as-null#comment77936_203551) ! You are totally Right! :) — Jorge Campos, Oct 06 '15 at 01:47
"RDBMs doesn't store empty strings" lucky I can't downvote a comment — underscore_d, Oct 12 '15 at 16:01

Erwin Brandstetter · Accepted Answer · 2015-10-12T15:56:54.300

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"

score 0 · Answer 2 · answered Oct 06 '15 at 01:17

0

You can use the DISTINCT keyword as follows so you will not need a temp table:

SELECT DISTINCT QUESTION,
ORDERNAME,
CASE CODE
  WHEN 'None' THEN 'No'
  WHEN '' THEN 'No'
  ELSE 'Yes'
FROM ORDERS
WHERE UTCDATE > SYSDATE - 10
AND CODE IS NOT NULL
AND CATEGORY IS NOT NULL
AND UTCDATE IS NOT NULL
ORDER BY 2,3;

answered Oct 06 '15 at 01:17

Miyuru Ratnayake

456
3
11

`DISTINCT` is a valid alternative if all columns used are in the `SELECT` list. To order by a different column, you would either need to a construct with a subselect or you have to use `GROUP BY` instead, or `DISTINCT ON ()`. – Erwin Brandstetter Oct 06 '15 at 01:34

Joining a Temp Table to Actual Table

2 Answers2