Table Will Not Sort - 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

General SQL Server Forums

New to SQL Server Programming

Table Will Not Sort

Author

Topic

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-04 : 04:09:27

I have quite a long SP which is made up of various tables, temp tables, etc.

I am creating 11 physical tables which i am using to create a DTS package to auto output to an excel spreadsheet (template for a report).

The problem is although i have used ORDER BY statements to sort the data in ascending order the data isnt sorted.

Heres an example of one of the table outputs

--Create Table of Managers
DELETE FROM Weekly_Term_Code_Output7
INSERT INTO Weekly_Term_Code_Output7
SELECT #Temp_Agent.Manager
FROM #temp5 INNER JOIN
#Temp_Agent ON #temp5.agent_login = #Temp_Agent.agent_login
WHERE (NOT (#Temp_Agent.Manager IS NULL))
GROUP BY #Temp_Agent.Manager
ORDER BY #Temp_Agent.Manager

ive also tried placing an order by statement in the output to excel in the DTS package but this also doesnt work?

Any ideas anyone??

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-04 : 04:10:13

Sorthing else ive noticed is all the text in excel has a leading ' in every cell

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 04:11:49

[code]-- Create Table of Managers
TRUNCATE TABLE Weekly_Term_Code_Output7

INSERT Weekly_Term_Code_Output7
SELECT DISTINCT ta.Manager
FROM #temp5 AS t5
INNER JOIN #Temp_Agent AS ta ON ta.agent_login = t5.agent_login
WHERE ta.Manager IS NOT NULL
ORDER BY ta.Manager[/code]

E 12°55'05.25"
N 56°04'39.16"

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-04 : 04:23:45

Many thanks for your help this worked.

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-04 : 06:03:11

I cant seem to get this query to work i get a Invalid column name 'Summary'.

Im not sure if this is because i am linking columns to create my summary?

TRUNCATE TABLE Weekly_Term_Code_Output11
INSERT Weekly_Term_Code_Output11
SELECT DISTINCT #temp5.App_Name, #temp5.term_code, #temp5.display_text, #temp5.App_Name+' '+#temp5.term_code+' '+#temp5.display_text as Summary,#temp5.kvi, #temp5.min_re, #temp5.max_re
FROM #temp5
GROUP BY #temp5.App_Name, #temp5.term_code, #temp5.display_text, #temp5.Summary,#temp5.kvi, #temp5.min_re, #temp5.max_re
ORDER BY #temp5.App_Name, #temp5.term_code
GO

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-10-04 : 06:07:26

quote:
Originally posted by jtwork

I cant seem to get this query to work i get a Invalid column name 'Summary'.

Im not sure if this is because i am linking columns to create my summary?

TRUNCATE TABLE Weekly_Term_Code_Output11
INSERT Weekly_Term_Code_Output11
SELECT DISTINCT #temp5.App_Name, #temp5.term_code, #temp5.display_text, #temp5.App_Name+' '+#temp5.term_code+' '+#temp5.display_text as Summary,#temp5.kvi, #temp5.min_re, #temp5.max_re
FROM #temp5
GROUP BY #temp5.App_Name, #temp5.term_code, #temp5.display_text, ~~#temp5.Summary,~~#temp5.kvi, #temp5.min_re, #temp5.max_re
ORDER BY #temp5.App_Name, #temp5.term_code
GO

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-10-04 : 06:38:56

Remember that is not allowed in SQL Server 2005.

I just tought you how to use ALIASES. Why do you revert from that path?

TRUNCATE TABLE	Weekly_Term_Code_Output11

INSERT		Weekly_Term_Code_Output11
SELECT DISTINCT	App_Name,
		term_code,
		display_text,
		App_Name + ' ' + term_code + ' ' + display_text as Summary,
		kvi,
		min_re,
		max_re
FROM		#temp5
ORDER BY	App_Name,
		term_code

E 12°55'05.25"
N 56°04'39.16"

jtwork
Yak Posting Veteran

82 Posts

Posted - 2007-10-04 : 06:43:04

Thank you. I have already written my code and will tidy up once i have the code working.

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-10-04 : 22:40:01

This is potentially doomed to fail no matter what you do to your insert statement. The order is determined by the select, not by the insert. Any order by on an insert serves only to allocate an identity number in that order. You will need to put the order by in the DTS where you select from your final table.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources