|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Whats Wrong with the Query?I am using this Query in application , its working fine with varchar column
types when i use COLLATE in QUERY but its not working for Datetime columns types, any suggestions? SELECT TOP 10 * FROM #TempExt WHERE eFolderID NOT IN (SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC ,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote:
>I am using this Query in application , its working fine with varchar column Hi vsr,>types when i use COLLATE in QUERY but its not working for Datetime columns >types, any suggestions? > >SELECT TOP 10 * FROM #TempExt >WHERE eFolderID NOT IN >(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC >,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC Please elaborate on "not working". More specific: tell us about your table (CREATE TABLE statement), your data (INSERT statements with sample data), your requirements (expected output), and the current results from your query. See www.aspfaq.com/5006 as well. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) I have one Temp Table with one of the columns as defined as datetime
datatype, its a simple Temp table and i am running this query , the expected results are first it shoud order by one field and then apply order on other field... for ex: RowID Column1 Column2 1 A V 2 B C 3 A D 4 B X After that query the result shoud be as below... RowID Column1 Column2 3 A D 1 A V 2 B C 4 B X Its working fine when i use COLLATE for varchar column types but its failing with datetime data types..... Let me know if needed more info Show quote "Hugo Kornelis" wrote: > On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote: > > >I am using this Query in application , its working fine with varchar column > >types when i use COLLATE in QUERY but its not working for Datetime columns > >types, any suggestions? > > > >SELECT TOP 10 * FROM #TempExt > >WHERE eFolderID NOT IN > >(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC > >,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC > > Hi vsr, > > Please elaborate on "not working". More specific: tell us about your > table (CREATE TABLE statement), your data (INSERT statements with sample > data), your requirements (expected output), and the current results from > your query. See www.aspfaq.com/5006 as well. > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) > If it is failing - what is the error message? The query you posted does not
match the column names in the table, The full text of the query would help - the one you post has no collate clause that you are referring to. What difference are you getting with/without the collate clause, Mike John Show quote "vsr" <v**@discussions.microsoft.com> wrote in message news:CFF48A08-0A46-4B64-A226-6036EC49768A@microsoft.com... >I have one Temp Table with one of the columns as defined as datetime > datatype, its a simple Temp table and i am running this query , the > expected > results are first it shoud order by one field and then apply order on > other > field... > > for ex: > RowID Column1 Column2 > 1 A V > 2 B C > 3 A D > 4 B X > > After that query the result shoud be as below... > RowID Column1 Column2 > 3 A D > 1 A V > 2 B C > 4 B X > > Its working fine when i use COLLATE for varchar column types but its > failing > with datetime data types..... > > Let me know if needed more info > > "Hugo Kornelis" wrote: > >> On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote: >> >> >I am using this Query in application , its working fine with varchar >> >column >> >types when i use COLLATE in QUERY but its not working for Datetime >> >columns >> >types, any suggestions? >> > >> >SELECT TOP 10 * FROM #TempExt >> >WHERE eFolderID NOT IN >> >(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC >> >,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC >> >> Hi vsr, >> >> Please elaborate on "not working". More specific: tell us about your >> table (CREATE TABLE statement), your data (INSERT statements with sample >> data), your requirements (expected output), and the current results from >> your query. See www.aspfaq.com/5006 as well. >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) >> No error , its just not ordering as intended...this is the example i gave and
infact my table is also same with different column names (that i mentioned in query), this is the plain query with out COLLATE and i am only adding COLLATE if the column type is of varchar,and everything is working fine when i add COLLATE for varchar columns , but for datetime data types the results are not as intended. Show quote "Mike John" wrote: > If it is failing - what is the error message? The query you posted does not > match the column names in the table, The full text of the query would help - > the one you post has no collate clause that you are referring to. What > difference are you getting with/without the collate clause, > > Mike John > > "vsr" <v**@discussions.microsoft.com> wrote in message > news:CFF48A08-0A46-4B64-A226-6036EC49768A@microsoft.com... > >I have one Temp Table with one of the columns as defined as datetime > > datatype, its a simple Temp table and i am running this query , the > > expected > > results are first it shoud order by one field and then apply order on > > other > > field... > > > > for ex: > > RowID Column1 Column2 > > 1 A V > > 2 B C > > 3 A D > > 4 B X > > > > After that query the result shoud be as below... > > RowID Column1 Column2 > > 3 A D > > 1 A V > > 2 B C > > 4 B X > > > > Its working fine when i use COLLATE for varchar column types but its > > failing > > with datetime data types..... > > > > Let me know if needed more info > > > > "Hugo Kornelis" wrote: > > > >> On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote: > >> > >> >I am using this Query in application , its working fine with varchar > >> >column > >> >types when i use COLLATE in QUERY but its not working for Datetime > >> >columns > >> >types, any suggestions? > >> > > >> >SELECT TOP 10 * FROM #TempExt > >> >WHERE eFolderID NOT IN > >> >(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC > >> >,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC > >> > >> Hi vsr, > >> > >> Please elaborate on "not working". More specific: tell us about your > >> table (CREATE TABLE statement), your data (INSERT statements with sample > >> data), your requirements (expected output), and the current results from > >> your query. See www.aspfaq.com/5006 as well. > >> > >> Best, Hugo > >> -- > >> > >> (Remove _NO_ and _SPAM_ to get my e-mail address) > >> > > > Do you have any compound indexes on the temp table where one column's
direction is ascending and the other descending? If not, then please post complete DDL and some sample data, so we can reproduce your problem. Gert-Jan vsr wrote: Show quote > > No error , its just not ordering as intended...this is the example i gave and > infact my table is also same with different column names (that i mentioned in > query), this is the plain query with out COLLATE and i am only adding COLLATE > if the column type is of varchar,and everything is working fine when i add > COLLATE for varchar columns , but for datetime data types the results are not > as intended. > > "Mike John" wrote: > > > If it is failing - what is the error message? The query you posted does not > > match the column names in the table, The full text of the query would help - > > the one you post has no collate clause that you are referring to. What > > difference are you getting with/without the collate clause, > > > > Mike John > > > > "vsr" <v**@discussions.microsoft.com> wrote in message > > news:CFF48A08-0A46-4B64-A226-6036EC49768A@microsoft.com... > > >I have one Temp Table with one of the columns as defined as datetime > > > datatype, its a simple Temp table and i am running this query , the > > > expected > > > results are first it shoud order by one field and then apply order on > > > other > > > field... > > > > > > for ex: > > > RowID Column1 Column2 > > > 1 A V > > > 2 B C > > > 3 A D > > > 4 B X > > > > > > After that query the result shoud be as below... > > > RowID Column1 Column2 > > > 3 A D > > > 1 A V > > > 2 B C > > > 4 B X > > > > > > Its working fine when i use COLLATE for varchar column types but its > > > failing > > > with datetime data types..... > > > > > > Let me know if needed more info > > > > > > "Hugo Kornelis" wrote: > > > > > >> On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote: > > >> > > >> >I am using this Query in application , its working fine with varchar > > >> >column > > >> >types when i use COLLATE in QUERY but its not working for Datetime > > >> >columns > > >> >types, any suggestions? > > >> > > > >> >SELECT TOP 10 * FROM #TempExt > > >> >WHERE eFolderID NOT IN > > >> >(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC > > >> >,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC > > >> > > >> Hi vsr, > > >> > > >> Please elaborate on "not working". More specific: tell us about your > > >> table (CREATE TABLE statement), your data (INSERT statements with sample > > >> data), your requirements (expected output), and the current results from > > >> your query. See www.aspfaq.com/5006 as well. > > >> > > >> Best, Hugo > > >> -- > > >> > > >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > >> > > > > > > It's very hard to answer questions when you don't give
all the details, and the column names and aliases you are actually using are probably crucial. The name resolution for ORDER BY items is both confusing and buggy, and I suspect there is more than one interpretation of some one of the ORDER BY columns. By adding COLLATE, you make the column reference into an expression, and then there is only one interpretation, so with the datetime column, try changing it from ORDER BY myDTcolumn to ORDER BY myDTcolumn+0. Beware that in SQL Server 2000, correlation names (table aliases) on column references are sometimes ignored in an ORDER BY clause. Steve Kass Drew University vsr wrote: Show quote > No error , its just not ordering as intended...this is the example i gave and > infact my table is also same with different column names (that i mentioned in > query), this is the plain query with out COLLATE and i am only adding COLLATE > if the column type is of varchar,and everything is working fine when i add > COLLATE for varchar columns , but for datetime data types the results are not > as intended. > > "Mike John" wrote: > > >>If it is failing - what is the error message? The query you posted does not >>match the column names in the table, The full text of the query would help - >>the one you post has no collate clause that you are referring to. What >>difference are you getting with/without the collate clause, >> >>Mike John >> >>"vsr" <v**@discussions.microsoft.com> wrote in message >>news:CFF48A08-0A46-4B64-A226-6036EC49768A@microsoft.com... >> >>>I have one Temp Table with one of the columns as defined as datetime >>>datatype, its a simple Temp table and i am running this query , the >>>expected >>>results are first it shoud order by one field and then apply order on >>>other >>>field... >>> >>>for ex: >>>RowID Column1 Column2 >>>1 A V >>>2 B C >>>3 A D >>>4 B X >>> >>>After that query the result shoud be as below... >>>RowID Column1 Column2 >>>3 A D >>>1 A V >>>2 B C >>>4 B X >>> >>>Its working fine when i use COLLATE for varchar column types but its >>>failing >>>with datetime data types..... >>> >>>Let me know if needed more info >>> >>>"Hugo Kornelis" wrote: >>> >>> >>>>On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote: >>>> >>>> >>>>>I am using this Query in application , its working fine with varchar >>>>>column >>>>>types when i use COLLATE in QUERY but its not working for Datetime >>>>>columns >>>>>types, any suggestions? >>>>> >>>>>SELECT TOP 10 * FROM #TempExt >>>>>WHERE eFolderID NOT IN >>>>>(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC >>>>>,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC >>>> >>>>Hi vsr, >>>> >>>>Please elaborate on "not working". More specific: tell us about your >>>>table (CREATE TABLE statement), your data (INSERT statements with sample >>>>data), your requirements (expected output), and the current results from >>>>your query. See www.aspfaq.com/5006 as well. >>>> >>>>Best, Hugo >>>>-- >>>> >>>>(Remove _NO_ and _SPAM_ to get my e-mail address) >>>> >> >> >> But the example you gave does not have a datetime co,umn in the order by.
Suspect the other replies nay have the answer - it is not actualy ordering on the column you may think it is. But in what way "not ordering as intended"? reversed, random, or what? Collate has no meaning when used with a datetime column, but it may be forcing an implicit conversion to varchar which would certainly affect the ordering. Mike John Show quote "vsr" <v**@discussions.microsoft.com> wrote in message news:C6E595B3-ABF4-45C5-A711-48B65C2B7F52@microsoft.com... > No error , its just not ordering as intended...this is the example i gave > and > infact my table is also same with different column names (that i mentioned > in > query), this is the plain query with out COLLATE and i am only adding > COLLATE > if the column type is of varchar,and everything is working fine when i add > COLLATE for varchar columns , but for datetime data types the results are > not > as intended. > > "Mike John" wrote: > >> If it is failing - what is the error message? The query you posted does >> not >> match the column names in the table, The full text of the query would >> help - >> the one you post has no collate clause that you are referring to. What >> difference are you getting with/without the collate clause, >> >> Mike John >> >> "vsr" <v**@discussions.microsoft.com> wrote in message >> news:CFF48A08-0A46-4B64-A226-6036EC49768A@microsoft.com... >> >I have one Temp Table with one of the columns as defined as datetime >> > datatype, its a simple Temp table and i am running this query , the >> > expected >> > results are first it shoud order by one field and then apply order on >> > other >> > field... >> > >> > for ex: >> > RowID Column1 Column2 >> > 1 A V >> > 2 B C >> > 3 A D >> > 4 B X >> > >> > After that query the result shoud be as below... >> > RowID Column1 Column2 >> > 3 A D >> > 1 A V >> > 2 B C >> > 4 B X >> > >> > Its working fine when i use COLLATE for varchar column types but its >> > failing >> > with datetime data types..... >> > >> > Let me know if needed more info >> > >> > "Hugo Kornelis" wrote: >> > >> >> On Thu, 1 Dec 2005 15:52:02 -0800, vsr wrote: >> >> >> >> >I am using this Query in application , its working fine with varchar >> >> >column >> >> >types when i use COLLATE in QUERY but its not working for Datetime >> >> >columns >> >> >types, any suggestions? >> >> > >> >> >SELECT TOP 10 * FROM #TempExt >> >> >WHERE eFolderID NOT IN >> >> >(SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested >> >> >ASC >> >> >,sContractName ASC) ORDER BY service_requested ASC ,sContractName >> >> >ASC >> >> >> >> Hi vsr, >> >> >> >> Please elaborate on "not working". More specific: tell us about your >> >> table (CREATE TABLE statement), your data (INSERT statements with >> >> sample >> >> data), your requirements (expected output), and the current results >> >> from >> >> your query. See www.aspfaq.com/5006 as well. >> >> >> >> Best, Hugo >> >> -- >> >> >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) >> >> >> >> >> On Thu, 1 Dec 2005 16:19:02 -0800, vsr wrote:
Show quote >I have one Temp Table with one of the columns as defined as datetime Hi vsr,>datatype, its a simple Temp table and i am running this query , the expected >results are first it shoud order by one field and then apply order on other >field... > >for ex: >RowID Column1 Column2 >1 A V >2 B C >3 A D >4 B X > >After that query the result shoud be as below... >RowID Column1 Column2 >3 A D >1 A V >2 B C >4 B X > >Its working fine when i use COLLATE for varchar column types but its failing >with datetime data types..... > >Let me know if needed more info Yes. Lots. Here's what I asked you for in my previous post: >> Please elaborate on "not working". More specific: tell us about your You didn;t post CREATE TABLE or INSERT statements. You did post sample>> table (CREATE TABLE statement), your data (INSERT statements with sample >> data), your requirements (expected output), and the current results from >> your query. See www.aspfaq.com/5006 as well. data in tabular format, but that doesn't help me to reproduce the problem. You also posted expected and acquired output in tabular format, but it doesn'tmatch the problem - you're talking about datetime data types, but I see no datetime data in your example. And the column names don;t match with the query you posted either. Please check www.aspfaq.com/5006. And then, post a SQL script that others can run to reproduce the problem. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) I dont' think you don't have to COLLATE for DATETIME datatypes, only CHAR and
VARCHAR I believe ( and their unicode equivalents NCHAR and NVARCHAR ). Try leaving the COLLATE statements off your DATETIME columns? Damien Show quote "vsr" wrote: > I am using this Query in application , its working fine with varchar column > types when i use COLLATE in QUERY but its not working for Datetime columns > types, any suggestions? > > SELECT TOP 10 * FROM #TempExt > WHERE eFolderID NOT IN > (SELECT TOP 20 eFolderID FROM #TempExt ORDER BY service_requested ASC > ,sContractName ASC) ORDER BY service_requested ASC ,sContractName ASC >  |
|||||||||||||||||||||||
Other interesting topics