Database link for creditor purchase JobNo?

[neil.interactit]

Hi guys,

I'm stumped - and prepared to be embarrassed when you tell me how obvious this is! - but I can't trace the database linking.

For a given CR_BatchPayLines.CR_BatchPayLinesID, how do I traverse the database to the field(s) displayed in the JobNo column?

Cheers,
Neil.

[Mike.Sheen]

CR_BatchPayLines.CR_TransID -> CR_Trans.TransID

then CR_Trans.SourceID links to different tables, depending on the CR_Trans.Source:

For example, when CR_Trans.Source == "Creditor Sourced Purchase"
then CR_Trans.SourceID -> CR_BatchTranLines.ReceiptLineID, CR_BatchTranLines.JB_JobStages_RecID -> JB_JobStages.RecID, JB_JobStages.JB_Stages_RecID -> JB_Stages.RecID and so on.

Make sense?

[neil.interactit]

Almost!

I can successfully traverse to CR_BatchTranLines.JB_JobStages_RecID, but from there I can't find the (many-to-one) path to JB_Main?

Cheers,
Neil

[Scott.Pearce]

JB_JobCostCentres is the missing stepping stone.

CR_BatchTranLines.JB_JobStages_RecID -> JB_JobStages.RecID, JB_JobStages.JB_JobCostCentres_RecID ->JB_JobCostCentres.RecID, JB_JobCostCentres.JobID -> JB_Main.JobID

[neil.interactit]

Cool! Many thanks.

FWIW, here's my final SQL, traversing from CR_BatchPayLines to JB_Main ...

Code: Select all

SELECT *
,ROW_NUMBER() OVER (PARTITION BY bp.LineNumber ORDER BY bp.LineNumber) AS RowNumber -- remove duplicate LineNumbers
FROM CR_BatchPayLines bp
LEFT JOIN CR_Trans tr ON bp.CR_TransID = tr.TransID
LEFT JOIN CR_BatchTranLines bt ON tr.SourceID = bt.ReceiptID
LEFT JOIN JB_JobStages js ON bt.JB_JobStages_RecID =js.RecID
LEFT JOIN JB_JobCostCentres jcc ON js.JB_JobCostCentres_RecID = jcc.RecID
LEFT JOIN JB_Main jb ON jcc.JobID = jb.JobID
WHERE tr.InvRemitNo = bt.RemitNo
AND tr.CreditorID = bt.AccountID
AND bp.CR_BatchPayID = (SELECT CR_BatchPayID FROM CR_BatchPayment WHERE BatchNo = '006970')
AND tr.Source = 'Creditor Sourced Purchase'


Thanks again,
Neil.