Is there a nicer way to create the list of primary keys that will be used in a sql command - see &inlist

(this list will have about 3000 keys)

SET HOLDFORMAT=ALPHA

TABLE FILE EXPORT_TBL
PRINT PRIMARY_KEY
WHERE AMT NE 0
ON TABLE HOLD AS PK
END

-SET &TOTPK = &LINES ;
-SET &INLIST = '';

-REPEAT PKLABEL FOR &I FROM 1 TO &TOTPK
-READ PK &PKVAL.&I.A11.
-SET &JA = IF &I NE &TOTPK THEN &INLIST||&PKVAL.&I||',' ELSE &INLIST||&PKVAL.&I;
-PKLABEL

ENGINE SQLMSS SET DEFAULT_CONNECTION IBNR
SQL SQLMSS
INSERT INTO dbo.IBNR_SNAPSHOT
SELECT
RESERVE_CELL,
AMT,
TIME_STAMP,
FROM EXPORT_TBL
WHERE PRIMARY_KEY IN ( &INLIST);
END

This message has been edited. Last edited by: jammer,

I would suggest unraveling the key list and holding it as a temp fex, in the form

 first key
,second key
,third key
...

and then imbedding it via -INCLUDE


first LIST the keys you want and HOLD,

Use FILEDEF (or APP FI) to declare your KEY_LIST file

DEFINE FILE HOLD
comma/A1=IF LIST EQ 1 THEN ' ' ELSE ',';
END
TABLEF FILE HOLD
PRINT
comma KEY_FIELD
ON TABLE SAVE AS KEY_LIST
END

In the SQL request, change

WHERE PRIMARY_KEY IN ( &INLIST );

to

WHERE PRIMARY_KEY IN (
-INCLUDE reference_to_the_temp_file
);

You will have to code the -INCLUDE to properly reference the server-side temp file.


the choice depends on ones taste -- and on the limit for the number of lines in a SQL passthru request vs. the limit on the size of an amper var.

what kind of table is the export_tbl?

can you put it in a sql file?

Per idea suggested by j.gross, the code below worked for me.
Note:
The -INCLUDE file is FILEDEFed as a .fex.
The -99 is a nonvalid key value severing as a placeholder.

 APP FI SHIPPERS DISK shippers.fex
 TABLE FILE CAR
 PRINT
 COMPUTE KEYVAL/A9 WITH COUNTRY = 
 IF COUNTRY EQ 'ENGLAND' THEN ',6770'  ELSE
 IF COUNTRY EQ 'ITALY'   THEN ',13151' ELSE
 IF COUNTRY EQ 'FRANCE'  THEN ',9885'  ELSE
 IF COUNTRY EQ 'JAPAN'   THEN ',14242' ELSE
                              ',13247' ;
 ON TABLE SET HOLDLIST PRINTONLY
 ON TABLE HOLD AS SHIPPERS FORMAT ALPHA
 END
-RUN
 ENGINE SQLORA SET DEFAULT_CONNECTION TEST
 SQL SQLORA
 select *
 from company
 WHERE COMPANYID IN ( -99
-INCLUDE SHIPPERS
 );
END

Thanks j. gross that looks good (I wasn't sure if there was just a special hold format to use instead of using the define). And thanks dbeagan - you made it a bit easier with the non-valid key

Frank - the truth is I am actually going to insert data from export_tbl to another sql table.

I try to limit the inserts/updates I do from WebFocus since this is frowned upon - with that being I would also avoid writing the list to sql.

But I might see where you are going - If I did write the list to sql - then I could do a join to this sql table