I'm migrating a fex from mainframe to WebFOCUS 7.1.3 and I'm discovering different behavior. As part of the migration, I am converting the queries from FOCUS syntax to SQL Passthru, so the LET statements change a bit but the concept of their use is the same. The problem is when I
set an &field = a LET value to be used in a WHERE statement. The &field does not get set properly. Observe the following code:

-SET &ECHO=ALL;
-SET &GROUP01='090100';
-SET &GROUP02='090110';
-SET &GROUP03='901000';
-SET &GROUP04='901100';
-SET &GROUP05='      ';
-SET &GROUP06='      ';
-SET &GROUP07='      ';
-SET &GROUP08='      ';
-SET &GROUP09='      ';
-SET &GROUP10='      ';
-DEFAULT &GROUPIND='M';
LET AGRP = AND EMP_GRP_NBR <> '      '

LET SGRP = AND EMP_GRP_NBR = '&GROUP01'

LET MGRP = AND EMP_GRP_NBR IN ('&GROUP01','&GROUP02','&GROUP03',

LET MGRPA = '&GROUP04','&GROUP05','&GROUP06','&GROUP07',

LET MGRPB = '&GROUP08','&GROUP09','&GROUP10')
LET MGRPC = ' '

-SET &GRPSELECT = IF &GROUPIND EQ 'R' THEN RGRP
-            ELSE IF &GROUPIND EQ 'S' THEN SGRP
-            ELSE IF &GROUPIND EQ 'A' THEN AGRP
-                                     ELSE MGRP;
-SET &GSEL1 = IF &GROUPIND EQ 'M' THEN MGRPA
-                                 ELSE MGRPC;
-SET &GSEL2 = IF &GROUPIND EQ 'M' THEN MGRPB
-                                 ELSE MGRPC;
? LET
-RUN
ENGINE DB2 SET DEFAULT_CONNECTION DB2W
SQL DB2 PREPARE ATTACH19 FOR
SELECT
  US_AMIS_PROV_SPEC
, PROV_SPECIAL
FROM DSNW.DWS_PROVIDER_SPEC
&GRPSELECT.EVAL
&GSEL1.EVAL
&GSEL2.EVAL
WITH UR;
END
-RUN  

This is the result

--------------------------------------------------------------------------------

No HTML Output!

--------------------------------------------------------------------------------


 -SET &GROUP01='090100';
 -SET &GROUP02='090110';
 -SET &GROUP03='901000';
 -SET &GROUP04='901100';
 -SET &GROUP05='      ';
 -SET &GROUP06='      ';
 -SET &GROUP07='      ';
 -SET &GROUP08='      ';
 -SET &GROUP09='      ';
 -SET &GROUP10='      ';
 -DEFAULT &GROUPIND='M';
 LET AGRP = AND EMP_GRP_NBR <> '      '
 LET SGRP = AND EMP_GRP_NBR = '090100'
 LET MGRP = AND EMP_GRP_NBR IN ('090100','090110','901000',
 LET MGRPA = '901100','      ','      ','      ',
 LET MGRPB = '      ','      ','      ')
 LET MGRPC = ' '
 -SET &GRPSELECT = IF M EQ 'R' THEN RGRP
 -            ELSE IF M EQ 'S' THEN SGRP
 -            ELSE IF M EQ 'A' THEN AGRP
 -                                     ELSE MGRP;
 -SET &GSEL1 = IF M EQ 'M' THEN MGRPA
 -                                 ELSE MGRPC;
 -SET &GSEL2 = IF M EQ 'M' THEN MGRPB
 -                                 ELSE MGRPC;
 ? LET
 -RUN
 AGRP          AND EMP_GRP_NBR <0> '      '
 MGRP          AND EMP_GRP_NBR IN ('090100','090110','901000',
 MGRPA         '901100','      ','      ','      ',
 MGRPB         '      ','      ','      ')
 MGRPC         ' '
 SGRP          AND EMP_GRP_NBR = '090100'
 ENGINE DB2 SET DEFAULT_CONNECTION DB2W
 SQL DB2 PREPARE ATTACH19 FOR
 SELECT
 US_AMIS_PROV_SPEC
 , PROV_SPECIAL
 FROM DSNW.DWS_PROVIDER_SPEC
 MGRP
 MGRPA
 MGRPB
 WITH UR;
 END
 -RUN
 (FOC1400) SQLCODE IS -104 (HEX: FFFFFF98)
 : [42601] [IBM][CLI Driver][DB2] SQL0104N  An unexpected token "MGRPA" was
 :  found following "".  Expected tokens may include:  "UNION".  SQLSTATE=4
 : 2601
 L    (FOC1405) SQL PREPARE ERROR. 

Any comments or suggestions? I'd appreciate any help you can provide

The sql not equal <> may be getting confused with the let syntax which allows the word following the let to be substituted in the LET string.

btw FOCUS to WebFOCUS does not require conversion of TABLE to SQL passthru.

Thanks hammo1j,

I changed the <> syntax to AND NOT EMP_GRP_MBR = ' ' with mo change in the results. The reason we are changing to SQL passthru is that we are dealing with high volume tables, heavily indexed, and we want to take advantage of those indices. Some tables have up to 10-15 indices.

quote:

LET MGRPA = '901100',' ',' ',' ',

