Confirm that this methodology will work.

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

SQL Server Development (2000)

Confirm that this methodology will work.

Author

Topic

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-07-31 : 18:20:30

Can the "Exists" Statment be used in the where clause like in the following method. I am going to use a CURSOR on the results so I can not use a "IF EXISTS" statment, I actually need the record set. I do not get a error, but I want to make sure it will work as I expect before I move the Stored Procedure into a active enviorment

SELECT a.GenerationdateID,a.EmployerID,a.Generationdate,a.BillingCycleID
FROM TAC_Generationdate a
Where not exists(Select Generationdate From TAC_EmployeeHour a3 where a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID)

I am trying to retrieve all the records only if there is no record that exists in the TAC_EMployeeHour table. It appears to work, but can someone please verify that I can this will function properly.

timmy
Master Smack Fu Yak Hacker

1242 Posts

Posted - 2006-07-31 : 19:31:23

It should work OK, but will probably work better if you use a LEFT JOIN:

SELECT a.GenerationdateID,a.EmployerID,a.Generationdate,a.BillingCycleID
FROM TAC_Generationdate a LEFT JOIN TAC_EmployeeHour a3 ON a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID
WHERE a3.EmployerID IS NULL

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2006-07-31 : 20:29:15

timmy's solution will probably be more efficient but both methods will give the same results (given that timmy coded right). But have you considered not using a cursor? Most people here are strongly opposed to cursors and usually there is a way around it.

--
Lumbago
"Real programmers don't document, if it was hard to write it should be hard to understand"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2006-07-31 : 21:27:06

quote:
Originally posted by Vinnie881

Can the "Exists" Statment be used in the where clause like in the following method. I am going to use a CURSOR on the results so I can not use a "IF EXISTS" statment, I actually need the record set. I do not get a error, but I want to make sure it will work as I expect before I move the Stored Procedure into a active enviorment

SELECT a.GenerationdateID,a.EmployerID,a.Generationdate,a.BillingCycleID
FROM TAC_Generationdate a
Where not exists(Select Generationdate From TAC_EmployeeHour a3 where a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID)

I am trying to retrieve all the records only if there is no record that exists in the TAC_EMployeeHour table. It appears to work, but can someone please verify that I can this will function properly.

Asking questions is great, and we are happy to help, but always remember that the best way to verify something is not to ask someone, but to test it. That skill is more valuable than memorizing syntax or definitions or anything specific about any programming language; the methodology of testing your ideas is the most important thing that any programmer can learn.

Disconnect from your live database, create a playground database (or log into one), create two tables, fill them up with a small set of sample data, and try it out. Test all possibilities, different ways of writing the SQL, and work with a small set of data that covers all possibilities so that you can verify the different ways of doing things and so that you can easily verify the results. Then you can be confident that when you apply your technique to your real data, it will work well and that you fully understand it.

- Jeff

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-01 : 03:04:04

Thank you very much for the responses. I tried to test this on my end prior to posting, and it appeard to work. The reason I posted this was simply because I never used the "Exists" in this manor before, and wanted to be safe not sorry. In regards to the cursor method, I do not believe there is a way around it in my scenerio, but I would be more then happy to be proven wrong, and pointed in the correct direction. I will post the t-sql shortly.

Vinnie881
Master Smack Fu Yak Hacker

1231 Posts

Posted - 2006-08-01 : 14:25:04

Here is the Syntax, Please let me know if there is a way to do all this w/o a cursor.

Declare @GenerationdateID int,@EmployerID int,@Generationdate datetime,@EStatusID int,@BillingCycleID int
Declare dbcursor cursor
READ_ONLY
FOR
SELECT a.GenerationdateID,a.EmployerID,a.Generationdate,a.BillingCycleID
FROM TAC_Generationdate a inner join TMI_Plan b on
a.PlanID = b.PlanID inner Join TMI_BenefitType c
on b.BenefitTypeID = c.BenefitTypeID
WHERE c.BillingTypeID = 2 and not exists(Select Generationdate From TAC_EmployeeSetRate a3 where a3.EmployerID = a.EmployerID and A3.Generationdate = a.Generationdate and a3.BillingCycleID = a.BillingCycleID)
OPEN dbcursor
FETCH NEXT FROM DBcursor INTO @GenerationdateID,@EmployerID,@Generationdate,@BillingCycleID
WHILE (@@fetch_status <> -1)
BEGIN
If Not exists (Select a.Generationdate From TAC_EmployeeSetRate a where a.EmployerID = @EmployerID and a.Generationdate = @Generationdate and a.BillingCycleID = @BillingCycleID)
Begin
IF (Select top 1 EstatusID from TMI_EmployerStatus b
where b.EffectiveDate <= @Generationdate and b.EmployerID = @EmployerID
order by b.EffectiveDate desc) = 1
BEGIN
Insert Into TAC_EmployeeSetRate(Generationdate,EmployerID,EmployeeID,Fee,StatusID,GeneratedStatusID,BillingCycleID)
Select @Generationdate,@EmployerID,a1.EmployeeID,0,isnull((Select Top 1 a2.EStatusID From TMI_EmployeeStatus a2 Where a2.EffectiveDate <= @Generationdate and a2.EmployeeID = a1.EmployeeID order by a2.EffectiveDate desc),99),
isnull((Select Top 1 a2.EStatusID From TMI_EmployeeStatus a2 Where a2.EffectiveDate <= @Generationdate and a2.EmployeeID = a1.EmployeeID order by a2.EffectiveDate desc),99),@BillingCycleID
From TMI_Employee a1
Where a1.EmployerID = @EmployerID
END
end
FETCH NEXT FROM DBcursor INTO @GenerationdateID,@EmployerID,@Generationdate,@BillingCycleID
END

Close dbcursor;
DeAllocate dbcursor;

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources