PrevPrev Go to previous topic
NextNext Go to next topic
Last Post 05/12/2017 3:12 PM by  Bob Canham
Help with SQL code for IPA flow
 11 Replies
Sort:
You are not authorized to post a reply.
Author Messages
Brooks Saunders
Lead System Analyst
Private
New Member
(7 points)
New Member
Posts:3


Send Message:

--
05/12/2017 8:58 AM
    Pulling in Data for use two fields.  Once I added the TRIM expression to the selection, I was receiving undefined when I reviewed my data in a text document.  If I leave the expressions out of the selection code, the fields are blank where I have null values (which is expected but I need to TRIM those fields).  Can anyone shed light to as why my COALESCE works for my POLINE.DESCRIPTION but does not work for APINVOICE.PO_NUMBER?  (Keep in mind ISNULL is not an expression IPA recognizes) ~Thanks

     

    SELECT  TO_CHAR ("APDISTRIB"."DISTRIB_DATE",'MM/DD/YYYY') as DISTRIB_DATE, TRIM("APDISTRIB"."INVOICE") as INVOICE, TO_CHAR("APINVOICE"."INVOICE_DTE",'MM/DD/YYYY') as INVOICE_DTE, TRIM("APINVOICE"."VENDOR") as VENDOR, TRIM(COALESCE("APINVOICE"."PO_NUMBER",'DIRECT INVOICE')) as PO_NUMBER, TRIM("APDISTRIB"."ACTIVITY") as ACTIVITY, "APDISTRIB"."DIS_ACCOUNT", TRIM("GHAPVENMAST"."VENDOR_VNAME")as VENDOR_VNAME, "APDISTRIB"."ORIG_TRAN_AMT", TRIM(COALESCE("POLINE"."DESCRIPTION",'DIRECT INVOICE')) as DESCRIPTION

     

    ;

    David Williams
    Private
    Private
    Veteran Member
    (3031 points)
    Veteran Member
    Posts:1021


    Send Message:

    --
    05/12/2017 9:05 AM
    You can't trim null fields, so it makes sense why you'd get an error. I believe the syntax to check for null values in JavaScript is
    if (PO_NUMBER===null) variable =""
    So on fields where a null might be possible, you need to add error trapping like this and reassign to a different variable.
    David Williams
    Woozy
    Private
    Private
    Veteran Member
    (3472 points)
    Veteran Member
    Posts:702


    Send Message:

    --
    05/12/2017 9:29 AM
    The coalesce should eliminate the null values, so I'm not sure why this is returning "null"/"undefined". What happens when you run that query in a "regular" SQL query tool? Maybe there is something goofy with your joins? How about posting the entire query rather than just the field section?

    Kelly
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Woozy
    Private
    Private
    Veteran Member
    (3472 points)
    Veteran Member
    Posts:702


    Send Message:

    --
    05/12/2017 9:31 AM
    Also, what DB are you running?
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Brooks Saunders
    Lead System Analyst
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    05/12/2017 10:22 AM

    Haven't ran this through SQL developer yet.  Our DB is Oracle. 

    Here is the full code:

    SELECT "APDISTRIB"."DISTRIB_DATE", "APDISTRIB"."INVOICE", "APINVOICE"."INVOICE_DTE", "APINVOICE"."VENDOR", TRIM(COALESCE("APINVOICE"."PO_NUMBER",'DIRECT INVOICE')) as PO_NUMBER, "APDISTRIB"."ACTIVITY", "APDISTRIB"."DIS_ACCOUNT", "GHAPVENMAST"."VENDOR_VNAME", "APDISTRIB"."ORIG_TRAN_AMT", TRIM(COALESCE("POLINE"."DESCRIPTION", 'DIRECT INVOICE')) as DESCRIPTION

     FROM   (("LAWPROD"."GHAPVENMAST" "GHAPVENMAST" INNER JOIN "LAWPROD"."APDISTRIB" "APDISTRIB" ON "GHAPVENMAST"."VENDOR"="APDISTRIB"."VENDOR") INNER JOIN "LAWPROD"."APINVOICE" "APINVOICE" ON (("APDISTRIB"."COMPANY"="APINVOICE"."COMPANY") AND ("APDISTRIB"."INVOICE"="APINVOICE"."INVOICE")) AND ("APDISTRIB"."REC_STATUS"="APINVOICE"."REC_STATUS")) FULL OUTER JOIN "LAWPROD"."POLINE" "POLINE" ON (("APDISTRIB"."COMPANY"="POLINE"."COMPANY") AND ("APDISTRIB"."PO_NUMBER"="POLINE"."PO_NUMBER")) AND ("APDISTRIB"."PO_LINE_NBR"="POLINE"."LINE_NBR")

     WHERE  ("APDISTRIB"."ACTIVITY" LIKE '8%' OR "APDISTRIB"."ACTIVITY" LIKE 'R%') AND ("APDISTRIB"."DISTRIB_DATE">={ts '2017-03-29 00:00:00'} AND "APDISTRIB"."DISTRIB_DATE"<{ts '2017-03-29 00:00:01'})

    TEXT DOCUMENT RESULT:

    FireGeek21
    Private
    Private
    Veteran Member
    (131 points)
    Veteran Member
    Posts:55


    Send Message:

    --
    05/12/2017 11:11 AM
    Isn't the trim function in SQL RTRIM?
    Brooks Saunders
    Lead System Analyst
    Private
    New Member
    (7 points)
    New Member
    Posts:3


    Send Message:

    --
    05/12/2017 2:36 PM

    "Posted By David Williams on 05/12/2017 9:05 AM
    You can't trim null fields, so it makes sense why you'd get an error. I believe the syntax to check for null values in JavaScript is
    if (PO_NUMBER===null) variable =""
    So on fields where a null might be possible, you need to add error trapping like this and reassign to a different variable."

    I am realizing that because the fields I am dealing with that contain blank data, once I introduce an expression like TRIM they return a null value instead of being blank.  What puzzles me is how to properly TRIM and get blank or null values as another value or to remain blank.

    FireGeek21
    Private
    Private
    Veteran Member
    (131 points)
    Veteran Member
    Posts:55


    Send Message:

    --
    05/12/2017 2:48 PM
    I typically use trim and other functions to format my data in assign nodes after the SQL. Each is then properly saved off in a variable that can be used later in the flow.
    Woozy
    Private
    Private
    Veteran Member
    (3472 points)
    Veteran Member
    Posts:702


    Send Message:

    --
    05/12/2017 2:54 PM
    Hmmm - I'm not an Oracle guy, so maybe it's how Oracle handles trim/coalesce. I'd strongly suggest troubleshooting your query in a SQL tool first before putting it in IPA. Once it is returning the expected data, then move it to IPA. IPA will introduce some strangeness too - but at least you know the base query is OK.

    As to the query itself, maybe you need to go with a CASE statement instead of trying to do everything in one step? This may help you get the result you are looking for.

    Sorry I can't provide much more help.
    Kelly Meade
    J. R. Simplot Company
    Boise, ID
    Karen Sheridan
    Private
    Private
    Veteran Member
    (325 points)
    Veteran Member
    Posts:123


    Send Message:

    --
    05/12/2017 3:07 PM
    We run into this all the time and I use a case statement.
    Case when "APINVOICE"."PO_NUMBER" is Null then ' ' (space) else trim("APINVOICE"."PO_NUMBER") end as PO_NUMBER
    or
    Case when "APINVOICE"."PO_NUMBER" = ' ' then ' ' (space) else trim("APINVOICE"."PO_NUMBER") end as PO_NUMBER
    This is Oracle.

    There are lots of fields in Lawson that are stored as a single space verses a null.

    Hope this helps,
    Karen
    Karen Sheridan
    Private
    Private
    Veteran Member
    (325 points)
    Veteran Member
    Posts:123


    Send Message:

    --
    05/12/2017 3:08 PM
    Also you can try this:

    Case when trim(field) is Null then.....
    Bob Canham
    Private
    Private
    Veteran Member
    (535 points)
    Veteran Member
    Posts:193


    Send Message:

    --
    05/12/2017 3:12 PM
    Since you're on oracle, you can use nvl to check null. trim(nvl(APINVOICE.PO_NUMBER,'')) so it will replace null with empty string and then trim it.
    You are not authorized to post a reply.