The problem is the quotes. Despite the information displayed by ? LET, the leading quote is taken as a string delimiter, and not as part of the literal.

Jack, wouldn't you think it would fail on the mainframe, which it doesn't?

Not only does it not fail on the mainframe, the code's been running correctly on the mainframe since the late 80s/early 90s. Personally, I think 15/20+ years is a pretty good track record for a focexec. Wouldn't you agree?

Yes, I recognize the code/technique.

I haven't written a SQL pass-thru statement since early 2001, nor do I write Let statements any more, but as I recall, you'll need to rewrite all the Lets statements to Set statements for WebFOCUS.

Thanks Sue,

I'm not looking forward to converting LETs to SETs but it's what I suspected might need to be done. The mainframe works pretty slick doesn't it?

Have you tried using -SET &ECHO=ALL; so you can see what's being generated? I can't speak to DB2, but with Oracle, if the FOCUS code is written correctly, there are many times when WebFOCUS generates perfect SQL.

Thanks jgelona,

Yes, I had &ECHO=ALL; set when the above results were displayed. Even the ? LET turns out exactly as I would have hoped. An IBI tech support person identified some documentation that says you cannot assign a Dialogue Manager amper variable a Let variable. It is quite possible that even though the code has worked on the mainframe for many years, it has been technically illegal code and shouldn't have worked. At this point I'm resigned to converting LETs to SETs. As for WebFOCUS generating perfect SQL, see my response to hamm01j for the reason to go to SQL Passthru.

quote:

Originally posted by focuscon:
Thanks jgelona,

Yes, I had &ECHO=ALL; set when the above results were displayed. Even the ? LET turns out exactly as I would have hoped. An IBI tech support person identified some documentation that says you cannot assign a Dialogue Manager amper variable a Let variable. It is quite possible that even though the code has worked on the mainframe for many years, it has been technically illegal code and shouldn't have worked. At this point I'm resigned to converting LETs to SETs. As for WebFOCUS generating perfect SQL, see my response to hamm01j for the reason to go to SQL Passthru.

I agree it's technically illegal now, but when I originally developed the programs as a FOCUS newbie 20 years ago using the above technique, I'm pretty sure it was fully documented!!!! I just recently threw away my FOCUS 4.5 manual so I no longer can prove this was a documented feature.

BUT....This is a prime example of:

1. IBI changing the FOCUS coding rules over time
2. A Company not modifying the focexecs over an extented period of time as FOCUS changes unless the programs fail.

You're correct in converting these extremely resource intensive programs to SQL Passthru, or even better, just adding a web front end to them and continue to run them on the mainframe.


Sue <--- retiring soon and extremely glad I no longer have to deal with large data bases, MVS, JCL, IMS, DB2, JESx, etc.

I did some testing and the coding technique still works just fine as long as you stay with FOCUS code.

I think the problem occurs because of the way FOCUS is handling the SQL. I believe when FOCUS sees the SQL, except for & variables which FOCUS knows have to be evaluated, FOCUS just passes the text over to the database. For example, how would WebFOCUS know that MGRP is not a table?

Also, even if this were just FOCUS code, the "AND" in AGRP, SGRP and MGRP needs to be "WHERE", otherwise, even the FOCUS code will not work.

quote:

Originally posted by sakeenan:
[QUOTE]Originally posted by focuscon:
Thanks jgelona,

Yes, I had &ECHO=ALL; set when the above results were displayed. Even the ? LET turns out exactly as I would have hoped. An IBI tech support person identified some documentation that says you cannot assign a Dialogue Manager amper variable a Let variable. It is quite possible that even though the code has worked on the mainframe for many years, it has been technically illegal code and shouldn't have worked. At this point I'm resigned to converting LETs to SETs. As for WebFOCUS generating perfect SQL, see my response to hamm01j for the reason to go to SQL Passthru.

In 1987 when the code was written by me, only IF was available.
(First message from my iPhone. This message box needs a scroll bar.)



I agree it's technically illegal now, but when I originally developed the programs as a FOCUS newbie 20 years ago using the above technique, I'm pretty sure it was fully documented!!!! I just recently threw away my FOCUS 4.5 manual so I no longer can prove this was a documented feature.

BUT....This is a prime example of:

1. IBI changing the FOCUS coding rules over time
2. A Company not modifying the focexecs over an extented period of time as FOCUS changes unless the programs fail.

You're correct in converting these extremely resource intensive programs to SQL Passthru, or even better, just adding a web front end to them and continue to run them on the mainframe.


Sue <--- retiring soon and extremely glad I no longer have to deal with large data bases, MVS, JCL, IMS, DB2, JESx, etc.

I do not think the SQL syntax parser ever supported LET substitution.
When using &VARS ... there is a behavior you can use that makes code simpler. When DM exchanges an &VAR that has the value FOC_NONE the whole line is thrown away.
-SET &PLACE = 'FOC_NONE';
SQL SELECT COUNTRY,CAR FROM CAR
WHERE COUNTRY = '&PLACE';
END
This works real nice in TABLE where you can have multiple WHERE and or IFs.

I wrote the original code for a VSAM data base. The company did not have DB2 then.

I don't recall when the programs were changed to DB2, but anytime you convert or write a program for DB2, the programmer should do a trace and see what SQL code is actually being written.