Something Wrong? - SQL Server Forums

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

SQL Server 2005 Forums

Transact-SQL (2005)

Something Wrong?

Author

Topic

scripter
Starting Member

49 Posts

Posted - 2007-05-17 : 18:35:37

Can anyone tell me if there is something wrong with this SP

this did return results prior to adding the two lines at the bottom
+CASE WHEN PJ.Job_EdLevel=@EdLevel THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Experience=@Exp THEN 1 ELSE 0 END

and of course the cooresponding ones at the top of the SP

SELECT PJ.*,JC.JobCat,CC.City,EDL.EdLevel,EXL.ExpLevel FROM
PostedJobs PJ
INNER JOIN JobCat JC ON PJ.Job_Category=JC.id
INNER JOIN CountyCity CC ON PJ.Job_City=CC.id
INNER JOIN EdLevel EDL ON PJ.Job_EdLevel=EDL.id
INNER JOIN ExpLevel EXL ON PJ.Job_Experience=EXL.id
WHERE
PJ.Job_Zip =@Zip OR
PJ.Job_City=@City OR
PJ.Job_Category=@JobCat OR
PJ.Job_Pay_Min=@SalLow OR
PJ.Job_Pay_Max=@SalHigh OR (
PJ.Job_Date_Posted <= GETDATE() AND
PJ.Job_Date_Posted >= GETDATE()-@LastPostedDate ) OR
PJ.Job_Description LIKE @KeyWords OR
PJ.Job_EdLevel=@EdLevel OR
PJ.Job_Experience=@Exp
ORDER BY
CASE WHEN PJ.Job_Zip = @Zip THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_City=@City THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Category=@JobCat THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Pay_Min=@SalLow THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Pay_Max=@SalHigh THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Date_Posted <= GETDATE() AND
PJ.Job_Date_Posted >= GETDATE()-@LastPostedDate THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Description LIKE @KeyWords THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_EdLevel=@EdLevel THEN 1 ELSE 0 END
+CASE WHEN PJ.Job_Experience=@Exp THEN 1 ELSE 0 END
DESC

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-17 : 18:53:35

Can the columns have NULL?

+CASE WHEN PJ.Job_EdLevel IS NULL THEN 0 WHEN PJ.Job_EdLevel =@EdLevel THEN 1 ELSE 0 END

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scripter
Starting Member

49 Posts

Posted - 2007-05-17 : 19:32:17

no none of them can have NULL vaues I dont show it here and I do appologize but I do have all of my variables set to standard valus for testing purposes.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-05-17 : 20:15:28

So adding a couple of columns in ORDER BY is changing the resultst?

Dinakar Nethi
SQL Server MVP
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

scripter
Starting Member

49 Posts

Posted - 2007-05-17 : 21:04:18

Thats what it appears I can't figure it out - I am at work now and tomorrow when I wake up I am jumping back into
it and performing any tests or suggestions I can find

scripter
Starting Member

49 Posts

Posted - 2007-05-18 : 11:29:33

Ok I figured it out. Not quite sure why it is but ok the two fields in the table

PJ.Job_Experience
PJ.Job_EdLevel
were null when I added to them a value now it will view them which I thought it would allow for that anyhow but I guess not

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-18 : 11:54:04

Even though those two fields may not allow nulls, they may in fact be null in the final result set, due to your INNER JOINS.

X002548
Not Just a Number

15586 Posts

Posted - 2007-05-18 : 12:59:21

quote:
Originally posted by rudesyle

Even though those two fields may not allow nulls, they may in fact be null in the final result set, due to your INNER JOINS.

True there is attributive non existance and relational non existance...you probably should use COALESCE

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-05-18 : 16:18:29

quote:
True there is attributive non existance and relational non existance...you probably should use COALESCE

But prior to using COALESCE, in the select statement, he'd still have to get a null returned. To do that, he should do an OUTER JOIN to the table instead on an INNER.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources