Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Problem with SQL Query Expand / Collapse
Author
Message
Posted Monday, January 13, 2014 8:50 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:17 AM
Points: 6, Visits: 29
Hi,

I ran this query multiple times last week in my SQL Server 2005 database and it worked fine. Today, I go to run the exact same query and it only returns the first row (code = 30001). I've tried other values of the BETWEEN statement and still the same behavior is evident. It's almost like the BETWEEN or the following AND is not working. This is driving me nuts. Would someone be kind enough to help me please? I restarted the database and rebooted the server too!!

SELECT(
SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_FIRSTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT
FROM HPSITE.DEMOGRAPHICS
INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN HPSITE.NOTES_MASTER ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_MASTER.IMRENOTE_CODE
INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.NOTES_MASTER.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN '30001' AND '32000'
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'
FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL
) AS COL_XML;
Post #1530331
Posted Monday, January 13, 2014 9:09 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 13,481, Visits: 12,339
Skuldouggery (1/13/2014)
Hi,

I ran this query multiple times last week in my SQL Server 2005 database and it worked fine. Today, I go to run the exact same query and it only returns the first row (code = 30001). I've tried other values of the BETWEEN statement and still the same behavior is evident. It's almost like the BETWEEN or the following AND is not working. This is driving me nuts. Would someone be kind enough to help me please? I restarted the database and rebooted the server too!!

SELECT(
SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_FIRSTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT
FROM HPSITE.DEMOGRAPHICS
INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN HPSITE.NOTES_MASTER ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_MASTER.IMRENOTE_CODE
INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.NOTES_MASTER.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN '30001' AND '32000'
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'
FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL
) AS COL_XML;


Without table definitions and some data we can't even take a shot in the dark.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530342
Posted Monday, January 13, 2014 9:14 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 12:19 PM
Points: 3,665, Visits: 7,998
If you haven't changed the query, then the data might have changed. Verify that as you might be in trouble.


Luis C.
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1530343
Posted Monday, January 13, 2014 10:48 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:17 AM
Points: 6, Visits: 29
Sorry, does this help? Also, I followed the link that the other gentleman had for putting some data together.

[HPSITE].[DEMOGRAPHICS]
[IMREDEM_CODE] [int]
[DEM_EXTERNALID] [varchar]
[DEM_LASTNAME] [varchar]
[DEM_FIRSTNAME] [varchar]
[DEM_DATEOFBIRTH] [datetime]

[HPSITE].[CHARTATTACHMENT]
[IMREDEMEC_CODE] [int]
[IMRENOTE_CODE] [int]
[CHAT_DATE] [datetime]

[HPSITE].[NOTES_MASTER]
[IMRENOTE_CODE] [int]

[HPSITE].[NOTES_TEXTDATA]
[IMRENOTE_CODE] [int]
[NOTE_TEXT] [text]

For the data:
SELECT 'SELECT '
+ DEM_EXTERNALID(ID,'''')+','
+ IMREDEM_CODE(CODE,'''')+','
+ DEM_LASTNAME(LN,'''')+','
+ DEM_LASTNAME(FN,'''')+','
+ DEM_DATEOFBIRTH(DOB,'''')+','
+ CHAT_DATE(DATE,'''')+','
+ NOTE_TEXT(TEXT,'''')+','
+ ' UNION ALL'
FROM HPSITE.DEMOGRAPHICS
INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN HPSITE.NOTES_MASTER ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_MASTER.IMRENOTE_CODE
INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.NOTES_MASTER.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN '1' AND '5000'
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'

Data:
SELECT '856','1688','LN1','A','Nov 29 1948 12:00AM','Sep 27 2004 12:23PM','LARGE TEXT FILE'
SELECT '11239','3036','LN2','L','Sep 18 1937 12:00AM','Sep 28 2004 9:58AM','LARGE TEXT FILE'
SELECT '255','1088','LN3','B','Jul 19 1955 12:00AM','Sep 29 2004 6:17PM','LARGE TEXT FILE'
SELECT '1602','816','LN4','C','Jul 9 1923 12:00AM','Sep 30 2004 10:29AM','LARGE TEXT FILE'
SELECT '804','1637','LN5','D','Feb 6 1933 12:00AM','Oct 7 2004 10:55AM','LARGE TEXT FILE'
SELECT '138','130','LN6','P','Jun 5 1943 12:00AM','Oct 12 2004 10:44AM','LARGE TEXT FILE'
SELECT '12505','4249','LN7','H','Jul 7 1925 12:00AM','Oct 13 2004 9:55AM','LARGE TEXT FILE'
SELECT '12731','4467','LN8','J','Mar 16 1933 12:00AM','Oct 13 2004 10:40AM','LARGE TEXT FILE'
SELECT '11929','3693','LN9','E','Sep 18 1922 12:00AM','Oct 18 2004 2:09PM','LARGE TEXT FILE'
Post #1530384
Posted Monday, January 13, 2014 12:05 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 13,481, Visits: 12,339
Skuldouggery (1/13/2014)
Sorry, does this help? Also, I followed the link that the other gentleman had for putting some data together.



None of this really helps a whole lot. We can turn this into actual create table scripts easily enough but we still don't have data for these tables. The idea here is to allow us to create these tables on our machine so we can see if the code actually works.

Also, I would HIGHLY recommend you change your text columns to varchar(max). The text datatype has been deprecated and is a complete PITA to work with. It is possible that you don't even need max because the varchar datatype can hold as much as 8,000 characters before being forced to use max.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530403
Posted Monday, January 13, 2014 12:29 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:17 AM
Points: 6, Visits: 29
I'm a little confused. Is that data at the bottom of what I posted not appropriate? Also, I do not know how to convert the Text datatype to what you specify.
Post #1530413
Posted Monday, January 13, 2014 12:36 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 13,481, Visits: 12,339
Skuldouggery (1/13/2014)
I'm a little confused. Is that data at the bottom of what I posted not appropriate? Also, I do not know how to convert the Text datatype to what you specify.


Well you have 4 tables in your query. You provided data but it doesn't match any of the tables you posted.

If you want to alter your datatype you just need to change the datatype with an alter command.

alter table [HPSITE].[NOTES_TEXTDATA]
alter column [IMRENOTE_CODE] varchar(max);



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530416
Posted Monday, January 13, 2014 1:56 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, March 17, 2014 11:17 AM
Points: 6, Visits: 29
This what I have. Also, I am a little leary to alter the data type...remember the whole 'newbie' thing.
HPSITE.DEMOGRAPHICS
EID,IMREDEM_CODE,LN,FN,DOB
SELECT '2','4','LN4','FN4','Mar 18 1983 12:00AM'
SELECT '5','7','LN7','FN7','Feb 6 1996 12:00AM'
SELECT '8','10','LN8','FN8','Feb 15 1957 12:00AM'
SELECT '20','22','LN9','FN9','Jun 9 1971 12:00AM'
SELECT '25','27','LN10','FN10','Jul 22 1996 12:00AM'
SELECT '28','30','LN11','FN11','Apr 13 1993 12:00AM'

HPSITE.CHARTATTACHMENT
IMREDEMEC_CODE,IMRENOTE_CODE,CHAT_DATE
SELECT '2','146669','Feb 3 2005 1:34PM'
SELECT '2','146720','Feb 3 2005 2:00PM'
SELECT '2','146955','Feb 3 2005 3:47PM'
SELECT '2','147394','Feb 4 2005 8:37AM'
SELECT '2','591108','Mar 8 2006 2:09PM'
SELECT '2','2589211','Mar 24 2011 12:37PM'
SELECT '5','863598','Oct 9 2006 12:47PM'
SELECT '5','2322272','Jun 17 2010 2:49PM'
SELECT '5','2781700','Oct 24 2011 4:56PM'
SELECT '7','104338','Dec 23 2004 11:28AM'
SELECT '8','667930','May 9 2006 2:13PM'
SELECT '8','879185','Oct 20 2006 8:41AM'
SELECT '8','2203832','Feb 17 2010 2:12PM'
SELECT '8','2322216','Jun 17 2010 2:29PM'
SELECT '20','1490261','Feb 19 2008 8:03PM'
SELECT '20','1783630','Jan 5 2009 11:03AM'
SELECT '20','2380446','Aug 18 2010 11:05AM'
SELECT '20','2490167','Dec 6 2010 1:21PM'
SELECT '25','120797','Jan 11 2005 7:19AM'

HPSITE.NOTES_TEXTDATA
IMRENOTE_CODE, NOTE_TEXT
SELECT '146669',Text1
SELECT '146720',Text2
SELECT '146955',Text3
SELECT '147394',Text4
SELECT '591108',Text5
SELECT ''2589211',Text6
SELECT '863598',Text7
SELECT '2322272',Text8
SELECT '2781700',Text9
SELECT '104338',Text10
SELECT '667930',Text11
SELECT '879185',Text12
SELECT '2203832',Text13
SELECT '2322216',Text14
SELECT '1490261',Text15
SELECT '1783630',Text16
SELECT '2380446',Text17
SELECT '2490167',Text18
SELECT '120797',Text19

This is the code again, I realized that I had an extra Inner Join.
SELECT(
SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_LASTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT
FROM HPSITE.DEMOGRAPHICS
INNER JOIN HPSITE.CHARTATTACHMENT ON HPSITE.DEMOGRAPHICS.IMREDEM_CODE = HPSITE.CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN HPSITE.NOTES_TEXTDATA ON HPSITE.CHARTATTACHMENT.IMRENOTE_CODE = HPSITE.NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN '1' AND '10'
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'
FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL
) AS COL_XML;
Post #1530451
Posted Monday, January 13, 2014 2:44 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 12:11 PM
Points: 13,481, Visits: 12,339
I will make the assumption that you didn't actually read the article that was suggested. If you had you would have realized that we want is create table statements and insert statements to those tables.

Something like this.

create table #DEMOGRAPHICS
(
IMREDEM_CODE int,
DEM_EXTERNALID varchar(10),
DEM_LASTNAME varchar(10),
DEM_FIRSTNAME varchar(10),
DEM_DATEOFBIRTH datetime
)


create table #CHARTATTACHMENT
(
IMREDEMEC_CODE int,
IMRENOTE_CODE int,
CHAT_DATE datetime
)

create table #NOTES_TEXTDATA
(
IMRENOTE_CODE int,
NOTE_TEXT text
)

insert #DEMOGRAPHICS
SELECT '2','4','LN4','FN4','Mar 18 1983 12:00AM' union all
SELECT '5','7','LN7','FN7','Feb 6 1996 12:00AM' union all
SELECT '8','10','LN8','FN8','Feb 15 1957 12:00AM' union all
SELECT '20','22','LN9','FN9','Jun 9 1971 12:00AM' union all
SELECT '25','27','LN10','FN10','Jul 22 1996 12:00AM' union all
SELECT '28','30','LN11','FN11','Apr 13 1993 12:00AM'

insert #CHARTATTACHMENT
SELECT '2','146669','Feb 3 2005 1:34PM' union all
SELECT '2','146720','Feb 3 2005 2:00PM' union all
SELECT '2','146955','Feb 3 2005 3:47PM' union all
SELECT '2','147394','Feb 4 2005 8:37AM' union all
SELECT '2','591108','Mar 8 2006 2:09PM' union all
SELECT '2','2589211','Mar 24 2011 12:37PM' union all
SELECT '5','863598','Oct 9 2006 12:47PM' union all
SELECT '5','2322272','Jun 17 2010 2:49PM' union all
SELECT '5','2781700','Oct 24 2011 4:56PM' union all
SELECT '7','104338','Dec 23 2004 11:28AM' union all
SELECT '8','667930','May 9 2006 2:13PM' union all
SELECT '8','879185','Oct 20 2006 8:41AM' union all
SELECT '8','2203832','Feb 17 2010 2:12PM' union all
SELECT '8','2322216','Jun 17 2010 2:29PM' union all
SELECT '20','1490261','Feb 19 2008 8:03PM' union all
SELECT '20','1783630','Jan 5 2009 11:03AM' union all
SELECT '20','2380446','Aug 18 2010 11:05AM' union all
SELECT '20','2490167','Dec 6 2010 1:21PM' union all
SELECT '25','120797','Jan 11 2005 7:19AM'

insert #NOTES_TEXTDATA
SELECT '146669','Text1' union all
SELECT '146720','Text2' union all
SELECT '146955','Text3' union all
SELECT '147394','Text4' union all
SELECT '591108','Text5' union all
SELECT '2589211','Text6' union all
SELECT '863598','Text7' union all
SELECT '2322272','Text8' union all
SELECT '2781700','Text9' union all
SELECT '104338','Text10' union all
SELECT '667930','Text11' union all
SELECT '879185','Text12' union all
SELECT '2203832','Text13' union all
SELECT '2322216','Text14' union all
SELECT '1490261','Text15' union all
SELECT '1783630','Text16' union all
SELECT '2380446','Text17' union all
SELECT '2490167','Text18' union all
SELECT '120797','Text19'

Now we both have the same temp tables that we are working with so we have a very solid common ground.

Let's take your query. You are including a predicate for IMREDEMEC_CODE. Since this is an int datatype you should not use strings in your query.

Given the nature of what you are returning you need to remove the XML portion so you can see the rows being returned.

--SELECT(
SELECT ROW_NUMBER() OVER(ORDER BY DEM_EXTERNALID) AS '@id', DEM_EXTERNALID, IMREDEM_CODE, DEM_LASTNAME, DEM_LASTNAME, DEM_DATEOFBIRTH,IMREDEMEC_CODE,CHAT_DATE,NOTE_TEXT
FROM #DEMOGRAPHICS
INNER JOIN #CHARTATTACHMENT ON #DEMOGRAPHICS.IMREDEM_CODE = #CHARTATTACHMENT.IMREDEMEC_CODE
INNER JOIN #NOTES_TEXTDATA ON #CHARTATTACHMENT.IMRENOTE_CODE = #NOTES_TEXTDATA.IMRENOTE_CODE
WHERE IMREDEMEC_CODE BETWEEN 1 AND 10
AND DEM_LASTNAME <>'Demonstration'
AND DEM_LASTNAME <>'Test'
AND DEM_LASTNAME <>'Train'
AND DEM_LASTNAME <>'Erroneous'
--FOR XML PATH('Row'), ROOT('Results'), ELEMENTS XSINIL
--) AS COL_XML;

That all seems to be working just fine to me. Can you explain what is not working correctly?

Of course let's not forget to include the cleanup to drop our temp tables.

drop table #DEMOGRAPHICS
drop table #CHARTATTACHMENT
drop table #NOTES_TEXTDATA



_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1530473
Posted Monday, January 13, 2014 11:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 3:25 AM
Points: 3,648, Visits: 5,328
Sean Lange (1/13/2014)


Of course let's not forget to include the cleanup to drop our temp tables.

drop table #DEMOGRAPHICS
drop table #CHARTATTACHMENT
drop table #NOTES_TEXTDATA




This is awesome advice. Is that in the referenced article? If not, it should be.

Do you have any idea how many times I've typed in those nefarious DROPs? Yuck!

Of course, I do usually throw in a "GO" directive just before them to be sure they get dropped when I code up some kind of syntax or compile error in my solution attempt.



My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1530545
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse