Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Create SQL statement selecting records with primary keys in a hold file
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Create SQL statement selecting records with primary keys in a hold file
 Login/Join
 
Gold member
posted
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,


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Reply With QuoteReport This Post
Virtuoso
posted Hide Post
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.
 
Posts: 1925 | Location: NYC | In FOCUS since 1983 | Registered: January 11, 2005Reply With QuoteReport This Post
Virtuoso
posted Hide Post
what kind of table is the export_tbl?

can you put it in a sql file?




Frank

prod: WF 7.6.10 platform Windows,
databases: msSQL2000, msSQL2005, RMS, Oracle, Sybase,IE7
test: WF 7.6.10 on the same platform and databases,IE7

 
Posts: 2387 | Location: Amsterdam, the Netherlands | Registered: December 03, 2006Reply With QuoteReport This Post
Platinum Member
posted Hide Post
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


WF 8.2.06
Win10 / IE11
AHTML EXL2K PDF
 
Posts: 141 | Registered: October 19, 2010Reply With QuoteReport This Post
Gold member
posted Hide Post
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


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Create SQL statement selecting records with primary keys in a hold file

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.