Help with SQL code for IPA flow

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.

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

Also, what DB are you running?

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:

Isn't the trim function in SQL RTRIM?

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

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.

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.

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

Also you can try this:

Case when trim(field) is Null then.....

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.