SQL Case statement

[icebox27]

Good day!

Please guide me to complete my script

I currently have the following scripts.

select * from tbl_1 <conditions here>
AND  (CASE WHEN (SELECT FIELD1 FROM TABLEX WHERE FIELD1  = 'XXXX') IS NOT NULL THEN
 tbl_1.FIELD2 ELSE ' ' END) = (CASE WHEN (SELECT FIELD1  FROM TABLEX  WHERE FIELD1  = 'XXXX') IS NOT NULL
THEN 'XXXX'ELSE ' ' END) 
 

I wanted to add the following conditions if "SELECT FIELD1 FROM TABLEX WHERE FIELD1 = 'XXXX'" returns a record.

Or tbl_1.FIELD2 is null
 

but there will be no additional conditions if "SELECT FIELD1 FROM TABLEX WHERE FIELD1 = 'XXXX'" does not return a record.

Thanks everyone

[amitpatel66]

Please check POSTING GUIDELINES on how to make use of CODE tags.

Please make sure you use proper CODE tags in future.

MODERATOR

[amitpatel66]

I would suggest you to make use of DECODE instead on CASE statement.

Please try below query:

select * from tbl_1<conditions here>
AND  DECODE((SELECT FIELD1 FROM TABLEX WHERE FIELD1  = 'XXXX'),'XXXX',tbl_1.field2,' ') = DECODE((SELECT FIELD1 FROM TABLEX WHERE FIELD1  = 'XXXX'),'XXXX','XXXX',' ')