[SOLVED] Converting multi-verb request to ALPHA file, keeping blanks
I'm in the process of generating a WHERE-clause for one data-set out of a different data-set. I'm almost there, I have the correct hierarchy of clauses, but the last step is failing me.
Here's an example of what I got:
APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM
COMPUTE W1/A100 = ' WHERE (COUNTRY EQ ''' || COUNTRY|| '''' ; NOPRINT
COMPUTE P1/A100 = '))) OR (COUNTRY EQ ''' || COUNTRY || '''' ; NOPRINT
COMPUTE L/I6 = LAST L +1; NOPRINT
COMPUTE C1/A100 = IF L EQ 1 THEN W1 ELSE P1; AS ''
BY COUNTRY NOPRINT
SUM
COMPUTE W2/A100 = ' AND (CAR EQ ''' || CAR|| '''' ; NOPRINT
COMPUTE P2/A100 = ')) OR (CAR EQ ''' || CAR || '''' ; NOPRINT
COMPUTE C2/A100 = IF LAST COUNTRY NE COUNTRY THEN W2 ELSE P2; AS ''
BY COUNTRY NOPRINT BY CAR NOPRINT
SUM
COMPUTE W3/A100 = ' AND MODEL IN ( ''' || MODEL|| '''' ; NOPRINT
COMPUTE P3/A100 = ',''' ||MODEL || '''' ; NOPRINT
COMPUTE C3/A100 = IF LAST CAR NE CAR THEN W3 ELSE P3; AS ''
BY COUNTRY NOPRINT BY CAR NOPRINT BY MODEL NOPRINT
-*ON TABLE HOLD AS HWHERE
END
The output should be an ALPHA file with same structure as the output of the above. When I try, the empty cells repeat in the ALPHA file and that is not what I need!
Is there a way to preserve blanks when HOLDING as ALPHA?
Note: In my actual case, the fields used within the strings are from a different table than the fields appended to them.This message has been edited. Last edited by: Wep5622,
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
January 29, 2019, 11:27 AM
Tony A
Hi Wep,
Do you mean something like the following?
APP PREPENDPATH IBISAMP
TABLE FILE CAR
SUM
COMPUTE W1/A100 = ' WHERE (COUNTRY EQ ''' || COUNTRY|| '''' ; NOPRINT
COMPUTE P1/A100 = '))) OR (COUNTRY EQ ''' || COUNTRY || '''' ; NOPRINT
COMPUTE L/I6 = LAST L +1; NOPRINT
COMPUTE C1/A100 = IF L EQ 1 THEN W1 ELSE P1; AS ''
BY COUNTRY NOPRINT
SUM
COMPUTE W2/A100 = ' AND (CAR EQ ''' || CAR|| '''' ; NOPRINT
COMPUTE P2/A100 = ')) OR (CAR EQ ''' || CAR || '''' ; NOPRINT
COMPUTE C2/A100 = IF LAST COUNTRY NE COUNTRY THEN W2 ELSE P2; AS ''
BY COUNTRY NOPRINT BY CAR NOPRINT
SUM
COMPUTE W3/A100 = ' AND MODEL IN ( ''' || MODEL|| '''' ; NOPRINT
COMPUTE P3/A100 = ',''' ||MODEL || '''' ; NOPRINT
COMPUTE C3/A100 = IF LAST CAR NE CAR THEN W3 ELSE P3; AS ''
BY COUNTRY NOPRINT BY CAR NOPRINT BY MODEL NOPRINT
ON TABLE SET SHOWBLANKS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD AS HWHERE FORMAT ALPHA
END
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
January 30, 2019, 04:20 AM
Wep5622
Not quite, that repeats the WHERE COUNTRY EQ statements, while I only want one per multi-verb segment.
The output should be something like the following, which I obtained by copy-pasting the HTML output into VIM (reducing multiple spaces to single spaces would be a nice bonus):
WHERE (COUNTRY EQ 'ENGLAND' AND (CAR EQ 'JAGUAR' AND MODEL IN ( 'V12XKE AUTO'
,'XJ12L AUTO'
)) OR (CAR EQ 'JENSEN' AND MODEL IN ( 'INTERCEPTOR III'
)) OR (CAR EQ 'TRIUMPH' AND MODEL IN ( 'TR7'
))) OR (COUNTRY EQ 'FRANCE' AND (CAR EQ 'PEUGEOT' AND MODEL IN ( '504 4 DOOR'
))) OR (COUNTRY EQ 'ITALY' AND (CAR EQ 'ALFA ROMEO' AND MODEL IN ( '2000 4 DOOR BERLINA'
,'2000 GT VELOCE'
,'2000 SPIDER VELOCE'
)) OR (CAR EQ 'MASERATI' AND MODEL IN ( 'DORA 2 DOOR'
))) OR (COUNTRY EQ 'JAPAN' AND (CAR EQ 'DATSUN' AND MODEL IN ( 'B210 2 DOOR AUTO'
)) OR (CAR EQ 'TOYOTA' AND MODEL IN ( 'COROLLA 4 DOOR DIX AUTO'
))) OR (COUNTRY EQ 'W GERMANY' AND (CAR EQ 'AUDI' AND MODEL IN ( '100 LS 2 DOOR AUTO'
)) OR (CAR EQ 'BMW' AND MODEL IN ( '2002 2 DOOR'
,'2002 2 DOOR AUTO'
,'3.0 SI 4 DOOR'
,'3.0 SI 4 DOOR AUTO'
,'530I 4 DOOR'
,'530I 4 DOOR AUTO'
(With ')));' appended manually to fix the syntax)
The purpose is to generate a more compact TABLE request then DB_INFILE creates for multi-column keys, as I'm running into a 32kB (or thereabout) statement length limit. I realised that I could put a lot more values into the statement if I don't have to repeat the whole key-column list each time!
BTW, I'm not sure who's limitation this is. The statement first goes to our remote reporting server, then to our SAP R3 adapter and then to Hana. There is probably a limit to the number of key values in the query as well; I know that our Oracle database rejects statements with more than 1000 IN-list values (which we just work around by inserting an OR every 1000 values, ahum...).
That said, this morning I also realised that with this approach I have no idea how many keys I can add to my statement until I reach that statement-length limit. Since the source of my key values is a passthru SQL statement, I'm probably better off generating the entire TABLE request from SQL, using that 32kB statement length limit as a query constraint (and a 1000 item limit for good measure).
This is turning into an interesting challenge, but I knew this wasn't going to be easy!
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
January 30, 2019, 05:42 AM
Tony A
Use PUTDDREC, amend the code as necessary as not tested for accurate syntax -
FILEDEF HWHERE DISK HWHERE.FEX (APPEND
-RUN
TABLE FILE CAR
SUM
COMPUTE W1/A100 = ' WHERE (COUNTRY EQ ''' || COUNTRY|| '''' ; NOPRINT
COMPUTE P1/A100 = '))) OR (COUNTRY EQ ''' || COUNTRY || '''' ; NOPRINT
COMPUTE L/I6 = LAST L + 1; NOPRINT
COMPUTE C1/I1 = IF L EQ 1 THEN PUTDDREC('HWHERE', 6, W1, 100, C1)
ELSE PUTDDREC('HWHERE', 6, P1, 100, C1); AS ''
BY COUNTRY NOPRINT
SUM
COMPUTE W2/A100 = ' AND (CAR EQ ''' || CAR|| '''' ; NOPRINT
COMPUTE P2/A100 = ')) OR (CAR EQ ''' || CAR || '''' ; NOPRINT
COMPUTE C2/I1 = IF LAST COUNTRY NE COUNTRY THEN PUTDDREC('HWHERE', 6, W2, 100, C2)
ELSE PUTDDREC('HWHERE', 6, P2, 100, C2); AS ''
BY COUNTRY NOPRINT BY CAR NOPRINT
SUM
COMPUTE W3/A100 = ' AND MODEL IN ( ''' || MODEL|| '''' ; NOPRINT
COMPUTE P3/A100 = ',''' ||MODEL || '''' ; NOPRINT
COMPUTE C3/I1 = IF LAST CAR NE CAR THEN PUTDDREC('HWHERE', 6, W3, 100, C3)
ELSE PUTDDREC('HWHERE', 6, P3, 100, C3); AS ''
BY COUNTRY NOPRINT BY CAR NOPRINT BY MODEL NOPRINT
ON TABLE SET SHOWBLANKS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD
END
-RUN
-WRITE HWHERE )))
CMD TYPE HWHERE.FEX
T
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
January 31, 2019, 09:05 AM
GamP
Or ... keep your original code, but at the end instead of:
ON TABLE SET SHOWBLANKS ON
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE PCHOLD AS HWHERE FORMAT ALPHA
, put in:
ON TABLE SET PAGE NOLEAD
ON TABLE HOLD AS HWHERE FORMAT DOC
You will need the filedef up front:
FILEDEF HWHERE DISK HWHERE.FEX (APPEND
-RUN
GamP
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
February 01, 2019, 05:56 AM
Wep5622
The solution using PUTDDREC looks very promising! It allows me to create formatted output in one file, while writing meta-data for that file (such as the total length of what was written using PUTDDREC) to a conventional hold file.
I'll continue trying to create my WHERE-statement text using SQL, as I see an opportunity to break output at the moment the generated text exceeds a certain size without iterating through the records hierarchically, but that problem is mind-boggling.
I will certainly keep this option in mind!
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 04, 2019, 08:18 AM
Wep5622
Well, what do you know... It turns out that if it walks like a duck and it quacks like a duck, it's not necessarily a duck!
In this case, although my problem looks like a hierarchical problem, it can be solved fairly simple using LAST. (Very) much like the code that Tony posted.
Combining this with PUTDDREC allows me to track some related data at the side.
FILEDEF HWHERE DISK HWHERE.FEX (APPEND
TABLE FILE CAR
SUM
COMPUTE L/I6 = LAST L +1; NOPRINT
COMPUTE W1/A100 = IF L EQ 1
THEN 'WHERE (COUNTRY EQ ''' | COUNTRY || ''''
ELSE ')) OR (COUNTRY EQ ''' | COUNTRY || ''''; NOPRINT
COMPUTE L1/I6 = ARGLEN(100, W1, L1); NOPRINT
COMPUTE F1/I1 = PUTDDREC('HWHERE', 6, W1, L1, F1); NOPRINT
BY COUNTRY NOPRINT
SUM
COMPUTE W2S/A7 = IF LAST COUNTRY NE COUNTRY THEN ' AND '
ELSE IF LAST CAR NE CAR THEN ')) OR ('
ELSE ''; NOPRINT
COMPUTE W2/A100 = W2S | 'CAR EQ ''' | CAR || ''''; NOPRINT
COMPUTE L2/I6 = ARGLEN(100, W2, L2); NOPRINT
COMPUTE F2/I1 = PUTDDREC('HWHERE', 6, W2, L2, F2); NOPRINT
BY COUNTRY NOPRINT
BY CAR NOPRINT
SUM
COMPUTE W3/A100 = IF LAST COUNTRY EQ COUNTRY AND LAST CAR EQ CAR
THEN ',''' || MODEL || ''''
ELSE ' AND MODEL IN (''' || MODEL || ''''; NOPRINT
COMPUTE L3/I6 = ARGLEN(100, W3, L3); NOPRINT
COMPUTE F3/I1 = PUTDDREC('HWHERE', 6, W3, L3, F3); NOPRINT
COMPUTE TOTALLEN/I10 = LAST TOTALLEN + MAX.L1 + MAX.L2 + MAX.L3;
COMPUTE TUPLE/A100 = '(''' || COUNTRY || ''',''' || CAR || ''',''' || MODEL || ''')';
BY COUNTRY NOPRINT
BY CAR NOPRINT
BY MODEL NOPRINT
WHERE TOTAL TOTALLEN LE 1024;
END
-RUN
CMD TYPE HWHERE
Since I need to limit the length of the total generated statement (to ~32kB), I'm rather happy with my dual use of the computed lengths of the generated strings; they server to both limit the length of the lines put to HWHERE and to aid in limiting the number of results.
And as a bonus, the last value of TUPLE gives me a string to use as starting point to continue the process in case it got terminated prematurely.
I'll mark this one as SOLVED!
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 04, 2019, 10:03 AM
Wep5622
Oooookayyy... ALMOST solved...
It turns out that:
WHERE TOTAL TOTALLEN LE 1024;
...gets applied too late and the HWHERE file still contains output from all rows!
It looks like this needs to be split across two TABLE requests; the first doing the calculations and the limiting, the second writing the HWHERE file.
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :
February 18, 2019, 04:27 AM
Wep5622
What I ended up with was a 2-part solution.
The first part is a pass-thru SQL statement applying window functions sum, lag and lead on the results in order. That allows me to calculate when I need an opening brace (')') or an extra closing brace.
It also calculates how large my statement has gotten up to the next row - that's when I use lead(keyx) over (order by key1, key2, ...), for putting the final WHERE-clause closing braces into a column specifically for those.
That part performs great and really has but a single drawback: You can't put a WHERE-condition on the result of a window function, so the query cannot be made to stop when we reach the desired total string length. I had to add a sufficiently large limit clause for that.
The second part reads the output of that pass-thru SQL and uses COMPUTE with PUTDDREC to write each string to a line of the correct length (we ommit space-padding there).
The SQL is like this (I used the CAR file twice here, which doesn't make discerning source table from target table any easier, but I hope you get the drift):
select
WC || W1 || W2 || W3 || WX as src_where
from (
select
WC, W1, W2, W3
-* Closing braces; add to last line
, case
-* last line
when last_value(seq) over () = seq
-* total size
or sum(length(WC) + length(W1) + L1 +1 + length(W2) + L2 +1 + length(W3) + L3 +1) over keyOrder
-* next line length
+ lead(length(WC) + length(W1) + L1 +1 + length(W2) + L2 +1 + length(W3) + L3 +1) over keyOrder
-* will next line exceed length limit?
+ 05 > 29422
then '))); '
else ''
end as WX
, 05 + sum(length(WC) + length(W1) + L1 +1 + length(W2) + L2 +1
+ length(W3) + L3 +1) over keyOrder as TotalLen
, COUNTRY, CAR, MODEL
from (
select
row_number() over keyOrder as seq
-* Close IN at bottom level (only on column immediately before!)
, case
when lag(CAR, 1, '') over keyOrder not in (CAR, '')
then ')'
else ''
end as WC
-* Top-level sort field
, case
when lag(COUNTRY) over keyOrder is null
then 'WHERE (COUNTRY EQ ''' || COUNTRY || ''''
when lag(COUNTRY) over keyOrder <> COUNTRY
then ') OR (COUNTRY EQ ''' || COUNTRY || ''''
else ''
end as W1
-* ...length
, case
when lag(COUNTRY) over keyOrder is null
or lag(COUNTRY) over keyOrder <> COUNTRY
then 7
else 0
end as L1
-* Intermediary sort fields (for multiple fields, expand with 'or lag' for each predeccesor)
, case
when lag(COUNTRY, 1, '') over keyOrder <> COUNTRY
then ' AND (CAR EQ ''' || CAR || ''''
when lag(CAR, 1, '') over keyOrder <> CAR
then ') OR (CAR EQ ''' || CAR || ''''
else ''
end as W2
-* ...length
, case
when lag(COUNTRY, 1, '') over keyOrder <> COUNTRY
or lag(CAR, 1, '') over keyOrder <> CAR
then 7
else 0
end as L2
-* Bottom-level sort-field
, case
when lag(COUNTRY, 1, '') over keyOrder <> COUNTRY
or lag(CAR, 1, '') over keyOrder <> CAR
then ' AND MODEL IN (''' || MODEL || ''''
when lag(MODEL, 1, '') over keyOrder <> MODEL
then ',''' || MODEL || ''''
else ''
end as W3
-* ...length
, case
when lag(COUNTRY, 1, '') over keyOrder <> COUNTRY
or lag(CAR, 1, '') over keyOrder <> CAR
or lag(MODEL, 1, '') over keyOrder <> MODEL
then 5
else 0
end as L3
, COUNTRY, CAR, MODEL
from public.CAR
window keyOrder as (order by COUNTRY, CAR, MODEL)
limit 5000
) clauses
window keyOrder as (order by COUNTRY, CAR, MODEL)
) conds
where totalLen <= 29422;
Much of the above is generated using Dialog Manager code (array variables, loops, etc.), but the example is complex enough w/o extra obfuscation I bet
There are a couple of "magic numbers" in there; 29422 is the amount of space my code calculated to be left available for the WHERE-clause string so that the total request length stays under ~30kB. There are also a few smaller numbers that attempt to adjust the calculated lengths for the changes WF makes in the final translation of the request back to SQL/ABAP (that is where the statement length actually counts!).
This is read using the following FOCUS statement:
-* NOTE: No (APPEND needed
FILEDEF HWHERE DISK &WHERE_FILE.FEX
TABLE FILE SQLBATCH
PRINT
COMPUTE L/I6 = ARGLEN(256, src_where, L); NOPRINT
COMPUTE F/I1 = PUTDDREC('HWHERE', 6, src_where, L, F); NOPRINT
-* Other output of interest can go here
ON TABLE HOLD AS HWHERERESULT
END
-RUN
When using that clause, you want to make sure there is actually a file to include. If there was some error in the process, and the file did not get created, we don't want to accidentally read the entire file (although with CAR that's not a problem of course)!
I solved that by doing a "phantom read" of the table first:
TABLE FILE CAR
PRINT *
WHERE 1 EQ 0;
-INCLUDE &WHERE_FILE.FEX
END
-RUN
-IF &FOCERRNUM NE 0 THEN GOTO :FATAL;
TABLE FILE CAR
PRINT *
-INCLUDE &WHERE_FILE.FEX
END
-RUN
-:FATAL
WebFOCUS 8.1.03, Windows 7-64/2008-64, IBM DB2/400, Oracle 11g & RDB, MS SQL-Server 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :