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,

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

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!

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

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

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!

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!

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.

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