[Resolved] Need to limit query

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 2005 Forums

Transact-SQL (2005)

[Resolved] Need to limit query

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-01-19 : 09:10:56

I have following sp:

select Batch.Name, Batch.ReportDate, Job.CompanyJobId, EventStatusType.Name, Account.UserName
from   [VGIWPSQL2].goLabor30.dbo.batch 
                  inner join [VGIWPSQL2].goLabor30.dbo.Event on Event.BatchGuid = Batch.BatchGuid
                  inner join [VGIWPSQL2].goLabor30.dbo.Job on Job.JobGuid = Event.JobGuid               
                  inner join [VGIWPSQL2].goLabor30.dbo.EventStatusType on EventStatusType.EventStatusTypeGuid = Event.EventStatus
	          inner join [VGIWPSQL2].goLabor30.dbo.Account on Account.AccountGuid = Event.AccountGuid

where   (@Owner IS Null or Account.username = @Owner) and  
        (@DateFrom IS  Null OR Batch.Reportdate >=  @DateFrom)and
        (@DateTo IS Null OR Batch.ReportDate <= @DateTo) and
        (@Job IS Null or Job.CompanyJobId = @Job) and
        (@Status IS Null or EventStatusType.Name = @Status)
order by Batch.Name

In the JOB table (line marked in red) there could be multiple occurences for same job and the result of the query is multiple lines with same Batch.Name. I just need one record for each distinct Batch.Name

Example:

rgdfrpj-599 Thu 2/11/10 2010-02-11 00:00:00.000 600560 Loaded to ERP RGDFRPJ
rgdfrpj-599 Thu 2/11/10 2010-02-11 00:00:00.000 600560 Loaded to ERP RGDFRPJ
rgdfrpj-599 Thu 2/11/10 2010-02-11 00:00:00.000 600560 Loaded to ERP RGDFRPJ
rgdfrpj-599 Thu 2/11/10 2010-02-11 00:00:00.000 600560 Loaded to ERP RGDFRPJ

How could I do this? Thank you very much.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 09:17:36

Make a SELECT to get the needed DISTINCT values of Job table.
Then put this SELECT statement between parenthesis and use it instaed of the original table.
for example INNER JOIN (SELECT ... FROM [VGIWPSQL2].goLabor30.dbo.Job GROUP BY ...) AS Job on Job.JobGuid = Event.JobGuid

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-01-19 : 10:15:12

Now have following:

select Batch.Name, Batch.ReportDate, Job.CompanyJobId, EventStatusType.Name, Account.UserName
from   [VGIWPSQL2].goLabor30.dbo.batch 
                  inner join [VGIWPSQL2].goLabor30.dbo.Event on Event.BatchGuid = Batch.BatchGuid
                  inner join (Select distinct(CompanyJobId) from [VGIWPSQL2].goLabor30.dbo.Job group by Job.CompanyJobId) as Job on Job.JobGuid = Event.JobGuid                  inner join [VGIWPSQL2].goLabor30.dbo.EventStatusType on EventStatusType.EventStatusTypeGuid = Event.EventStatus
	          inner join [VGIWPSQL2].goLabor30.dbo.Account on Account.AccountGuid = Event.AccountGuid

where   (@Owner IS Null or Account.username = @Owner) and  
        (@DateFrom IS  Null OR Batch.Reportdate >=  @DateFrom)and
        (@DateTo IS Null OR Batch.ReportDate <= @DateTo) and
        (@Job IS Null or Job.CompanyJobId = @Job) and
        (@Status IS Null or EventStatusType.Name = @Status)
order by Batch.Name

Processing the query getting error (on line marked in red)

Invalid column name 'JobGuid'.

I cannot figure out why. Thank you.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 10:18:13

JobGuid has to be a column inside your derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 10:19:02

I mean it has to be a member of the SELECT list inside your derived table.

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-01-19 : 11:29:46

I had to change my query a little bit. The table that have multiple occurences is table "Event".

This is the query I have:

select Batch.Name, Batch.ReportDate, Event.BatchGuid  
from   [VGIWPSQL2].goLabor30.dbo.batch 
                  inner join (Select distinct(BatchGuid), JobGuid, EventStatus, AccountGuid from [VGIWPSQL2].goLabor30.dbo.Event group by BatchGuid, JobGuid, EventStatus, AccountGuid) as Event on Event.BatchGuid = Batch.BatchGuidorder by Batch.Name

I am getting multiple identical records from the Event table, been using distinct keyword but it does not seem to help. In the Batch file there is just one record.

RGDFRAJ-522 THU 1/14/10 2010-01-14 00:00:00.000 93FC4338-5694-4D52-99F1-D2D54C7C4F37
RGDFRAJ-522 THU 1/14/10 2010-01-14 00:00:00.000 93FC4338-5694-4D52-99F1-D2D54C7C4F37

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 12:20:23

We need the DDL of table Event and some sample duplicate data.
Then we can help you getting distinct rows without any further POST-PING-PONG

No, you're never too old to Yak'n'Roll if you're too young to die.

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2011-01-19 : 13:31:04

I was able to work around my issue using following query:

select distinct(Event.BatchGuid), Batch.Name, Batch.ReportDate, Job.CompanyJobId, EventStatusType.Name, EventApprovalLevel.Name
from   [VGIWPSQL2].goLabor30.dbo.event 
                  inner join [VGIWPSQL2].goLabor30.dbo.Batch on Batch.BatchGuid = Event.BatchGuid
                  inner join [VGIWPSQL2].goLabor30.dbo.Job on Job.JobGuid = Event.JobGuid
                  inner join [VGIWPSQL2].goLabor30.dbo.EventStatusType on EventStatusType.EventStatusTypeGuid = Event.EventStatus
	              inner join [VGIWPSQL2].goLabor30.dbo.Account on Account.AccountGuid = Event.AccountGuid

Webfred, Thank you for your help (vielen Dank fur Ihre Hilfe).

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2011-01-19 : 14:55:11

Es war mir ein Vergnügen (it was my pleasure)

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources