please help on this scenarios?

Database
Enthusiast

please help on this scenarios?

CREATE VOLATILE TABLE SPCL_TYP(

PID INT,

SPCL_TYP_C CHAR(4),

SUB_SPCL_TYPE1 CAHR(3),

SUB_SPCL_TYPE2CHAR(3)

)ON COMMIT PRESERVE ROWS

INSERT INTO SPCL_TYP VALUES(1,'INTR','PUM,NULL);

INSERT INTO SPCL_TYP VALUES(1,'INTR',NULL,NULL);

INSERT INTO SPCL_TYP VALUES(2,'CCM','PUM',NULL);

INSERT INTO SPCL_TYP VALUES(3,'DED','TRE','WRE');

-------------------------------------------------------------------------------------

 NEED TO WRITE BELOW CASE

IF SPCL_TYP_C IS NOT NULL THEN 'Y'

IF SUB_SPCL_TYPE1 IS NOT NULL THEN 'N'

IF SUB_SPCL_TYPE2 IS NOT NULLTHEN 'N'

I NEED O/P LIKE THIS:-

SPCL_TYPE_CODE,PRE_IND_CD

INTR ,Y

INTR ,Y

CCM ,Y

DED,Y

PUM,N

PUM,N

TRE,N

WRE,N

4 REPLIES
Enthusiast

Re: please help on this scenarios?

all the not null values from the SUB_SPCL_TYPE1,2 VALUES SHOULD ME MARKE AS 'N'AND THOSE NOT NULL VALUES NEED TO PASS IN SPCL_TYPE_CODE COLUMN AS SHOWN ABOVE.

Senior Apprentice

Re: please help on this scenarios?

SELECT SPCL_TYP_C AS SPCL_TYPE_CODE, 'Y' AS PRE_IND_CD
FROM SPCL_TYP
WHERE SPCL_TYP_C IS NOT NULL
UNION ALL
SELECT SUB_SPCL_TYPE1, 'N'
FROM SPCL_TYP
WHERE SUB_SPCL_TYPE1 IS NOT NULL
UNION ALL
SELECT SUB_SPCL_TYPE2, 'N'
FROM SPCL_TYP
WHERE SUB_SPCL_TYPE2 IS NOT NULL
Enthusiast

Re: please help on this scenarios?

@THANKS FOR QUICKRESPONSE ...

I NEED ,ALL SPCL_TYPE_CODE ,SUB_SPCL_TYPE1,2 NOT NULL VALUES IIN ONE ROWS AND WITH INDICATORS IN ANOTHER ROWS AS SHOWN BELOW

spcl_type_code,pre_ind_cd

INTR,Y

INTR,Y

CCM,Y

DDM,Y--ONE COLUMNVALUES WITH PRE_IND_CD

PUM,N

PUM,N

TRE,N FROM SUB_SPCL_TYPE1,NOT NULLCOLUMN VALUES WITH INDICATORS COLUMNS

WRE,N FROM SUB_TYPE2,NOTNULL VALUES WITH INDICATORS COLUMN

Enthusiast

Re: please help on this scenarios?

thank you so much dnoeth...it is working