SQL automatic addition of empty lines although WHERE condition

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?

Page 1 / 1

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

Hi Pooja,
thank you for your idea but its still not working.

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 );

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

Hello Julia.
The result set shows that you have more rows of data with EX=5 and VW=4.
verify your dataset using all fields and check for duplicates.
Regards.
Daniel.

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN