When running the code below against from and to date i get the message the 'TO' column is not retrieved...

What i am doing is creating 2 hold files of the same table with different WHERE condition one for 'DR' and one for 'CR' and create a inner join with the both the hold files and get the output using html report...The reason is the data which is in 2 rows in database tables needs to be converetd to vertical mode..so for
DR i have mt as 10000.00 and CR i have amt as 2000.00 in 2 rows of the same table..i need to have that displayed in WF as single row under headings CR and DR...

Below is the code i do and get the error message.
--------------
1
0 NUMBER OF RECORDS IN TABLE= 2450 LINES= 2450
0
0 NUMBER OF RECORDS IN TABLE= 432 LINES= 432
0 NUMBER OF RECORDS IN TABLE= 2254 LINES= 2254
0 NUMBER OF RECORDS IN TABLE= 2273 LINES= 2273
(FOC1071) VALUE FOR JOIN 'TO' FIELD OUT OF SEQUENCE. RETRIEVAL ENDED
-------------
if i remove the 'J1.H1_CR.ENTRY_AMOUNT' AS 'CR AMT' i get teh data but only of DR and CR also need to be included..
--------------------------
TABLE FILE ORD_DRCRLD
PRINT
'ORD_DRCRLD.ORD_DRCRLD.AREA_UNIT'
'ORD_DRCRLD.ORD_DRCRLD.CURRENCY'
'ORD_DRCRLD.ORD_DRCRLD.MAIN_ACCOUNT'
'ORD_DRCRLD.ORD_DRCRLD.ACCOUNT'
'ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE'
'ORD_DRCRLD.ORD_DRCRLD.DR_CR_FLAG'
'ORD_DRCRLD.ORD_DRCRLD.ENTRY_AMOUNT'
HEADING
""
FOOTING
""
WHERE ( ORD_DRCRLD.ORD_DRCRLD.WSS_GDP_SITE EQ 'PRO' )
AND ( ORD_DRCRLD.ORD_DRCRLD.ACCOUNTING_TYPE EQ 'G' )
AND ( ORD_DRCRLD.ORD_DRCRLD.DR_CR_FLAG EQ 'DR' )
AND ( ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE GE DT(&FROM_DATE 00:00:00))
AND ( ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE LE DT(&TO_DATE 00:00:00));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H1_DR FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END
TABLE FILE ORD_DRCRLD
PRINT
'ORD_DRCRLD.ORD_DRCRLD.AREA_UNIT'
'ORD_DRCRLD.ORD_DRCRLD.CURRENCY'
'ORD_DRCRLD.ORD_DRCRLD.MAIN_ACCOUNT'
'ORD_DRCRLD.ORD_DRCRLD.ACCOUNT'
'ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE'
'ORD_DRCRLD.ORD_DRCRLD.DR_CR_FLAG'
'ORD_DRCRLD.ORD_DRCRLD.ENTRY_AMOUNT'
HEADING
""
FOOTING
""
WHERE ( ORD_DRCRLD.ORD_DRCRLD.WSS_GDP_SITE EQ 'PRO' )
AND ( ORD_DRCRLD.ORD_DRCRLD.ACCOUNTING_TYPE EQ 'G' )
AND ( ORD_DRCRLD.ORD_DRCRLD.DR_CR_FLAG EQ 'CR' )
AND ( ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE GE DT(&FROM_DATE 00:00:00))
AND ( ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE LE DT(&TO_DATE 00:00:00));
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE HOLD AS H1_CR FORMAT ALPHA
ON TABLE SET HTMLCSS ON
END
JOIN
INNER H1_DR.H1_DR.ACCOUNT AND H1_DR.H1_DR.AREA_UNIT AND H1_DR.H1_DR.CURRENCY
AND H1_DR.H1_DR.POSTING_DATE AND H1_DR.H1_DR.MAIN_ACCOUNT IN H1_DR TO MULTIPLE
H1_CR.H1_CR.ACCOUNT AND H1_CR.H1_CR.AREA_UNIT AND H1_CR.H1_CR.CURRENCY
AND H1_CR.H1_CR.POSTING_DATE AND H1_CR.H1_CR.MAIN_ACCOUNT IN H1_CR TAG J1 AS J1
END
TABLE FILE H1_DR
SUM
'H1_DR.H1_DR.ENTRY_AMOUNT' AS 'DR AMT'
'J1.H1_CR.ENTRY_AMOUNT' AS 'CR AMT'
BY 'H1_DR.H1_DR.AREA_UNIT'
BY 'H1_DR.H1_DR.ACCOUNT'
BY 'H1_DR.H1_DR.MAIN_ACCOUNT'
BY 'H1_DR.H1_DR.CURRENCY'
BY 'H1_DR.H1_DR.POSTING_DATE'
HEADING
""
FOOTING
""
WHERE RECORDLIMIT EQ 100
ON TABLE SET PAGE-NUM OFF
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
UNITS=IN,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
$
TYPE=TITLE,
STYLE=BOLD,
$
TYPE=TABHEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=TABFOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=HEADING,
SIZE=12,
STYLE=BOLD,
$
TYPE=FOOTING,
SIZE=12,
STYLE=BOLD,
$
TYPE=SUBHEAD,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBFOOT,
SIZE=10,
STYLE=BOLD,
$
TYPE=SUBTOTAL,
BACKCOLOR=RGB(210 210 210),
$
TYPE=ACROSSVALUE,
SIZE=9,
$
TYPE=ACROSSTITLE,
STYLE=BOLD,
$
TYPE=GRANDTOTAL,
BACKCOLOR=RGB(210 210 210),
STYLE=BOLD,
$
ENDSTYLE
END

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

The error is not "the 'TO' column is not retrieved", but "VALUE FOR JOIN 'TO' FIELD OUT OF SEQUENCE" because your ALPHA hold files are not in the sequence that the JOIN expects. Try sorting the two hold files using the fields that are in the JOIN (BY H1_DR.H1_DR.ACCOUNT BY H1_DR.H1_DR.AREA_UNIT...).

Try sort the details by fields to be used by join fields

quote:

TABLE FILE ORD_DRCRLD
PRINT
'ORD_DRCRLD.ORD_DRCRLD.DR_CR_FLAG'
'ORD_DRCRLD.ORD_DRCRLD.ENTRY_AMOUNT'
BY 'ORD_DRCRLD.ORD_DRCRLD.AREA_UNIT'
BY 'ORD_DRCRLD.ORD_DRCRLD.CURRENCY'
BY 'ORD_DRCRLD.ORD_DRCRLD.MAIN_ACCOUNT'
BY 'ORD_DRCRLD.ORD_DRCRLD.ACCOUNT'
BY 'ORD_DRCRLD.ORD_DRCRLD.POSTING_DATE'

and do the same for CRs

Or you can try to do a Match.

You still have to sort 'em.

quote:

for DR i have mt as 10000.00 and CR i have amt as 2000.00 in 2 rows of the same table..i need to have that displayed in WF as single row under headings CR and DR...

If that is what you need, why not just define different "buckets" for your data and avoid all that holding/joining stuff?

Something like:

DEFINE FILE ORD_DRCRLD
DR_ENTRY_AMOUNT/D12.2 MISSING ON = IF DR_CR_FLAG EQ 'DR' THEN ENTRY_AMOUNT ELSE MISSING;
CR_ENTRY_AMOUNT/D12.2 MISSING ON = IF DR_CR_FLAG EQ 'CR' THEN ENTRY_AMOUNT ELSE MISSING;
END
-*
TABLE FILE ORD_DRCRLD
SUM
      DR_ENTRY_AMOUNT
      CR_ENTRY_AMOUNT
BY AREA_UNIT
BY ACCOUNT
BY MAIN_ACCOUNT
BY CURRENCY
BY POSTING_DATE
WHERE ( WSS_GDP_SITE EQ 'PRO' )
WHERE ( ACCOUNTING_TYPE EQ 'G' )
WHERE ( POSTING_DATE FROM DT(&FROM_DATE 00:00:00)) TO ( DT(&TO_DATE 00:00:00))
END

The code not only looks much cleaner but it might perform more efficiently as well.

Hope that helps,

- Neftali.

quote:

it might perform more efficiently

Please take my code as a simplified general guide of what may do to achieve what you want. If your data source is a SQL-based DBMS you will have to fine tune the code in order to take advantage of database aggregation and filtering before attempting to use data buckets; as they are not known to your database, the code as it is will force the SUM operation to be done in WF and there will be a penalty there.

The performance gain you would notice immediately with the sample code I provided comes as a result of avoiding an extra round-trip to the database as well as the (hopefully) unnecessary sorting/joining of the intermediate HOLD files but you can and certainly should do much more in your code to make that operation more efficient so please take this for what it is, a guide to illustrate an idea.

- Neftali.

Neftali, I will certainly take your advice SUM whenever I can.

Thank you.

Hua

Thank You Neftali..Your code works perfectly fine...And is much cleaner .we use Oracle 10G as DB...Since there is no idexing at teh Table level which has more than >50Million records, it is bit slow...

Once again Thank you very mcuh for the Help...

Abraham

Wfdev Abraham , I'm glad to hear that the idea is working for you!

Indexes wouldn't help much in this case unless the percentage of records with WSS_GDP_SITE = 'PRO' and ACCOUNTING_TYPE = 'G' is relatively small when compared to the total number of records in your table.

I hope that your table is at least partitioned by POSTING_DATE so Oracle can eliminate (prune) unnecessary segments when performing the query.

Even if that is not the case right now, would you have the flexibility in your environment to create views? It would be much, much better if you can somehow translate the decoding of DR_ENTRY_AMOUNT and CR_ENTRY_AMOUNT as "virtual" fields within a database view. That way, the whole SUM operation would be handled entirely by Oracle and not by WebFOCUS and I can almost guarantee that you'll notice a significant gain in performance. For this approach to work though you'll need to create a masterfile on the "new" Oracle view and use that in your report instead of the original masterfile you had.

Of course, there is always SQL passthru as a last resort but if you can keep it "simple" with only masterfiles it might simplify maintenance in the future especially if other members of your team are more familiar with WebFOCUS than they are with SQL.

- Neftali.