Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Info from 2 unrelated tables into one with error msg. 1

Status
Not open for further replies.

willydude

Programmer
Oct 24, 2006
123
US
I am entering info into a timesheet table. The Client_PK_ID comes from the ClientBasicInfo table; the StepNumber_PK_ID and other info comes from the Jobs_JobSteps table. All the info gets inserted into the Timesheet table. Pretty basic idea.

I have the following Select statements.

Code:
SELECT Client_PK_ID
FROM tbl_Clients_ClientBasicInfo
WHERE
tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

---new select statement, same Sproc
SELECT StepNumber_PK_ID, PayFormulaCode AS PFC, 
	PrevailingWage AS PW, Standard AS STD
FROM
tbl_Jobs_JobSteps
JOIN
tbl_Jobs_JobNumber
ON
tbl_Jobs_JobSteps.JobNumber_PK_ID = 
		tbl_Jobs_JobNumber.JobNumber_PK_ID
	JOIN
	tbl_Customers_CustomerBasicInfo
	ON
	tbl_Jobs_JobNumber.Customer_PK_ID =
		tbl_Customers_CustomerBasicInfo.Customer_PK_ID
		JOIN
		tbl_Jobs_JobStepCodesAndPayFormulaCodes
		ON
		tbl_Jobs_JobStepCodesAndPayFormulaCodes.StepCode =
			tbl_Jobs_JobSteps.StepCode			
where
tbl_Customers_CustomerBasicInfo.CustomerNumber = @CustomerNumber
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobSteps.StepNumber = @StepNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber

Both pull the desired info correctly. My Insert is as follows:
Code:
if @Timesheet_PK_ID is null or
	@TimeSheet_PK_ID = 0
begin

Insert into tbl_TimeSheetEntry
(BatchNumber,
LocationNumber,
WorkDate,
Client_PK_ID,
StepNumber_PK_ID,
--ClientTimeStudy_PK_ID,
HoursWorked,
Units,
PrevailingWage,
Standard,
WagesPaid,
FundingCode,
PayFormulaCode)

values
(@BatchNumber,
@LocationNumber,
@WorkDate,
@Client_PK_ID,
@StepNumber_PK_ID,
--@ClientTimeStudy_PK_ID,
@HoursWorked,
@Units,
@PrevailingWage,
@Standard,
@WagesPaid,
@FundingCode,
@PayFormulaCode
)

--Assign New TimeSheet_PK_ID to record just added
Set @TimeSheet_PK_ID = Scope_Identity ()
end

In my results, I get 4 return lines:

Code:
Client_PK_ID
3		--this is a valid #

StepNumber_PK_ID	 PFC	PW		Std
104	                AW	NULL		NULL

--all of the above are valid #’s, but the following gives me NULLs

@Client_        @StepNumber_    @TimeSheet_     @Message
PK_ID                 PK_ID   		PK_ID
NULL		   NULL		NULL


ReturnValue
-6

And the message:
Code:
Msg 515, Level 16, State 2, Procedure TimeSheet_Insert_TimesheetEntry, Line 196
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 the Client_PK_ID, the StepNumber_PK_ID and other info is correct, then why am I getting NULLS, a -6 return value and a msg telling me about a NULL Client_PK_ID?

I thought maybe the UNION would work, but I don't think that it is appropriate for this task.

Thanks,

Bill
 
Where do you set the values for these variables?
(@BatchNumber,
@LocationNumber,
@WorkDate,
@Client_PK_ID,
@StepNumber_PK_ID,
--@ClientTimeStudy_PK_ID,
@HoursWorked,
@Units,
@PrevailingWage,
@Standard,
@WagesPaid,
@FundingCode,
@PayFormulaCode

I am not sure if you have implied this or not, but your select statements should assign the values, e.g.
Code:
SELECT @Client_PK_ID = Client_PK_ID
FROM tbl_Clients_ClientBasicInfo
WHERE
tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber
If you then run "SELECT @Client_PK_ID", it will show you the value it contains.



"I'm living so far beyond my income that we may almost be said to be living apart
 
Thanks HMC,

--following works as desired
Code:
SELECT @Client_PK_ID = Client_PK_ID
FROM tbl_Clients_ClientBasicInfo
WHERE
tbl_Clients_ClientBasicInfo.ClientNumber = @ClientNumber

--This works also. I have more columns to add here.
Code:
SELECT @StepNumber_PK_ID = StepNumber_PK_ID, 
		@PayFormulaCode = PayFormulaCode, 
		@PrevailingWage = PrevailingWage, 
		@Standard = Standard 
FROM
tbl_Jobs_JobSteps
JOIN
tbl_Jobs_JobNumber
ON
tbl_Jobs_JobSteps.JobNumber_PK_ID = 
		tbl_Jobs_JobNumber.JobNumber_PK_ID
	JOIN
	tbl_Customers_CustomerBasicInfo
	ON
	tbl_Jobs_JobNumber.Customer_PK_ID =
		tbl_Customers_CustomerBasicInfo.Customer_PK_ID
		JOIN
		tbl_Jobs_JobStepCodesAndPayFormulaCodes
		ON
		tbl_Jobs_JobStepCodesAndPayFormulaCodes.StepCode =
			tbl_Jobs_JobSteps.StepCode			
where
tbl_Customers_CustomerBasicInfo.CustomerNumber = @CustomerNumber
and
tbl_Jobs_JobNumber.JobNumber = @JobNumber
and
tbl_Jobs_JobSteps.StepNumber = @StepNumber
and
tbl_Jobs_JobNumber.LocationNumber = @LocationNumber

Question: If the Clients (workers) are in a different table than the Job Steps, should they be in the same bit of code where both are selected, or separate as above. I've read that for performance issues, have just one overall Select statement is best.

Thoughts?

Bill
 
Only Advise I can give on what I see in the posting is to test as many ways as possible. IN order to answer your question, I would need to see the relationships between the tables, size of tables, indexes within the tables etc.

If you can get the info in one query and its highly performant that will generally work better than two queries, but two queries may be better if they can get to the data quickly via good index selection.

Sorry to waffle on, but everything is treated on a case by case basis and only way to ensure its ok is to test extensively.

"I'm living so far beyond my income that we may almost be said to be living apart
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top