

Go  New  Search  Notify  Tools  Reply 
Virtuoso 
I'm in the process of generating a WHEREclause for one dataset out of a different dataset. 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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Expert 
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
 

Virtuoso 
Not quite, that repeats the WHERE COUNTRY EQ statements, while I only want one per multiverb segment. The output should be something like the following, which I obtained by copypasting 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 multicolumn 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 keycolumn 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 INlist 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 statementlength 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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Expert 
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
 

Virtuoso 
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
 

Virtuoso 
The solution using PUTDDREC looks very promising! It allows me to create formatted output in one file, while writing metadata 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 WHEREstatement 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 mindboggling. I will certainly keep this option in mind! WebFOCUS 8.1.03, Windows 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Virtuoso 
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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Virtuoso 
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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Virtuoso 
What I ended up with was a 2part solution. The first part is a passthru 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 WHEREclause closing braces into a column specifically for those. That part performs great and really has but a single drawback: You can't put a WHEREcondition 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 passthru SQL and uses COMPUTE with PUTDDREC to write each string to a line of the correct length (we ommit spacepadding 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 * Toplevel 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 * Bottomlevel sortfield , 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 WHEREclause 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 764/200864, IBM DB2/400, Oracle 11g & RDB, MS SQLServer 2005, SAP, PostgreSQL 11, Output: HTML, PDF, Excel 2010 : Member of User Group Benelux :  

Powered by Social Strata 
Please Wait. Your request is being processed... 