SQL QUERY NODE ACTING UP!

FYI: It looks like when I posted this my variables got messed up but I think you can make out what I am trying to do. For the record, there are values in these variables when the node runs.

tailVEND is actually DetailVENDOR
eckQ_INVOI is actually CheckQ_INVOICE (CheckQ being from a DME query returning ACH related information)
eckQ_COMPA is actually CheckQ_COMPANY (CheckQ being from a DME query returning ACH related information)

Hi Ward,

I'm a little confused.  The input values in your log don't match the input values of the screenshot of your SQL query.  In the log, you show VENDOR=71 and INVOICE='A TEST OF NO COMMENTS', but in the screenshot you show VENDOR=972 and INVOICE='TEST OF COMMENTS'.

Can you validate that the query built by the flow (exactly as shown in the log) returns data if you run it in your SQL tool?  Is there a chance that there aren't any comments for the query in the log?

Not that it matters, but I will update a new screenshot. The same issue remains, no data is returning back.

Try taking the double quotes out from around the table/field names.

John, I just tried that. Same result.

You might be running into an issue with the way Lawson stores the vendor number. Your SQL is treating it like a numeric, but it is really alpha " 71". SQL Mgmt Studio is "smart" enough to translate it, but I'm guessing that PF / JDBC is not. Try hard coding it, so that APINVOICE.VENDOR = ' 71' and see if you get back a record.

I tried that and here is the log. It does not specify when I put single quotes around numeric fields that there is data returned.


Executing Branch Activity Branch4240.....
Pushing CommentQ Activity in Execution Stack
Executing SQL Query Activity CommentQ.....SELECT "L_HAPI"."NAME" as COMMENTNAME
FROM "APINVOICE"
LEFT OUTER JOIN "L_HAPI" ON "APINVOICE"."L_INDEX"="L_HAPI"."L_INDEX"
WHERE
"APINVOICE"."VENDOR"='<!--tailVEND-->' AND
"APINVOICE"."INVOICE"='<!--eckQ_INVOI-->' AND
"APINVOICE"."COMPANY"='<!--eckQ_COMPA-->'
ORDER BY
"APINVOICE"."INVOICE_DTE", "APINVOICE"."VENDOR"

Executing Sql ..... SELECT "L_HAPI"."NAME" as COMMENTNAME
FROM "APINVOICE"
LEFT OUTER JOIN "L_HAPI" ON "APINVOICE"."L_INDEX"="L_HAPI"."L_INDEX"
WHERE
"APINVOICE"."VENDOR"='972' AND
"APINVOICE"."INVOICE"='NOCOMMENTS' AND
"APINVOICE"."COMPANY"='9400'
ORDER BY
"APINVOICE"."INVOICE_DTE", "APINVOICE"."VENDOR"
CommentQ_RETURN_MSG = Success
CommentQ_RETURN_CODE = 0
CommentQ_errorCode = 0
CommentQ_informationCode = 0
CommentQ_returnMessage = SQL query complete.
CommentQ_outputData =
CommentQ_RECORD_COUNT = 0
Executing FgaVarString Activity MsgBuilder8160.....

FYI: In an assign node i do the following:

detailVENDOR=trim(CheckQ_VENDOR)

This is so I can trim the preceding and trailing spaces that were in CheckQ_VENDOR when the DME query returned the data back. I thought that something like

      79

was causing the sql to crash. (Notice the spaces before the 79, that is what the DME rreturns with the numeric)

Since the log shows:
Executing Sql ..... SELECT "L_HAPI"."NAME" as COMMENTNAME
FROM "APINVOICE"
...
"APINVOICE"."VENDOR"='972' AND

It looks like your vendor number is being left-justified.

Like I said before, try hard-coding VENDOR=' 972' into your SQL node to verify.

John, I did and get no records. When it is truncated, it finds the record though. It seems as though spaces in front are a no-no and trimming it works. Any other ideas?

Here are my findings:

-VENDOR IS CHECKED AS STRING TRUNCATED

Executing Sql ..... SELECT L_HAPI.NAME
FROM APINVOICE
LEFT OUTER JOIN L_HAPI ON APINVOICE.L_INDEX=L_HAPI.L_INDEX
WHERE
APINVOICE.VENDOR='972' AND
APINVOICE.INVOICE='NOCOMMENTS' AND
APINVOICE.COMPANY=9400
ORDER BY
APINVOICE.INVOICE_DTE, APINVOICE.VENDOR
CommentQ_RETURN_MSG = Success
CommentQ_RETURN_CODE = 0
CommentQ_errorCode = 0
CommentQ_informationCode = 0
CommentQ_returnMessage = SQL query complete.
CommentQ_outputData =
CommentQ_RECORD_COUNT = 0
Executing FgaVarString Activity MsgBuilder8160.....



-VENDOR IS CHECKED AS NUMBER TRUNCATED BUT IT SHOWS undefined INSTEAD OF ACTUAL VALUE IN DATABASE

Executing Sql ..... SELECT L_HAPI.NAME
FROM APINVOICE
LEFT OUTER JOIN L_HAPI ON APINVOICE.L_INDEX=L_HAPI.L_INDEX
WHERE
APINVOICE.VENDOR=972 AND
APINVOICE.INVOICE='NOCOMMENTS' AND
APINVOICE.COMPANY=9400
ORDER BY
APINVOICE.INVOICE_DTE, APINVOICE.VENDOR
CommentQ_RETURN_MSG = Success
CommentQ_RETURN_CODE = 0
CommentQ_errorCode = 0
CommentQ_informationCode = 0
CommentQ_returnMessage = SQL query complete.
CommentQ_outputData =
CommentQ_RECORD_COUNT = 1
Executing Iteration Start Activity (loop 1 of 1 ).....
CommentQ_1 = null
CommentQ_1_1 = null
CommentQ_NAME = null
CommentQ_NAME_1 = null
Executing FgaVarString Activity MsgBuilder1000....



-VENDOR IS CHECKED AS STRING NOT TRUNCATED BUT IT SHOWS undefined INSTEAD OF ACTUAL VALUE IN DATABASE

Executing Sql ..... SELECT L_HAPI.NAME
FROM APINVOICE
LEFT OUTER JOIN L_HAPI ON APINVOICE.L_INDEX=L_HAPI.L_INDEX
WHERE
APINVOICE.VENDOR=' 972' AND
APINVOICE.INVOICE='NOCOMMENTS' AND
APINVOICE.COMPANY=9400
ORDER BY
APINVOICE.INVOICE_DTE, APINVOICE.VENDOR
CommentQ_RETURN_MSG = Success
CommentQ_RETURN_CODE = 0
CommentQ_errorCode = 0
CommentQ_informationCode = 0
CommentQ_returnMessage = SQL query complete.
CommentQ_outputData =
CommentQ_RECORD_COUNT = 1
Executing Iteration Start Activity (loop 1 of 1 ).....
CommentQ_1 = null
CommentQ_1_1 = null
CommentQ_NAME = null
CommentQ_NAME_1 = null
Executing FgaVarString Activity MsgBuilder1000.....



-VENDOR IS CHECKED AS NUMBER NOT TRUNCATED BUT IT SHOWS undefined INSTEAD OF ACTUAL VALUE IN DATABASE

Executing Sql ..... SELECT L_HAPI.NAME
FROM APINVOICE
LEFT OUTER JOIN L_HAPI ON APINVOICE.L_INDEX=L_HAPI.L_INDEX
WHERE
APINVOICE.VENDOR= 972 AND
APINVOICE.INVOICE='NOCOMMENTS' AND
APINVOICE.COMPANY=9400
ORDER BY
APINVOICE.INVOICE_DTE, APINVOICE.VENDOR
CommentQ_RETURN_MSG = Success
CommentQ_RETURN_CODE = 0
CommentQ_errorCode = 0
CommentQ_informationCode = 0
CommentQ_returnMessage = SQL query complete.
CommentQ_outputData =
CommentQ_RECORD_COUNT = 1
Executing Iteration Start Activity (loop 1 of 1 ).....
CommentQ_1 = null
CommentQ_1_1 = null
CommentQ_NAME = null
CommentQ_NAME_1 = null
Executing FgaVarString Activity MsgBuilder1000.....

For giggles, I added a field form APINVOICE in the select list and look what happens:

Executing Sql ..... SELECT L_HAPI.NAME AS VALNAME,
APINVOICE.INVOICE
FROM APINVOICE
LEFT OUTER JOIN L_HAPI ON APINVOICE.L_INDEX=L_HAPI.L_INDEX
WHERE
APINVOICE.VENDOR=972 AND
APINVOICE.INVOICE='NOCOMMENTS' AND
APINVOICE.COMPANY=9400
ORDER BY
APINVOICE.INVOICE_DTE, APINVOICE.VENDOR
CommentQ_RETURN_MSG = Success
CommentQ_RETURN_CODE = 0
CommentQ_errorCode = 0
CommentQ_informationCode = 0
CommentQ_returnMessage = SQL query complete.
CommentQ_outputData =
CommentQ_RECORD_COUNT = 1
Executing Iteration Start Activity (loop 1 of 1 ).....
CommentQ_1 = null
CommentQ_1_1 = null
CommentQ_VALNAME = null
CommentQ_VALNAME_1 = null
CommentQ_2 = NOCOMMENTS
CommentQ_1_2 = NOCOMMENTS
CommentQ_INVOICE = NOCOMMENTS
CommentQ_INVOICE_1 = NOCOMMENTS
Executing FgaVarString Activity MsgBuilder1000.....

Executing FgaVarString Activity MsgBuilder8160.....


It's almost like ProcessFlow cannot query the L_HAPI table. It is in Lawson and is valid. Hmmmmmmm....

I figured this out...

First of all, the info I found on myLawson about the comments being in L_HAPI and L_DAPI - not correct.

I checked those tables and in the L_HAPI, the NAME field does store the title of the comment. But in L_DAPI, there was no comment in there.

So I decided at that point to instead utilize the jgetattachrec.exe and WebRun node to get the comments, using the following posts:
- https://www.lawsonguru.co...s-table-for-comments
- https://www.lawsonguru.co...ft/7492/Default.aspx

Once I tweaked by process flow by adding the WebRun node and a Assign node with free-text javascript, bam, comments coming in as expected!

So this one is done. Thanks to John and everyone for your help.

2 days dealing with this to finally come around and find the above links. Wow!!!! LOL