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     INPUT 1 RECORD OUTPUT 2 RECORDS

Read-Only Read-Only Topic
Go
Search
Notify
Tools
INPUT 1 RECORD OUTPUT 2 RECORDS
 Login/Join
 
JimmyD
Member
posted
I need to read one record in from an Oracle table and output 2 records to a Focus hold file from that 1 input record.


Here's the data from the input file:

ACCT_NBR SLS_1 SLS_2
123412343 1245 3240
344465321 3452 2145
333127854 4267 1299

This is what I want in the output file, two records for each input file.

ACCT_NBR SLS_NBR
123412343 1245
123412343 3240
344465321 3452
344465321 2145
333127854 4267
333127854 1299

Any help is most appreciate.

Jim Dale

WEBFOCUS 7.11, UNIX, Oracle tables
 
Posts: 12 | Location: Bay Area - San Francisco | Registered: January 06, 2006Report This Post
susannah
Expert
posted Hide Post
set asnames = on
table file thing
print sls_1 as sls_nbr by acct_nbr
on table hold as h1
end
table file thing
print sls_2 as sls_nbr by acct_nbr
on table hold as h2
end
table file h1
print sls_nbr by acct_nbr
{on table hold as h3}
more
file h2
end



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
Alan B
Virtuoso
posted Hide Post
Or, using a little bit of McGuyver:
-* create temporary file for a join
DEFINE FILE yourFile
CNTR/I4 WITH ACCT_NBR = CNTR+1;
BLANK/A1 WITH ACCT_NBR = ' ';
END
TABLE FILE yourFile
WRITE BLANK
LIST CNTR
IF CNTR LE 2
-* put read or record limit in here
ON TABLE HOLD AS MCGUYVER FORMAT FOCUS INDEX BLANK
END
-*join to temp file with defined field
JOIN BLANK WITH ACCT_NBR IN yourFile TO BLANK IN MCGUYVER
DEFINE FILE yourFile
NEW_SLS/I4 = IF CNTR EQ 1 THEN SLS_1 ELSE SLS_2;
BLANK/A1 WITH ACCT_NBR = ' ';
END
TABLE FILE yourFile
PRINT NEW_SLS
BY ACCT_NBR
END

This message has been edited. Last edited by: Alan B,


Alan.
WF 7.705/8.007
 
Posts: 1451 | Location: Portugal | Registered: February 07, 2007Report This Post
FrankDutch
Virtuoso
posted Hide Post
how about

TABLE FILE XXX
PRINT
ACCT_NBR SLS_1 OVER
ACCT_NBR SLS_2
BY ACCT_NBR NOPRINT
END




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
susannah
Expert
posted Hide Post
OVER isn't going to give him a file, tho, Frank, its just a display mechanism, and he says he wants a file.



In Focus since 1979///7706m/5 ;wintel 2008/64;OAM security; Oracle db, ///MRE/BID
 
Posts: 3811 | Location: Manhattan | Registered: October 28, 2003Report This Post
FrankDutch
Virtuoso
posted Hide Post
thx Susannah I did not see he wanted in a file, but you're right of course.




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
JimmyD
Member
posted Hide Post
Thanks everyone, I will try your suggestions.

Jim dale
 
Posts: 12 | Location: Bay Area - San Francisco | Registered: January 06, 2006Report This Post
TexasStingray
Master
posted Hide Post
Depending on how many records there are here is another option
TABLE FILE oracle_file
PRINT
SLS_1
BY ACCT_NBR 
MORE 
FILE oracle_file
SLS_2
BY ACCT_NBR 
ON TABLE HOLD AS focus_file
END


I think I have the syntax correct
 
Posts: 865 | Registered: May 24, 2004Report This Post
OPALTOSH
Platinum Member
posted Hide Post
TexasStingray,
The correct Syntax is
TABLE FILE oracle_file
PRINT
SLS_1
BY ACCT_NBR
ON TABLE HOLD AS focus_file
MORE
FILE oracle_file
ON TABLE HOLD AS focus_file
END
The only code you put in the second part are selection tests.
Your second file must have fields (real or defined) of the same name and format as the second file.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
OPALTOSH
Platinum Member
posted Hide Post
The McGyver technique is your best option.
 
Posts: 140 | Location: Adelaide South Australia | Registered: October 27, 2006Report This Post
Danny-SRL
Virtuoso
posted Hide Post
Jimmy,
I would use an alternate MASTER. If you do:
TABLE FILE ...
PRINT ACCT_NBR SLS_1 SLS_2
ON TABLE HOLD AS MINE FORMAT ALPHA
END

You get a MASTER file something like this:
FILE=MINE, SUFFIX=FIX
SEGNAME=MINE
FIELD=ACCT_NBR...
FIELD=SLS_1...
FIELD=SLS_2...

Change that to
FILE=MINE, SUFFIX=FIX
SEGNAME=MINE
FIELD=ACCT_NBR...
SEGNAME=SLS, PARENT=MINE, OCCURS=VARIABLE
FIELD=SLS...

and save it

Then go back AND change your program to:
TABLE FILE ...
PRINT ACCT_NBR SLS_1 SLS_2
ON TABLE SAVE AS MINE
END
TABLE FILE MINE
PRINT ACCT_NBR SLS
ON TABLE HOLD FORMAT FOCUS
END

It should do the trick elegantly.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 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     INPUT 1 RECORD OUTPUT 2 RECORDS

Copyright © 1996-2020 Information Builders