Answered by:
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.jobnumberreturns
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
Friday, March 13, 2020 6:01 PM
Answers
-
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 JobNumberThanks All
oned_gk, it looks like your idea would work
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, March 14, 2020 3:20 AM
All replies
-
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.jobnumberit 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
Friday, March 13, 2020 6:11 PM -
User77042963 posted
SELECT * FROM orderheader Where try_cast(stuff(documentnumber,CharIndex('C',documentnumber), len(documentnumber),'') as int) =jobnumber
Friday, March 13, 2020 8:10 PM -
User-1716253493 posted
Try this
case when CharIndex(t.documentnumber)>0 then Left(T.documentnumber, CharIndex(t.documentnumber,'C')-1) else '' end = orderheader.jobnumber
Saturday, March 14, 2020 12:25 AM -
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 JobNumberThanks All
oned_gk, it looks like your idea would work
- Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
Saturday, March 14, 2020 3:20 AM -
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)
=jobnumberMonday, March 16, 2020 1:36 PM