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

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

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,

how about

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

OVER isn't going to give him a file, tho, Frank, its just a display mechanism, and he says he wants a file.

thx Susannah I did not see he wanted in a file, but you're right of course.

Thanks everyone, I will try your suggestions.

Jim dale

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

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.

The McGyver technique is your best option.

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.