Hi,

I am trying to mimic the oracle pl sql code i have (which works perfectly correct) in webfocus by creating 2 hold files, join and report...

Hold Files are H_BB AND H_LDR AND Joining against ORD_NOSTRO master file WHICH IS the base table to which i do left outer join for the hold files...

But i get this error message when i run to expect export in exl

0 NUMBER OF RECORDS IN TABLE= 663 LINES= 663
1
0 NUMBER OF RECORDS IN TABLE= 3008 LINES= 3008
0
(FOC1070) VALUE FOR JOIN 'FROM' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED


I know i have to do INDEX into Hold Files but with i create the final output i don't get the balances which is from hold files...
'J1.H_LDR.CLOSING_BALANCE'
'J0.H_BB.AMT'
*******************************************
below is the webfocus code in created for this..
My Hold Files are H_BB AND H_LDR..ORD_BB AND ORD_LEDGER ARE THE CORREPSONDING Master files which is referring tables in oracle database...

the webfocus code is as below...

SET ALL = ON
TABLE FILE ORD_BB
PRINT
'ORD_BB.ORD_BB.GL_UNIT'
'ORD_BB.ORD_BB.CCY'
'ORD_BB.ORD_BB.NOSTRO_AC'
'ORD_BB.ORD_BB.NOSTRO_QUAL'
'ORD_BB.ORD_BB.AMT'
HEADING
""
FOOTING
""
WHERE ( ORD_BB.ORD_BB.VALUE_DATE EQ DT(2009/10/20 00:00:00) ) AND ( ORD_BB.ORD_BB.TYPE EQ '015' )
AND ( ORD_BB.ORD_BB.NOSTRO_AC EQ 'CASHSL' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H_BB FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
---i have all the report settings
$
ENDSTYLE
END
TABLE FILE ORD_LEDGER
PRINT
'ORD_LEDGER.ORD_LEDGER.GL_AREA_UNIT'
'ORD_LEDGER.ORD_LEDGER.AREA_CODE'
'ORD_LEDGER.ORD_LEDGER.CURRENCY'
'ORD_LEDGER.ORD_LEDGER.ACCOUNT'
'ORD_LEDGER.ORD_LEDGER.QUALIFIER'
'ORD_LEDGER.ORD_LEDGER.CLOSING_BALANCE'
HEADING
""
FOOTING
""
WHERE ( ORD_LEDGER.ORD_LEDGER.WSS_COA_DATE EQ DT(2009-10-20) ) AND ( ORD_LEDGER.ORD_LEDGER.ACCOUNT EQ 'CASHSL' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H_LDR FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
-------i have all the report settings
$
ENDSTYLE
END
JOIN
LEFT_OUTER ORD_NOSTRO.ORD_NOSTRO.AREA_UNIT AND ORD_NOSTRO.ORD_NOSTRO.ACCOUNT
AND ORD_NOSTRO.ORD_NOSTRO.CURRENCY AND ORD_NOSTRO.ORD_NOSTRO.QUALIFIER IN
ORD_NOSTRO TO UNIQUE H_BB.H_BB.GL_UNIT AND H_BB.H_BB.NOSTRO_AC AND H_BB.H_BB.CCY
AND H_BB.H_BB.NOSTRO_QUAL IN H_BB TAG J0 AS J0
END
JOIN
LEFT_OUTER ORD_NOSTRO.ORD_NOSTRO.ACCOUNT AND ORD_NOSTRO.ORD_NOSTRO.AREA_UNIT
AND ORD_NOSTRO.ORD_NOSTRO.CURRENCY AND ORD_NOSTRO.ORD_NOSTRO.QUALIFIER IN
ORD_NOSTRO TO UNIQUE H_LDR.H_LDR.ACCOUNT AND H_LDR.H_LDR.GL_AREA_UNIT
AND H_LDR.H_LDR.CURRENCY AND H_LDR.H_LDR.QUALIFIER IN H_LDR TAG J1 AS J1
END
TABLE FILE ORD_NOSTRO
PRINT
'ORD_NOSTRO.ORD_NOSTRO.AREA_CODE'
'ORD_NOSTRO.ORD_NOSTRO.CURRENCY'
'ORD_NOSTRO.ORD_NOSTRO.SHORT_CODE'
'J1.H_LDR.CLOSING_BALANCE'
'J0.H_BB.AMT'
HEADING
""
FOOTING
""
WHERE ( ORD_NOSTRO.ORD_NOSTRO.NOSTRO_VOSTRO_FLAG EQ MISSING );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
---i have all the report settings
$
ENDSTYLE
END

When i run this throws me that error..

My correct Pl SQL code is as follows..
select nos.area_code,nos.currency,nos.short_code,lg.closing_balance,bb.amt

from ord_nostro nos

Left outer join ord_bb bb on nos.area_unit = bb.gl_unit
and nos.account = bb.nostro_ac
and nos.currency = bb.ccy
and nos.qualifier = bb.nostro_qual
and to_date(bb.value_date,'dd-mon-yy') = '20-OCT-09'
and bb.type = '015'
AND bb.nostro_ac = 'CASHSL'

Left outer join ord_ledger lg on nos.area_unit = lg.gl_area_unit
and nos.account = lg.account
and nos.currency = lg.currency
and nos.qualifier = lg.qualifier
and to_date(lg.wss_coa_date,'dd-mon-yy') = '20-OCT-09'
AND lg.account = 'CASHSL'

where NOS.NOSTRO_VOSTRO_FLAG IS NULL
order by nos.area_code,nos.currency,nos.short_code

This gives me the perfect out put but the issue get the code working in webfocus using focus/fex...

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

You don't have to have indexes but you sure as heck have to have the hold files sorted in the same order. Plus I'm not sure that you can do a fixed to fixed file join with multiple fields. I'd suggest that you concatenate all of th fields together (if some are numeric, you'll have to convert but we can get to that later), sort by the concatenated field, then join.

Try that. Also post the formats of the fields that participate in the join.

Thanks Ginny for the reply..
I sorted all fields invlved in join staement for the Hold Files using BY..

Can you provide me how to incorporate the concatenate as you mentioned...
And also i sorted the output file too..

Here is the below code after the sorting..
SET ALL = ON
TABLE FILE ORD_BB
PRINT
'ORD_BB.ORD_BB.AMT'
BY 'ORD_BB.ORD_BB.GL_UNIT'
BY 'ORD_BB.ORD_BB.CCY'
BY 'ORD_BB.ORD_BB.NOSTRO_AC'
BY 'ORD_BB.ORD_BB.NOSTRO_QUAL'
HEADING
""
FOOTING
""
WHERE ( ORD_BB.ORD_BB.VALUE_DATE EQ DT(2009/10/20 00:00:00) ) AND ( ORD_BB.ORD_BB.TYPE EQ '015' )
AND ( ORD_BB.ORD_BB.NOSTRO_AC EQ 'CASHSL' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H_BB FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
----
$
ENDSTYLE
END
TABLE FILE ORD_LEDGER
PRINT
'ORD_LEDGER.ORD_LEDGER.CLOSING_BALANCE'
BY 'ORD_LEDGER.ORD_LEDGER.GL_AREA_UNIT'
BY 'ORD_LEDGER.ORD_LEDGER.CURRENCY'
BY 'ORD_LEDGER.ORD_LEDGER.ACCOUNT'
BY 'ORD_LEDGER.ORD_LEDGER.QUALIFIER'
HEADING
""
FOOTING
""
WHERE ( ORD_LEDGER.ORD_LEDGER.WSS_COA_DATE EQ DT(2009-10-20) ) AND ( ORD_LEDGER.ORD_LEDGER.ACCOUNT EQ 'CASHSL' );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H_LDR FORMAT ALPHA
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
---
$
ENDSTYLE
END
JOIN
LEFT_OUTER ORD_NOSTRO.ORD_NOSTRO.AREA_UNIT AND ORD_NOSTRO.ORD_NOSTRO.ACCOUNT
AND ORD_NOSTRO.ORD_NOSTRO.CURRENCY AND ORD_NOSTRO.ORD_NOSTRO.QUALIFIER IN
ORD_NOSTRO TO UNIQUE H_BB.H_BB.GL_UNIT AND H_BB.H_BB.NOSTRO_AC AND H_BB.H_BB.CCY
AND H_BB.H_BB.NOSTRO_QUAL IN H_BB TAG J0 AS J0
END
JOIN
LEFT_OUTER ORD_NOSTRO.ORD_NOSTRO.ACCOUNT AND ORD_NOSTRO.ORD_NOSTRO.AREA_UNIT
AND ORD_NOSTRO.ORD_NOSTRO.CURRENCY AND ORD_NOSTRO.ORD_NOSTRO.QUALIFIER IN
ORD_NOSTRO TO UNIQUE H_LDR.H_LDR.ACCOUNT AND H_LDR.H_LDR.GL_AREA_UNIT
AND H_LDR.H_LDR.CURRENCY AND H_LDR.H_LDR.QUALIFIER IN H_LDR TAG J1 AS J1
END
TABLE FILE ORD_NOSTRO
PRINT
'ORD_NOSTRO.ORD_NOSTRO.SHORT_CODE'
'J1.H_LDR.CLOSING_BALANCE'
'J0.H_BB.AMT'
BY 'ORD_NOSTRO.ORD_NOSTRO.QUALIFIER'
BY 'ORD_NOSTRO.ORD_NOSTRO.ACCOUNT'
BY 'ORD_NOSTRO.ORD_NOSTRO.CURRENCY'
BY 'ORD_NOSTRO.ORD_NOSTRO.AREA_CODE'
HEADING
""
FOOTING
""
WHERE ( ORD_NOSTRO.ORD_NOSTRO.NOSTRO_VOSTRO_FLAG EQ MISSING );
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL2K
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
---
$
ENDSTYLE
END

and also the format of the field is same across tables...all 4 joined columns in both the hold files and the main ORD_NOSTRO tables are in varchar and same size...

DEFINE FILE ORD_BB
BB_KEY/Ann=GL_UNIT|CCY|NOSTRO_AC|NOSTRO_QUAL;
END

TABLE FILE ORD_BB
PRINT
'ORD_BB.ORD_BB.AMT'
BY BB_KEY
BY 'ORD_BB.ORD_BB.GL_UNIT'
BY 'ORD_BB.ORD_BB.CCY'
BY 'ORD_BB.ORD_BB.NOSTRO_AC'
BY 'ORD_BB.ORD_BB.NOSTRO_QUAL'
END
Do this for each table. You'll have to calculate the 'nn' in the defined field format. I recommend strongly that this not be a varchar, i.e. no AnnV in the format. Also, if any of the fields above do not have an Ann format, they must be converted to alpha. For instance if CCY has a format of YY, then it must be converted before doing the concatenation. There are lots of posts on the forum that can tell you how to do that.

Is this starting to make sense?

Just curious as to why you are using HOLD files and not pointing to the Oracle tables. You could do the joins and data extract via WebFOCUS code or via SQl passthru - since you already have SQL that works, why not use it in the WebFOCUS program?

quote:

since you already have SQL that works, why not use it in the WebFOCUS program?

Absolutely agree with this.

You are also double handling the data which is never going to improve efficiency.

T