Use of EXISTS when data required?

[willydude]

All of the variables below, @Client_PK_ID, @FundingCode, etc. are required EXCEPT for @TimeStudyPercent. A client might have one, but then again, they might not, which is OK too.

If they have one, I need to pull their percent from the tbl_Clients_ClientTimeStudy. If they do not have one, then do not pull anything and the variable will be set to NULL.

The problem is, even if they do not/should not have a TS%, the sproc is being killed and nothing is put into the @Client_PK_ID variable. I have tried numerous configurations with WHERE, FROM and also the parenthesis. Some have been syntax correct, but logically wrong.

SELECT @Client_PK_ID =
	tbl_Clients_ClientBasicInfo.Client_PK_ID,
	@FundingCode =
	tbl_Clients_ClientFundingAndProgramSources.FundingCode,
	@AverageWage =
	tbl_Clients_ClientWageRates.AverageWage,
	@FringeWage =
	tbl_Clients_ClientWageRates.FringeWage

So far, so good. But the following is killing sproc even when TS% is not req'd

	Select @TimeStudyPercent =    
	tbl_Clients_ClientTimeStudy.TimeStudyPercent

Following is good.

FROM tbl_Clients_ClientBasicInfo
	JOIN
	tbl_Clients_ClientFundingAndProgramSources
	ON
	tbl_Clients_ClientBasicInfo.Client_PK_ID = 
		tbl_Clients_ClientFundingAndProgramSources.Client_PK_ID
		JOIN
		tbl_Clients_ClientTimeStudy
		ON
		tbl_Clients_ClientBasicInfo.Client_PK_ID = 
			tbl_Clients_ClientTimeStudy.Client_PK_ID
		JOIN
		tbl_Clients_ClientWageRates
		ON
		tbl_Clients_ClientBasicInfo.Client_PK_ID = 
			tbl_Clients_ClientWageRates.Client_PK_ID
where tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

Problem continues

AND  --<<also tried WHERE EXISTS
EXISTS   (SELECT @TimeStudyPercent FROM    
			tbl_Clients_ClientTimeStudy
where tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber)

Should I even being using EXISTS in this situation?

As always, thanks.

Bill

 

[bborissov]

SELECT @TimeStudyPercent
       FROM tbl_Clients_ClientTimeStudy
where tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

Can't you see what is wrong?

I am surprised you didn't have errors here.
You use tbl_Clients_ClientTimeStudy in your FROM clause but and tbl_Clients_ClientBasicInfo in your WHERE BUT didn't JOIN these tables. Usually when you use EXISTS the contruction is:

SELECT * 
       FROM OneTable
INNER JOIN Orher ON .....
WHERE ..

And BTW you have no need to use that EXISTS in WHERE clause. You already have INNER JOIN between tbl_Clients_ClientBasicInfo and tbl_Clients_ClientTimeStudy in your main query, so if there is NO records in tbl_Clients_ClientTimeStudy you will not get any resulting records.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[willydude]

Boris, thanks for the help. I don’t believe I explained the situation well enough.

Let me give a “real life” example.

I have the following tables;
tblClients: contains Dennis and Bob.

tblJobSteps: contains many non-time studied jobs and one time studied job (199-9381-21). All job step records (for time studied steps or not) MUST be set up here before they can be set up in the time study table (if required) or entered into the timesheet entry table below.

tblTimeStudies: contains records that “link” a specific client to a specific job (i.e. Dennis to 199-9381-21). Bob does not work on any time studied jobs and thus has no records in this table. A client may have none, one or many different time studied job records.

tblTimeSheetEntry.
------------------------------
The day begins.

Dennis and Bob both work on non-time studied jobs. This is OK and is the majority of the work done. Remember, for non-time studied jobs, they do not need a record set up in the TimeStudy table.

A week later, Dennis begins to work on his time studied job. When entering timesheets, I get to Dennis’ entry and see that he did the sweeping time studied job. I enter his client number (@ClientNumber) and the respective time studied customer, job and step numbers, (199-9381-21). Since Dennis IS set for this job, the entry goes in. This is good. Keep in mind that Dennis also works on non-time studied jobs thru the day and/or week. These also are accepted into the system.

Now, I get to Bob’ timesheet. Bob’s timesheet says that he worked on 199-9381-21 so I enter the required numbers. But the entry is rejected. This is good. Bob DOES NOT have a record set up for this job in the TimeStudy table so the entry should be rejected. I call the shop, they give me the correct NON-time studied customer-job-step number, I enter it, and it goes in. All of Bob’s NON-time studied jobs are accepted as they should be.

The above is how it is supposed to work. Now here is the problem that I am having with my Sproc.

Since Bob does not have a record set up in the TimeStudy table, the sproc will not allow ANY entries for Bob, time studied or not. This means that even if Bob did assembly type work or whatever that IS NOT time studied, I still cannot make an entry for him. This is not correct. Since Dennis HAS an entry in the Time study table, his entries are accepted, even if they are NOT time studied.

During timesheet entry, what I am needing the sproc to do is:

1) Accept any entries that are not timestudied as long as they have been set up in the JobSteps table.

2) If an entry is for a time studied job, look in the TimeStudy table and see if the client has been set up for the particular job (i.e. Dennis for 199-9381-21). If yes, then pull their time study percent and accept the entry into the timesheet table. If no (i.e. Bob does not have any entries in the Time study table) then reject the attempt.

My thinking was: If the client worked on a time studied job and a record EXISTS for it in the time studied table, then give me the required info from the time studied table. If not give me an error.

If the entry was NOT for a time studied job, then don’t worry about the time studied table and let me make the entry.

I’m wondering if my table relationships are correct.
1) Clients are related to TimeStudy on Client_PK_ID.
2) JobSteps are related to TimeStudy on StepNumber_PK_ID.
3) Timesheets are related to JobSteps on StepNumber_PK_ID.
4) Timesheets are NOT directly related to the TimeStudy table.

I’m not sure how to handle the JobStep_PK_ID between the TimeSheet table and the TimeStudy table. Both are FK’s in relation to the JobStep table which has the JobSteps_PK_ID.

The Timestudy table has it’s own TimeStudy_PK_ID. Set up a relationship with the time sheet table and the timestudy table based on the TimeStudy_PK_ID? Doesn’t seem correct.

My selection code:

SELECT @Client_PK_ID =
			tbl_Clients_ClientBasicInfo.Client_PK_ID,
		@FundingCode =
			tbl_Clients_ClientFundingAndProgramSources.FundingCode,
		@AverageWage =
			tbl_Clients_ClientWageRates.AverageWage,
		@FringeWage =
			tbl_Clients_ClientWageRates.FringeWage,
		@GuaranteedWage =
			tbl_Clients_ClientWageRates.GuaranteedWage,
		@SpecialWage =
			tbl_Clients_ClientWageRates.SpecialWage,
		@WorkStatusCode =
			tbl_Clients_ClientWorkStatus.WorkStatusCode,
	--following is killing sproc even when TS% is not req'd
		@TimeStudyPercent =    
			tbl_Clients_ClientTimeStudy.TimeStudyPercent

FROM tbl_Clients_ClientBasicInfo
	JOIN
	tbl_Clients_ClientFundingAndProgramSources
	ON
	tbl_Clients_ClientBasicInfo.Client_PK_ID = 
		tbl_Clients_ClientFundingAndProgramSources.Client_PK_ID
		JOIN --<Joining BasicInfo & TmeStudy tbls here
		tbl_Clients_ClientTimeStudy
		ON
		tbl_Clients_ClientBasicInfo.Client_PK_ID = 
			tbl_Clients_ClientTimeStudy.Client_PK_ID
		JOIN
		tbl_Clients_ClientWageRates
		ON
		tbl_Clients_ClientBasicInfo.Client_PK_ID = 
			tbl_Clients_ClientWageRates.Client_PK_ID
		JOIN
		tbl_Clients_ClientWorkStatus
		ON
		tbl_Clients_ClientBasicInfo.Client_PK_ID = 
			tbl_Clients_ClientWorkStatus.Client_PK_ID
WHERE
tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

When trying to enter "Bob", I get the following:

Msg 515, Level 16, State 2, Procedure TimeSheet_Insert_TimesheetEntry, Line 339
Cannot insert the value NULL into column 'Client_PK_ID', table 'ClientPayroll.dbo.tbl_TimesheetEntry'; column does not allow nulls. INSERT fails.
The statement has been terminated.

If you’ve made it this far, thanks for your patience and help.

Bill