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

[willydude]

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.

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:

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:

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:

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

 

[hmckillop]

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.

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

 

[willydude]

Thanks HMC,

--following works as desired

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.

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

 

[johnwm]

http://r937.com/relational.html

___________________________________________________________
If you want the best response to a question, please check out FAQ222-2244 first.
'If we're supposed to work in Hex, why have we only got A fingers?'
Drive a Steam Roller
Steam Engine Prints

 

[hmckillop]

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