CODEWITHSUNDEEP
sqlpostgresqlamazon-redshift
Abootman
Abootman

Reputation: 73

Joining a Temp Table to Actual Table

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?

Upvotes: 1

Views: 4513

Answers (2)

Erwin Brandstetter
Erwin Brandstetter

Reputation: 656784

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"

Upvotes: 1

Miyuru Ratnayake
Miyuru Ratnayake

Reputation: 456

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;

Upvotes: 0

Related Questions