Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


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

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Create SQL statement selecting records with primary keys in a hold file
 Login/Join
 
jammer
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, 2006Report This Post
j.gross
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, 2005Report This Post
FrankDutch
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, 2006Report This Post
dbeagan
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


WebFOCUS 8.2.06
 
Posts: 210 | Location: Sterling Heights, Michigan | Registered: October 19, 2010Report This Post
jammer
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, 2006Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

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-2020 Information Builders