UBE runs fine locally, fails on AS400 with error: SQL0104 - Token &1 was not valid Valid tokens: &2. OS400QL017 - PrepareAnd

[Prathiba]

UBE runs fine locally, fails on AS400 with error: SQL0104 - Token &1 was not valid Valid tokens: &2. OS400QL017 - PrepareAnd

Hi,

We are on JDE E1 8.11 SP1, Tools 8.98.3.4 on AS400 V5R4.

I have a need to write a report selecting only split lines (line numbers like 1.001, 1.002, 1.1, 1.2 etc) from Purchase Order file F4311. I built this custom report by using internal data selection that results in SQL like this:

Select *

from crpdta/F4311

where (pdnxtr <'999' and pdlnid >1 and pdlnid < 2)

OR (pdnxtr <'999' and pdlnid >2 and pdlnid < 3)

OR (pdnxtr <'999' and pdlnid >3 and pdlnid < 4)

.....

OR (pdnxtr <'999' and pdlnid >997 and pdlnid <998)

I am stopping at line 998 since I dont think we will ever have to go there. The report runs successfully in about a minute locally on a windows fat client and produces correct results. However, when I kick it off on server it errors out. The jde.log shows:

1004/9 MAIN_THREAD Mon Mar 26 13:10:25.269832 dbdrv_log.c196

OS400QL017 - PrepareAndOrDescriberepare failed. QSQLMSG *LIBL - SQL0104 - Token &1 was not valid. Valid tokens: &2




1004/9 MAIN_THREAD Mon Mar 26 13:10:25.270168 dbdrv_log.c196




OS400AG005 - DoPrepare:Statement is not prepared.

1004/9 MAIN_THREAD Mon Mar 26 13:10:25.270280 jdb_drvm.c1038




JDB9900168 - Failed to initialize db request

When I try to turn on logging and debug locally, the ER debugger just hangs and kicks me out of JDE.

Have any of you faced this problem? Also, any other suggestions to "select" only split lines?

I dont want to select all lines and do "IF/ELSE" logic to pick split lines since that adds unnecessary processing. This is more than a report, I will have additional logic for split lines that will involve file I/O etc.

Any help is greatly appreciated.

Thanks,

Prathibha

 

[mdalton]

Re: UBE runs fine locally, fails on AS400 with error: SQL0104 - Token &1 was not valid Valid tokens: &2. OS400QL017 - PrepareAnd

Here is IBM's feedback on the error:

http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=%2Frzaii%2Frzaiiodbc44.htm

I'd try to whack the SQL packages and re run on the server, probably won't help but worth a shot. It looks like there is a problem with your sql syntax. Generate the sql locally and then run the SQL statement on the 400 and isolate the specific error. Get the SQL working and then build your UBE to match the SQL. If all else fails, parse the lines looking for .01 etc, and limit the search criteria by order type and status codes.

 

[Prathiba]

Re: UBE runs fine locally, fails on AS400 with error: SQL0104 - Token &1 was not valid Valid tokens: &2. OS400QL017 - PrepareAnd

Thanks for the feedback.

However, there is no problem with the SQL since even on JDE fat client (windows), data resides on our AS400 and it is running the program successfully generating the same SQL.

I did delete the SQL packages though its a new report. Still ending in error.

A very small percentage of the PO lines are split lines (lines with .001 etc). Hence, I dont think its efficient to run wide open and parse for split lines.

Order Type and Status codes are not limiting the selection substantially.

Appreciate any other input on this.

Thanks,
Prathibha

 

[mdalton]

Re: UBE runs fine locally, fails on AS400 with error: SQL0104 - Token &1 was not valid Valid tokens: &2. OS400QL017 - PrepareAnd

The SQL syntax is translated through the ODBC driver. Get the SQL that is executed locally and run it on the 400. You might need to tweak it to be UDB compliant but it should not, in the substantive form, be much different than what you run on your fat client. Please let us know the results.

 

[Prathiba]

Re: UBE runs fine locally, fails on AS400 with error: SQL0104 - Token &1 was not valid Valid tokens: &2. OS400QL017 - PrepareAnd

The SQL that is executed locally can be run on AS400 with no tweaks. It runs successfully. However, I only extracted the statement until loop 5 i.e. till where lines > 5 and lines < 6. This is because, I was not able to run the job successfully with LOGGING TURNED ON. WITHOUT LOGGING ON, IT WORKS FINE LOCALLY.

Thanks,
Prathibha

 

[cchavero]

Finally what is the solution? please.

 

[Chan Rana]

Instead of writing a long where clause for each line # why dont you extract the decimal part and check for > zero. I think this will resolve your issue.

Chan

 

[manojrana]

Chan has a point. Tweak in SQL and see what is the out comes.

 

[David Robertson]

2.5 year old thread, resuscitation is unlikely...

 

[cchavero]

[FONT=open_sansregular]I'm migrating my server from Windows Server 2008 to Windows Server 2012 R2, and when I execute some queries show me this error SQL0104, but in Windows Server 2008 execute correctly. My conecction is well, but when I use function like COALESCE show me this error.[/FONT]