When Cojoining all the columns issue is coming

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

When Cojoining all the columns issue is coming

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-28 : 05:09:45

Hello all,

i had a table Frequency have columns like :

FromOperator FromFrequency ToOperator TOFrquency
<= 2 >= 2
<= 4 >= 3
<= 2 >= 5
------------------
No i am concatinating all the columns

WHEN MetricType = 'O' THEN NU.FromOperator + '' + NU.FromFrequency + 'TO' + NU.ToOperator +ToFrequency
NU.FromOperator +''+ NU.FromFrequency IS NULL THEN ''
WHEN NU.ToOperator + NU.ToFrequency) IS NULL THEN NU.FromOperator +''+ NU.FromFrequency

output is coming Fine
here my problem is when i pass FromOperator and FromFrequency values like >=,2 and ToOperator , ToFrequency is null then it show values like >=2

again

when i pass ToOperator and ToFrequency values like >=,2 and FromOperator , FromFrequency is null then it show values like NULL value....can u suggest me

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-28 : 05:35:31

WHEN NU.ToOperator + NU.ToFrequency) IS NULL THEN NU.FromOperator +''+ NU.FromFrequency --> This is the reason you are getting >=2 in the 1st case (when i pass FromOperator and FromFrequency values like >=,2 and ToOperator , ToFrequency is null then it show values like >=2 )

NU.FromOperator +''+ NU.FromFrequency IS NULL THEN '' --> Here you will get empty string ( For 2nd case)

For 2nd case, if u want result as >=2
WHEN NU.FromOperator +''+ NU.FromFrequency IS NULL THEN NU.ToOperator + NU.ToFrequency)

Finally here your code is incomplete....

--
Chandu

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-28 : 05:52:21

hey chandu make it simple leave that code and everything In General Case scenario :
four columns and data

FromOperator || FromFrequency|| ToOperator ||TOFrquency || TotalFrequency
<= 2 >= 2 || <= 2 >= 2
<= 4 >= 3 || <= 4 >= 3
<= 2 >= 5 || <= 2 >= 5
>= 3 NULL NULL || >= 3
NULL NULL >= 5 || NULL

In case condition if i pass values for FromOperator column and FromFrequency column and null values for ToOperator and TOFrquency then values should come like >= 3
In case condition if i pass values for ToOperator column and TOFrquency column and null values for FromOperator and FromFrequency then values should come NULL

P.V.P.MOhan

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2012-11-28 : 06:14:00

DECLARE @tab TABLE(FromOperator varchar(4), FromFrequency varchar(4), ToOperator varchar(4), TOFrquency varchar(4))
INSERT INTO @tab
SELECT '<=', '2', '>=', '2' UNION ALL
SELECT '<=', '4', '>=', '3' UNION ALL
SELECT '<=', '2', '>=', '5' UNION ALL
SELECT '>=', '3', NULL, NULL UNION ALL
SELECT NULL, NULL, '>=', '5'


SELECT CASE
WHEN ToOperator + TOFrquency is NULL THEN FromOperator +' '+ FromFrequency
WHEN FromOperator + FromFrequency IS NULL THEN null
ELSE 
FromOperator +' '+ FromFrequency +' '+ ToOperator +' '+ TOFrquency
END as Total
FROM @tab

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources