locked
Sub Query Issue RRS feed

  • 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

    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 JobNumber

    Thanks 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.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

    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 JobNumber

    Thanks 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)
    =jobnumber

    Monday, March 16, 2020 1:36 PM