March 28, 2011 at 8:10 pm
Here is my sample data
ID,TABLEA,TABLEB
1,CA,NULL
2,NULL,GA
3,MA,NULL
I am mapping TableB(Source field ) to my target field. and if TableB is Null use TableA value. I am using this expression
"NULL"? [COLUMN_B] : [COLUMN_A] ( still red on derived column ) Need help to fix this issue, I want to know where i am wrong.
CREATE TABLE SAMPLE
(
IDINT,
COLUMN_AVARCHAR(5),
COLUMN_BVARCHAR(5)
)
INSERT INTO SAMPLE (ID,COLUMN_A,COLUMN_B )VALUES ('1','CA','NULL')
INSERT INTO SAMPLE (ID,COLUMN_A,COLUMN_B )VALUES ('2','NULL','GA')
INSERT INTO SAMPLE (ID,COLUMN_A,COLUMN_B )VALUES ('3','MA','NULL')
Note:- Please help me if want to use more than two column how i can expand this expression.
March 28, 2011 at 8:27 pm
I think you will need something like..
ISNULL ([COLUMN_B]) ? [COLUMN_B] : [COLUMN_A]
March 28, 2011 at 8:51 pm
appreciate your prompt reply, after i run your suggest query i am getting result listed below,
IDCOLUMN_ACOLUMN_Bnew column
1CANULLCA
2NULLGANULL
3MANULLMA
New column i m using in derived column. They just copy Column_A value only.
March 28, 2011 at 11:40 pm
happycat59 (3/28/2011)
I think you will need something like..ISNULL ([COLUMN_B]) ? [COLUMN_B] : [COLUMN_A]
Almost, the correct expression is:
ISNULL ([COLUMN_B]) ? [COLUMN_A] : [COLUMN_B]
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 30, 2011 at 8:31 pm
Here is the result that i am getting sir,
IDCOLUMN_ACOLUMN_Bnew column
1CA NULL CA
2NULL GA NULL
3MA NULL MA
Should be like this,
ID COLUMN_A COLUMN_B NEW COLUMN
1 CA NULL CA
2 NULL GA GA
3 MA NULL MA
Please help me out where i am wrong.
March 30, 2011 at 11:37 pm
Did you use the expression that I gave you? I find that hard to believe...
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 31, 2011 at 4:05 am
ISNULL ([COLUMN_B]) ? [COLUMN_A] : [COLUMN_B]
The above expression is correct, the only problem i see is while inserting data to Sample table you have used 'NULL', this won't work with ISNULL expression as it is taking it as a string value.
Remove the single quote from NULL and it will work fine
March 31, 2011 at 4:52 am
sam 55243 (3/31/2011)
... the only problem i see is while inserting data to Sample table you have used 'NULL', this won't work with ISNULL expression as it is taking it as a string value.Remove the single quote from NULL and it will work fine
Well spotted! I looked over that one.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply