SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 When Cojoining all the columns issue is coming
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/28/2012 :  05:09:45  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  05:35:31  Show Profile  Reply with Quote
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
Go to Top of Page

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/28/2012 :  05:52:21  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2210 Posts

Posted - 11/28/2012 :  06:14:00  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000