Sub Query Issue

Question

User1215529056 posted

I am trying to get a PO number from the orderheader table in a sub query that uses invoice document number. The doment number 90% of the time equals the job number.

10% of the time a document number could have additional characters added to it for 2nd invoice, credit invoice or reinvoice.

Samples of Job Numbers   22921, 23591, 25989, 26779

Samples of Document Numbers  22921-1, 23591R, 25989C, 26779C

I was trying to use left and charindex in the where clause to deal with this but that did not work.

I tried this

SELECT Orderheader.PONumber
FROM orderheader
WHERE T.documentnumber= orderheader.jobnumber
or
Left(T.documentnumber, CharIndex(t.documentnumber,'C')-1) = orderheader.jobnumber
)

Results for document number 27429C returns null  for that row and field

SELECT Orderheader.PONumber
FROM orderheader
WHERE T.documentnumber= orderheader.jobnumber
or
Left(T.documentnumber, CharIndex(t.documentnumber,'C')-1) = orderheader.jobnumber

returns

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

Any suggestions would be greatly appreciated.

Thanks

Andy

Answer 1

User1215529056 posted

More info.

I see I have the CharIndex() backwards

Now I try this

SELECT Orderheader.PONumber
FROM orderheader
WHERE T.documentnumber= orderheader.jobnumber
or
Left(T.documentnumber, CharIndex('C',t.documentnumber)-1) = orderheader.jobnumber

it returns this:

Msg 537, Level 16, State 2, Line 1
Invalid length parameter passed to the LEFT or SUBSTRING function.

I need to test for the documentnumber character length to make sure CharIndex('C',t.documentnumber)-1 has enough characters in the field. If documentnumber is empty

it can't subtract 1 from 0.

that is where I am now stuck

Answer 2

User77042963 posted

SELECT * FROM orderheader
	Where 
	try_cast(stuff(documentnumber,CharIndex('C',documentnumber), len(documentnumber),'') as int)
	=jobnumber

Answer 3

User-1716253493 posted

Try this

case when CharIndex(t.documentnumber)>0 then Left(T.documentnumber, CharIndex(t.documentnumber,'C')-1) else '' end = orderheader.jobnumber

Answer 4

User1215529056 posted

The object was to get the PO number from the order header table so I would not have to link the order header table to this stored procedure in a Crystal Report

I gave up on the sub query and modified the document number in this manner so I could link the order header table in the Crystal Report.

,CASE
WHEN CharIndex('C', T.documentnumber) > 0
THEN Left(T.documentnumber, CharIndex('C', t.documentnumber) - 1)
WHEN CharIndex('R', T.documentnumber) > 0
THEN Left(T.documentnumber, CharIndex('R', t.documentnumber) - 1)
WHEN CharIndex('-', T.documentnumber) > 0
THEN Left(T.documentnumber, CharIndex('-', t.documentnumber) - 1)
ELSE T.DocumentNumber
END AS JobNumber

Thanks All

oned_gk, it looks like your idea would work

Answer 5

User77042963 posted

You can use replace function to remove the three characters you don't want:

SELECT * FROM orderheader
Where
try_cast(replace(replace(replace(documentnumber,'C',''),'-',''),'R','') as int)
=jobnumber