SQl Query Not Working - 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 2012 Forums

Transact-SQL (2012)

SQl Query Not Working

Author

Topic

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-04 : 14:19:58

For the last few years I've been using this SQL query (see below). I only use it once a year (it gathers some data used in a government report). This year it isn't working. It returns zero rows. I don't understand. Does anyone see an error here?? ...SQL doesn't give me one when I execute this.


SELECT t.SSN, t.StartOfCare, t.PrimDiag, t.ClientID, 
    c.ID, c.ClientName
FROM
    (
    SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, SSN, PrimDiag, ClientID, StartOfCare, ID
    FROM Patient
    WHERE RptYear = '2014' and PrimDiag <> 'NULL' and PrimDiag <> '' and Status <> 'Pending' and Status <> 'Non-Admit' and SSN <> ''
    ) t
    INNER JOIN Client c ON c.ID = t.ClientID
  WHERE t.Seq = 1
Order By t.ClientID

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-04 : 14:36:25

We don't know what it's supposed to do to be able to tell you what's wrong with it. But I would break it down to see why you aren't getting the data. Remove a piece of the WHERE clause until you find the issue.

I would specifically look at this piece first: PrimDiag <> 'NULL'. That screams of a problem. Typically it would be PrimDiag IS NOT NULL if the data is an actual NULL rather than the string 'NULL'. But I don't know your data to be able to tell you if it's correct or not.

I would also look at the join clause.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-04 : 14:46:10

Thanks ....I can actually comment out that entire WHERE clause and the query still doesn't return anything. Oddly enough my developer software that uses this query returns results just fine. It's the same database!

Thanks for the tips.

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

ScottPletcher
Aged Yak Warrior

550 Posts

Posted - 2015-02-04 : 14:54:30

Maybe the report year should be 2015 now?!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-04 : 15:04:36

If the software returns the data for the EXACT same query, then the issue is with the tool you are using? What tool are you using?

And if the queries aren't identical and yours doesn't return data when you comment out the where clause in the derived table, then the issue is what the join condition/data.

Does this return data?

SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, SSN, PrimDiag, ClientID, StartOfCare, ID
FROM Patient

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-04 : 15:04:48

quote:
Originally posted by ScottPletcher

Maybe the report year should be 2015 now?!

Thanks Scott...2014 is actually correct. I need last years records.

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-04 : 15:07:00

quote:
Originally posted by tkizer

If the software returns the data for the EXACT same query, then the issue is with the tool you are using? What tool are you using?

And if the queries aren't identical and yours doesn't return data when you comment out the where clause in the derived table, then the issue is what the join condition/data.

Does this return data?

SELECT ROW_NUMBER() OVER (PARTITION BY SSN ORDER BY StartOfCare) AS Seq, SSN, PrimDiag, ClientID, StartOfCare, ID
FROM Patient

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

I'm using MS SQL Management Studio. My development platform is Alpha Anywhere.

No, that query you asked me to try does not work in SQL management studio

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

mikeallenbrown
Yak Posting Veteran

72 Posts

Posted - 2015-02-04 : 15:08:57

wait a minute ...it's my database that is the problem. I use a backup copy of the data locally to run tests on. When I run this same query on my LIVE SQL server it gets me data.

Sorry gents ...looks like this is user error ...

Mike Brown
ITOT Solutions, Inc.
SQL Server 2012
Alpha Five v3 (12)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2015-02-04 : 15:09:19

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources