SQL automatic addition of empty lines although WHERE condition

Technical Implementation Codes & Statements
#1

Hi everyone,

I hope you can help me with my SQL Problem.

I created the Table Cases and selected my Case Key and a specific filtering for “Specific Type”.
The strange thing is that my output includes two lines but in my row data the conditions are matched for ‘Text1’.

DROP TABLE IF EXISTS “CASES”;

CREATE TABLE “CASES” AS(
SELECT DISTINCT
“B”.“ID” AS “CASE_KEY”,
CASE
WHEN “B”.“EX” = 5 AND “B”.“VW” = 4
THEN ‘Text1’
ELSE ‘next’
END AS “Specific Type”

FROM “Data” AS “B” );

SELECT * FROM “CASES” WHERE “CASES”.“CASE_KEY” =‘722’
OUPUT:
CASE_KEY Specific Type"
722 Text1
722 next

Does someone know how why I still get next as a output and how can I change the syntax to get sure to get out only Text1?

1 Like
#2

Hi Julia,

Please try with the below syntax -
DROP TABLE IF EXISTS CASES;

CREATE TABLE CASES AS(

SELECT DISTINCT B.“ID” AS “CASE_KEY”,

(CASE WHEN (B.“EX” = 5 AND B.“VW” = 4) THEN ‘Text1’

ELSE ‘Next’

END) AS “Specific Type”

FROM “Data” AS B );

Thanks & Regards,
Pooja

#3

Hi Pooja,

thank you for your idea but it’s still not working.

#4

Hello @Julia ,

The reason why you are getting 722 for ‘Text1’ and ‘Next’ is that the Data table has ID value of 722 for EX = 5 and VW = 4 and for some other values as well.
For the First criteria it is inserting (722, Text1) and for other values of EX and VW, it is inserting (722, Next).

If you wish to insert only for EX = 5 and VW = 4, Please try the below code. Hope it helps. Thank You.

CREATE TABLE “CASES” AS(

SELECT DISTINCT
“B”.“ID” AS “CASE_KEY”,
CASE WHEN “B”.“EX” = 5 AND “B”.“VW” = 4 THEN ‘Text1’ END AS “Specific Type”
FROM “Data” AS “B” );

#5

Hello jayanta,

thank you for your reply.
Unfortunately it is still not working but we opened up a ticket because our conditions are clear (conditions can only apply to individual values once).

Thanks,
Julia