Column Headers in Subquery?
-
I'm trying to get column headers to display in results from a subquery. The reason I'm trying to get headers is to automate this job via a DTS package. The package doesn't seem to like where the headers aren't named in the transformation step. Any help is appreciated. Here's my sql code
SELECT D.ProjectID,
(SELECT TOP 1 AttResult as ATTResult FROM History H
WHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC ),
(SELECT TOP 1 CallDateTime as CallDateTime FROM History H
WHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC),
(SELECT TOP 1 CRC as CRC FROM History H
WHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC),
D.PhoneNum,
(SELECT TOP 1 ProjName as ProjectName FROM History H
WHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC),
(SELECT TOP 1 ConnectTime as ConnectTime FROM History H
WHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC),
D.SourceID, D.Field1
FROM Dial D
WHERE D.ProjectID = 635
ORDER BY D.Field1, D.PhoneNum
Thanks
Ron
-
You must set an alias after the paranthesis, like this
SELECT D.ProjectID,
(SELECT TOP 1 AttResult
as ATTResultFROM History HWHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC ) AS AttResult,
(SELECT TOP 1 CallDateTime
as CallDateTimeFROM History HWHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC) as CallDateTime,
(SELECT TOP 1 CRC
as CRCFROM History HWHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC) as CRC,
D.PhoneNum,
(SELECT TOP 1 ProjName
as ProjectNameFROM History HWHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC) as ProjectName,
(SELECT TOP 1 ConnectTime
as ConnectTimeFROM History HWHERE H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum ORDER BY CallDateTime DESC) as ConnectTime,
D.SourceID, D.Field1
FROM Dial D
WHERE D.ProjectID = 635
ORDER BY D.Field1, D.PhoneNum
That should do it.
I have a thought about your query. Since you have 5 subqueries, I think the performance of you query must be bad. The query must be slow, right?
See of this rewritten query performs better regarding time and speed against your original data and that the rewritten query fetches the same data.
SELECT D.ProjectID,
MAX(H.AttResult) ATTResult,
MAX(H.CallDateTime) CallDateTime,
MAX(H.CRC) CRC,
D.PhoneNum,
MAX(H.ProjName) ProjectName,
MAX(H.ConnectTime) ConnectTime,
D.SourceID,
D.Field1
FROM Dial D
WHERE D.ProjectID = 635
LEFT JOIN History H ON H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum
GROUP BY D.ProjectID,
D.PhoneNum,
D.SourceID,
D.Field1
ORDER BY D.Field1,
D.PhoneNum
Good luck!
N 56°04'39.16"
E 12°55'05.25" -
Peter
Much thanks, for some reason (I have to admit I'm not great at JOIN statements) the query threw a syntax error. Through some trial and error I got this to work:
SELECT D.ProjectID,
MAX(H.AttResult) ATTResult,
MAX(H.CallDateTime) CallDateTime,
MAX(H.CRC) CRC,
D.PhoneNum,
MAX(H.ProjName) ProjectName,
MAX(H.ConnectTime) ConnectTime,
D.SourceID,
D.Field1
FROM Dial D, History H
WHERE D.ProjectID = 635 AND H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum
GROUP BY D.ProjectID,
D.PhoneNum,
D.SourceID,
D.Field1
ORDER BY D.Field1,
D.PhoneNum
and you were absolutely right, this ran much faster!
-
Sorry, my bad!
The WHERE and the LEFT JOIN should switch places.
SELECT D.ProjectID,
MAX(H.AttResult) ATTResult,
MAX(H.CallDateTime) CallDateTime,
MAX(H.CRC) CRC,
D.PhoneNum,
MAX(H.ProjName) ProjectName,
MAX(H.ConnectTime) ConnectTime,
D.SourceID,
D.Field1
FROM Dial D
LEFT JOIN History H ON H.DialID = D.DialID AND H.PhoneNum = D.PhoneNum
WHERE D.ProjectID = 635
GROUP BY D.ProjectID,
D.PhoneNum,
D.SourceID,
D.Field1
ORDER BY D.Field1,
D.PhoneNum
The LEFT JOIN is crucial to retrieve all rows from Dial table. Using CROSS JOIN (as you do now) or INNER JOIN only matches all rows from both tables where there are rows in history too. If you absolutely know that there always will be at least one row in history table that matches a row in Dial table, the LEFT JOIN can be replaced with a INNER JOIN.
Can you please post the time differences here?
1. Old query (with subqueries) in seconds
2. My query with LEFT JOIN or INNER JOIN?
3. Your "old SQL syntax" query with CROSS JOIN?
N 56°04'39.16"
E 12°55'05.25" -
Hmm, now I'm pulling too much data back. My objective is to get the MAX or most recent record from the history table and ONLY that record. This query does that for the CallDateTime column but not for the CRC column.
In other words I want to return the most recent record from the history table for all records in that table where there is a match on the dial table for the account number and the phone number.
Thanks again for your help...
-
SELECT D.ProjectID,
H.AttResult ATTResult,
H.CallDateTime CallDateTime,
H.CRC CRC,
D.PhoneNum,
H.ProjName ProjectName,
H.ConnectTime ConnectTime,
D.SourceID,
D.Field1
FROM Dial D
INNER JOIN (
SELECT DialID,
MAX(CallDateTime) maxDT
FROM History
) z ON z.DialID = D.DialID
INNER JOIN History H ON H.DialID = z.DialID AND H.CallDateTime = z.maxDT
WHERE D.ProjectID = 635
ORDER BY D.Field1,
D.PhoneNum
Will this query suffice? It not, you will have to post table structures here.
N 56°04'39.16"
E 12°55'05.25" -
This is what seems to be doing the trick and here are the run times, quite a bit different!
SELECT D.ProjectID,
D.Status,
MAX(H.CallDateTime) CallDateTime,
D.CRC,
D.PhoneNum,
H.ProjName,
MAX(H.ConnectTime) ConnectTime,
D.SourceID,
D.Field1
FROM Dial D
INNER JOIN History H ON H.DialID = D.DialID
WHERE D.ProjectID = 635
GROUP BY D.ProjectID,
D.Status,
D.PhoneNum,
D.CRC,
D.SourceID,
H.ProjName,
D.Field1
ORDER BY D.Field1,
D.PhoneNum
1. Old query (with subqueries) in seconds = ~480 seconds (8min)
2. Peter's query with LEFT JOIN or INNER JOIN? = ~13 seconds
Thanks again!
Ron
-
I believe if you create indexes on both H.DialID and D.DialID run time must fall under 1 second.
Not to mention an index on D.ProjectID, of course.
_____________
Code for TallyGenerator -
Hopefully the last issue in this thread, the output from the last query above is putting " " around some of the data and causing issues with processing the file. Any hints on how to get rid of these quotes? Here's what the output looks like:
635,2,2006-07-07 13:11:44,LIVE,"201200xxxx","DTV UFC61",40,"801658xxxx","000280222"
-
I think I may have found this in the DTS transformation Define Columns fuction and changing the drop downs to non quotable.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply