Order by expression

Har nu prøvet uden UNION ALL, hvor hvor hvert queryfragment fik ODER BY-delen på:

Hvis queryen ser sådan her ud

frag_1
UNION ALL
frag_2
ORDER BY

har jeg altså prøvet

frag_1
ORDER BY
-virker

frag_2
ORDER BY
-virker

Hvad kan problemer være?? fatter banjo af det her :P..

are you saying that the second select WITH order by .... works?


SELECT SUM(Time_spent.hours) AS Time_spent, testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

FROM testing LEFT JOIN Time_spent ON testing.ID = Time_spent.id

WHERE testing.[test platform] =  'CPH_CRTU#2'  AND ucase(testing.[Status]) NOT LIKE ('%DONE%') AND ucase(testing.[Status]) NOT LIKE ('%WAITING%') AND ucase(testing.[Test platform]) NOT LIKE ('%REPORT%')

GROUP BY testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

ORDER BY IIF(ucase(testing.[Product]) LIKE ('%CALIBRATION%'), 1, 2),IIF(ucase(testing.[Product]) LIKE ('%MAINTANANCE%'), 1, 2),IIF(len(testing.[Priority]) > 0, 1, 2) ,testing.[Priority] asc,IIF(len(testing.[TCC target date]) > 0, 1, 2) ,testing.[TCC target date] asc,IIF(len(testing.[PP Demand date]) > 0, 1, 2) ,testing.[PP Demand date] asc,IIF(len(testing.[Duration]) > 0, 1, 2) ,testing.[Duration] asc,IIF(len(testing.[Remaining]) > 0, 1, 2) ,testing.[Remaining] asc

If it does then try it on the first SELECT

You also need to make sure that the resulting column names in BOTH SELECTS are the same as in the ORDER BY fields!!!

If you run the query without the order by so (that it works) then you can see the column names. These are the names you have to use when you sort

Order by virker seperat på begge selects, men når jeg prøver at gøre det på dem begge samlet (ved hjælp af UNION ALL), virker det ikke.. Det var det jeg prøvede at vise i min første kommentar.. Jeg bruger den samme order by flere steder i mit system, hvor jeg selecter de samme kolonner..

Jeg synes, det er lidt mærkeligt, at det virker på 1. og 2, men ikke på dem samlet..

could I see your database?


eksperten@NOSPAMsanthell.dk

remove NOSPAM

Try changing your order by to the names of the columns you see in the query when you do NOT use order by

It might be something like

ORDER BY IIF(ucase([Product]) ....
in other words WITHOUT the table names

Databasen er sendt via mail..

Det virkede ikke at udelade table names :(..

Here is a solution which works although it s not maybe the most elegant

Make a query WITHOUT the order by

Now make another query where you select all fields from the first query and then add the order by.

If you are using this in ASP then you can always make the select on the query instead of the writing the whole SQL in ASP.

Hvordan ville dette se ud i praksis??

?
not sure what you mean there!

Do you know how to make a query in Access?

Save all of the SQL without the ORDER BY in a query. Lets say the query has the name myQuery

Now you would use

SELECT * FROM myQuery
ORDER BY IIF(ucase(testing.[Product]) LIKE ('%CALIBRATION%'), 1, 2),IIF(ucase(testing.[Product]) LIKE ('%MAINTANANCE%'), 1, 2),IIF(len(testing.[Priority]) > 0, 1, 2) ,testing.[Priority] asc,IIF(len(testing.[TCC target date]) > 0, 1, 2) ,testing.[TCC target date] asc,IIF(len(testing.[PP Demand date]) > 0, 1, 2) ,testing.[PP Demand date] asc,IIF(len(testing.[Duration]) > 0, 1, 2) ,testing.[Duration] asc,IIF(len(testing.[Remaining]) > 0, 1, 2) ,testing.[Remaining]

Betyder det, at jeg skal eksekvere min sql: rs.open sql,... og derefter bruge rs-værdien som myQuery? - altså noget i retning af dette??

sql = "SELECT * FROM (...)"
rs.open sql,...

sql_with_orderpart = "SELECT * FROM " & rs & "
ORDER BY IIF(ucase(testing.[Product]) LIKE ('%CALIBRATION%'), 1, 2),IIF(ucase(testing.[Product]) LIKE ('%MAINTANANCE%'), 1, 2),IIF(len(testing.[Priority]) > 0, 1, 2) ,testing.[Priority] asc,IIF(len(testing.[TCC target date]) > 0, 1, 2) ,testing.[TCC target date] asc,IIF(len(testing.[PP Demand date]) > 0, 1, 2) ,testing.[PP Demand date] asc,IIF(len(testing.[Duration]) > 0, 1, 2) ,testing.[Duration] asc,IIF(len(testing.[Remaining]) > 0, 1, 2) ,testing.[Remaining]"

Jeg taget til Afrika i 2 uger senere i dag, så det kan være jeg får lidt svært ved at svare lige med det sammen (skal først afsted kl. 2)

In Access you can make your query and SAVE it with a name. A bit like a table. Then you can use the query in your ASP code as if it were a table


So if your SQL before was
"SELECT SUM(Time_spent.hours) AS Time_spent, testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

FROM multisystem INNER JOIN (testing LEFT JOIN Time_spent ON testing.ID = Time_spent.id) ON multisystem.name = testing.[Test platform]

WHERE multisystem.member_1 = 'CPH_CRTU#2'  OR multisystem.member_2 =  'CPH_CRTU#2'  OR multisystem.member_3 =  'CPH_CRTU#2'  OR multisystem.member_4 =  'CPH_CRTU#2'  AND ucase(testing.[Status]) NOT LIKE ('%DONE%') AND ucase(testing.[Status]) NOT LIKE ('%WAITING%') AND ucase(testing.[Test platform]) NOT LIKE ('%REPORT%')

GROUP BY testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

UNION ALL

SELECT SUM(Time_spent.hours) AS Time_spent, testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

FROM testing LEFT JOIN Time_spent ON testing.ID = Time_spent.id

WHERE testing.[test platform] =  'CPH_CRTU#2'  AND ucase(testing.[Status]) NOT LIKE ('%DONE%') AND ucase(testing.[Status]) NOT LIKE ('%WAITING%') AND ucase(testing.[Test platform]) NOT LIKE ('%REPORT%')

GROUP BY testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

ORDER BY IIF(ucase(testing.[Product]) LIKE ('%CALIBRATION%'), 1, 2),IIF(ucase(testing.[Product]) LIKE ('%MAINTANANCE%'), 1, 2),IIF(len(testing.[Priority]) > 0, 1, 2) ,testing.[Priority] asc,IIF(len(testing.[TCC target date]) > 0, 1, 2) ,testing.[TCC target date] asc,IIF(len(testing.[PP Demand date]) > 0, 1, 2) ,testing.[PP Demand date] asc,IIF(len(testing.[Duration]) > 0, 1, 2) ,testing.[Duration] asc,IIF(len(testing.[Remaining]) > 0, 1, 2) ,testing.[Remaining] asc"


it will now be

"SELECT * from myQuery ORDER BY
ORDER BY IIF(ucase([Product]) LIKE ('%CALIBRATION%'), 1, 2),IIF(ucase([Product]) LIKE ('%MAINTANANCE%'), 1, 2),IIF(len([Priority]) > 0, 1, 2) ,[Priority] asc,IIF(len([TCC target date]) > 0, 1, 2) ,[TCC target date] asc,IIF(len([PP Demand date]) > 0, 1, 2) ,[PP Demand date] asc,IIF(len([Duration]) > 0, 1, 2) ,[Duration] asc,IIF(len([Remaining]) > 0, 1, 2) ,[Remaining] asc"



So the query which is saved in Access with the name myQuery contains the SQL


"SELECT SUM(Time_spent.hours) AS Time_spent, testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]

FROM testing LEFT JOIN Time_spent ON testing.ID = Time_spent.id

WHERE testing.[test platform] =  'CPH_CRTU#2'  AND ucase(testing.[Status]) NOT LIKE ('%DONE%') AND ucase(testing.[Status]) NOT LIKE ('%WAITING%') AND ucase(testing.[Test platform]) NOT LIKE ('%REPORT%')

GROUP BY testing.[Product],testing.[TOM TR name],testing.[Test sheet links],testing.[TCC target date],testing.[PP Demand date],testing.[Duration],testing.[Remaining],testing.[Time spend],testing.[Last updated],testing.[Status],testing.[EUT available],testing.[Testing started],testing.[Done by],testing.[Comments & AP],testing.[PCM],testing.[Type],testing.[Stream & position],testing.[Test request description],testing.[Test Platform],testing.[Progress status],testing.[Priority],testing.[ID]"


So you still only use one recordset in your code



Have a good holiday in Afrika, hope the lions dont get you :o)

ok.. det laver jeg så - selvom det ikke er så pænt :p..

Jeg har ikke lige tid til at tjekke, om det virker, men det regner jeg med, så jeg accepterer dit svar.. tak for hjælpen..

thanks
post a comment if you still need help when you get back off your holidays