Query modification - 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

SQL Server 2000 Forums

Transact-SQL (2000)

Query modification

Author

Topic

rookie_sql
Constraint Violating Yak Guru

443 Posts

Posted - 2006-11-22 : 11:57:33

Hi i have a View that uses the same source table,
i would like just to do the query as one and not to use 2 views at all i want to try and put this into a DTS
package. The end result of this query is feed into a table/

Here is the code

---Name of this view is svw_icss_status its created from the source table dbo.tbl_raw_icss_call_actions

SELECT source_system, log_number AS unique_id,
MAX(action_sequence_no) AS sequence,
MAX(event_date) AS status_dt
FROM dbo.tbl_raw_icss_call_actions
GROUP BY source_system, log_number

--This is the 2nd view were it uses the first view to join to the same source table (dbo.tbl_raw_icss_call_actions).

SELECT dbo.svw_icss_status.source_system,
dbo.svw_icss_status.unique_id,
1 AS closed_ind,
dbo.svw_icss_status.status_dt AS close_dt
FROM dbo.tbl_raw_icss_call_actions INNER JOIN dbo.svw_icss_status ON
dbo.tbl_raw_icss_call_actions.log_number = dbo.svw_icss_status.unique_id AND
dbo.tbl_raw_icss_call_actions.action_sequence_no = dbo.svw_icss_status.sequence
GROUP BY dbo.svw_icss_status.source_system, dbo.svw_icss_status.unique_id, dbo.svw_icss_status.status_dt

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-22 : 12:07:11

If you don't want View, why can't you use first query as a derived table to the second one?

SELECT dbo.svw_icss_status.source_system, 
dbo.svw_icss_status.unique_id, 
1 AS closed_ind, 
dbo.svw_icss_status.status_dt AS close_dt
FROM dbo.tbl_raw_icss_call_actions 
INNER JOIN 
(
SELECT source_system, log_number AS unique_id, 
MAX(action_sequence_no) AS sequence, 
MAX(event_date) AS status_dt
FROM dbo.tbl_raw_icss_call_actions
GROUP BY source_system, log_number
) as svw_icss_status ON 
dbo.tbl_raw_icss_call_actions.log_number = dbo.svw_icss_status.unique_id AND 
dbo.tbl_raw_icss_call_actions.action_sequence_no = dbo.svw_icss_status.sequence
GROUP BY dbo.svw_icss_status.source_system, dbo.svw_icss_status.unique_id, dbo.svw_icss_status.status_dt

Harsh Athalye
India.
"Nothing is Impossible"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-11-22 : 12:08:15

The SQL Optimizer is smart enough to run this query as "one".
But I am not sure wether or not the logic in your code is ok.
It is not guaranteed that both MAX will select from the same row.
But it has no meaning since you "distinct" the values anyway by grouping them!

This code will do the same thing as your OP and Harsh's suggestion.

SELECT		source_system,
		log_number AS unique_id,
		1 AS closed_ind,
		MAX(event_date) AS close_dt
FROM		dbo.tbl_raw_icss_call_actions
GROUP BY	source_system,
		log_number

Peter Larsson
Helsingborg, Sweden

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources