REPLACE in SELECT_SQL Where

This Q&A forum allows users to post and respond to "How Do I Do ....." questions. Please do not use to report (suspected) errors - you must use your regional help desk for this. The information contained in this forum has not been validated by LANSA and, as such, LANSA cannot guarantee the accuracy of the information.
Post Reply
jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

REPLACE in SELECT_SQL Where

Post by jyoung » Wed Apr 26, 2017 6:55 am

I am trying to use REPLACE in a SELECT_SQL where clause but keep getting the dreaded "Unrecoverable error occurred while executing the Web application routine." error.

I have a feeling it is something to do with the quotes. If I remove the replace it works fine. Any one use REPLACE with select_sql? Any ideas on how to make it work?

Code: Select all

#STD_TEXT := #CLOFN.UpperCase.Concat( "%" )
select_sql fields(#CLOFID #CLOD #CLOCN #CLOFN #CLOIN) from_files((AF1155)(XF1140)) where('(SUBSTRING(CAST(#CWDWP AS VARCHAR(8)), 1, 4) = :wk_YearString) AND (REPLACE(#CLOFN, '' '', '''') LIKE :STD_TEXT) AND (#CWOFID = #CLOFID) AND (#CWOFD = #CLOFD) AND (#CWOCN = #CLOCN) AND (#CWOD = #CLOD) AND (#CWELVL = 0) AND (#CLELVL = 0)')
The X_ERR.log does not help much.

Code: Select all

============================================================================== 
Thu Feb 23 15:29:15 2017 
Release 14.1.0 Build 4138 Windows 10 Enterprise Edition (10.0.0.0 Build 10586)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (11/17/2016 10:34:33 AM)
---EPC (17-11-2016 10:43:44)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (2/3/2017 10:04:13 AM)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (2/6/2017 9:40:28 AM)
---LANSA Installation/Upgrade 14.1.0 Build: 4138 (2/6/2017 10:44:09 AM)
---EPC (07-02-2017 12:19:20)
---EPC (07-02-2017 12:21:43)
---EPC (13-02-2017 14:54:55)
Message : X_COMP returned error code -1. Check X_ERR.LOG for more information.
Routine : X_Execute_WAM
Job Number: 007956 OS User : LANSA
DBII=LANSA DBUT=MSSQLS GUSR=QOTHPRDOWN PSTC=Y PROC=*WAM WAML=LANSA:XHTML WVAR=2 W3ST=1 WXSL=YY LANG=ENG PART=SYS USER=LANSA CMTH=T CDLL=LCOMGR32.DLL WSTY=IISB ITHP=N
ROOT=C:\Program Files (x86)\LANSA\X_WIN95
Is there anyway to view the generated SQL? Perhaps that can clue me into why it is failing.

soa
Posts: 302
Joined: Mon Dec 07, 2015 3:15 pm

Re: REPLACE in SELECT_SQL Where

Post by soa » Wed Apr 26, 2017 10:32 am

Why don't you build the SQL statement in full and execute it with the USING clause rather than relying on the SQL command generated by LANSA. This way you can take the string you've built in debug and run in a standard SQL tool such as STRSQL. This will either execute correctly or give you useful diagnostic messages.

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: REPLACE in SELECT_SQL Where

Post by jyoung » Thu Apr 27, 2017 12:03 am

I've been trying to stay away from the free format select_sql because we move code between different environments and each environment has a different library. When using the free format version, we pretty much hard code the library in the statement, we do have some ways to handle it better but its a big headache. Letting the library list of the job (which we do set dynamically) handle all of this helps out a lot.

It would not be a problem, if it would just tell me what the query is that its trying to generate.

Syntactically this seems correct, the query works when removing the "replace" and using replace fails on the simplest of queries. Using other functions work fine (SUBSTRING and CASE) it seems like only functions that have quote delimiters that are problematic.

billcross
Posts: 30
Joined: Thu Apr 07, 2016 3:01 am

Re: REPLACE in SELECT_SQL Where

Post by billcross » Thu Apr 27, 2017 3:22 am

Have you tried putting the where into a variable and using the *QUOTE field instead of ' ? The command to build could be

#WHERE := <Start of Where> + *QUOTE + ' ' + *QUOTE + ',' + *QUOTE + *QUOTE + <End of Where>

soa
Posts: 302
Joined: Mon Dec 07, 2015 3:15 pm

Re: REPLACE in SELECT_SQL Where

Post by soa » Thu Apr 27, 2017 9:04 am

I do not understand why you would hard code the library in the USING statement. SELECT_SQL will use the library list with either format.

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: REPLACE in SELECT_SQL Where

Post by jyoung » Thu Apr 27, 2017 11:36 pm

Perhaps I am doing something wrong, but every time I have to use the free format select_sql I've had to specify the library the File is in.

http://docs.lansa.com/14/en/lansa015/in ... free_p.htm mentions that
It is usually necessary to specify the collection that the table is in.
"Ususally" for me has been always.

I think this has to do with using two operating systems. When working locally (on Windows) I have to specify the library name, on the iSeries I don't. I think its an OS problem and not a RDBMS problem as I think the issue is that Windows does not have the concept of a library list (that I know of. I think the PATH environment variable is the closest thing).

This turns into a mess of code like this for every query that uses free form select_sql.

Code: Select all

if (*OSAPI = IBM)
* create query here
else
* create query with library here
endif
Like any good ORM, using its query generation ability gives you the flexibility to move between OS(s) and RDBMS(s) with few issues. I think this is actually a bad thing to do, you should IMHO, develop on what you are deploying to. This of course presents a problem because unlike my RPG coding co workers, who develop on the iSeries, we LANSA devs develop on Windows.

When the query generation tools break down and provides no feedback you are pretty much stuck with having to resort to custom sql and using, in this case, code specific to each platform.

soa
Posts: 302
Joined: Mon Dec 07, 2015 3:15 pm

Re: REPLACE in SELECT_SQL Where

Post by soa » Fri Apr 28, 2017 8:26 am

I've only used this on the iseries but it seems odd that SELECT_SQL in its old form would not require a library name but freeform would.

If you have to use conditioning in building the statement why not do something like

#stm = 'Select * from &1 where x=1'

if (*OSAPI = IBM)
#stm = #stm.replace('&1', 'MYFILE')
else
#stm = #stm.replace('&1', 'LIBRARY/MYFILE')
endif

soa
Posts: 302
Joined: Mon Dec 07, 2015 3:15 pm

Re: REPLACE in SELECT_SQL Where

Post by soa » Fri Apr 28, 2017 8:29 am

Another suggestion would be to replicate your problem code into an RDML function then check it in to an iSeries with keep source. Then the RPGSQL source listing will show you what is being generated and maybe even give you syntax error information. I assume there's generated c++ source somewhere but I've never looked for that.

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: REPLACE in SELECT_SQL Where

Post by jyoung » Sat Apr 29, 2017 2:19 am

That is pretty much what I am having to do know except the logic to determine what library is bit more complex. We have 3 different environments, 2 geographical regions and 2 "applications" each with different libraries. I've got it worked out, so that I can do something like

Code: Select all

#wk_Sql := ("select ... from &1.MyTable where ...").Subsitute(#LibraryListManager.GetDataLibrary(#wk_CountryCode))
Where country code denotes what "region" I am in. I also ended up making a system variable *ENVIRONMENT that tells me if I am local, in dev or production.

All that being done, it would still be easier IMHO to do

Code: Select all

select_sql fields(...) from_files((MyTable)) where(...)

Tim McEntee
Posts: 10
Joined: Thu May 26, 2016 8:46 am

Re: REPLACE in SELECT_SQL Where

Post by Tim McEntee » Tue May 02, 2017 9:59 am

Is it because you are using hashes for your fields in the where clause?
I would have used (REPLACE(CLOFN, '' '', '''') LIKE :STD_TEXT)

jyoung
Posts: 653
Joined: Thu Jan 21, 2016 6:43 am
Location: Oklahoma City, OK USA

Re: REPLACE in SELECT_SQL Where

Post by jyoung » Wed May 03, 2017 11:43 pm

Hi Tim,

Don't know but I did not think about it, but I'll give it a shot. I can use the hash in other SQL functions like CAST but perhaps REPLACE and its quotes require something different.

Will give it a go and see what happens.

Thanks,
Joe

Post Reply