David Hockenbroch · Oct 28, 2022

Using Macros in SQL - Cache vs. IRIS

In Cache 2018, we were using a macro in a query that looked like this:

select $$GetExtraSQL^GetExtra('B',bddtl.odnumb,bddtl.odsnum,bddtl.oddsc1) as "Description", * from sqluser.bddtl

We could save that query as a view, and there was no problem with it.

In IRIS, if we put that query into SQL in the management portal, it still works, but if we save that query as a view, when we try to run a query on that view, we get a big error message:

ERROR #5540: SQLCODE: 400 Message: Process 604526 failed to compile Cached Query Class %sqlcq.R001.cls2136 with these errors: ERROR #5475: Error compiling routine: %sqlcq.R001.cls2136.1. Errors: ERROR: #1010: Missing right parenthesis : 'i%"e,3,5,7,8,GetExtraGetExtra"=$$GetExtra^GetExtra($s((i%"c""B""1"="")||(i%"c""||(i%odnumb="")||(i%"c""||(i%odsnum="")||(i%"c""||(i%oddsc1=""):"",1:$s($tr(i%"c""B""1"_i%"c""||""2"_i%odnumb_i%"c""||""4"_i%odsnum_i%"c""||""6"_i%oddsc1,$c(0),"")'="":$tr(i%"c""B""1"_i%"c""||""2"_i%odnumb_i%"c""||""4"_i%odsnum_i%"c""||""6"_i%oddsc1,$c(0),""),1:$c(0))))' : Offset:104 [%0AmBuncommitted+1^%sqlcq.R001.cls2136.1] > ERROR #5030: An error occurred while compiling class '%sqlcq.R001.cls2136' SQLTEXT: SELECT Description,ID,odstat,odtype,odprod,odnumb,odsnum,odqord,odqshp,odunet,oduprc,odgrss,oddisc,odnet,odmcst,odbcst,odlcst,odscst,odqbrk,odqbds1,od...

We ended up having to rewrite the macro that was being called to take several arguments and build the string on the macro end. It seems like it was caused by using that string SQL function for an argument to that macro. That's not a big deal, but what's different about the way views work in IRIS compared to queries?

(PS: I'm aware my $ZV and product version don't line up; the $ZV is the Cache version, and the product version is the IRIS version. I wasn't sure how to list that.)

Product version: IRIS 2021.1
$ZV: Cache for UNIX (Red Hat Enterprise Linux for x86-64) 2018.1.2 (Build 309U) Mon Mar 4 2019 15:07:46 EST
0 127
Discussion (1)1
Log in or sign up to continue

I would open a WRC on that since it looks like a pure bug in the SQL compiler