All Forums Database
patilsantosh 7 posts Joined 07/15
23 Jul 2015
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
 

patilsantosh 7 posts Joined 07/15
23 Jul 2015

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.

dnoeth 4628 posts Joined 11/04
23 Jul 2015
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

 

Dieter

patilsantosh 7 posts Joined 07/15
23 Jul 2015

@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
 
 
 

patilsantosh 7 posts Joined 07/15
23 Jul 2015

thank you so much dnoeth...it is working

You must sign in to leave a comment.