Hi All,
I am joining hold file with SQL query.
But I am not able to get the data ou to it.
Please find the below code
SQL
SELECT MIN(RD.COMGRP_GROUP_CODE) AS COMGRP_GROUP_CODE,
MIN(RD.COMGRP_DESC) AS COMGRP_DESC,
MIN(RD.COMMODITY_CODE) AS COMMODITY_CODE,
MIN(RD.COMMODITY_NAME) AS COMMODITY_NAME,
MIN(RD.REGION_CODE) AS REGION_CODE,
MIN(RD.REGION_NAME) AS REGION_NAME,
MIN(RD.PRODUCTLINE_CODE) AS PRODUCTLINE_CODE,
MIN(RD.PRODUCTLINE_DESC) AS PRODUCTLINE_DESC,
RD.PLANT_CODE,
MIN(RD.PLANT_NAME) AS PLANT_NAME,
RD.MST_CODE,
MIN(RD.SUPPLIER_NAME) AS SUPPLIER_NAME,
RD.RLDC_PART_NUMBER,
MIN(RD.PART_DESC) AS PART_DESC,
-* SUM(CEH1.EXCHANGE_RATE * RD.SPND * (1/CEH2.EXCHANGE_RATE)) AS SPND,
SUM(RD.QTY) AS QTY
FROM RECEIPTS_DTL RD
,
CURRENCY_EXCHANGE_HOLD CEH1,
CURRENCY_EXCHANGE_HOLD CEH2
WHERE
RD.YR = CEH1.YEAR
AND
CEH1.CURRENCY_CODE = '&SELECTED_CURRENCY'
AND RD.YR = CEH2.YEAR
AND RD.RLDC_CUR_CODE = CEH2.CURRENCY_CODE
GROUP BY RD.MST_CODE,
RD.RLDC_PART_NUMBER,
RD.PLANT_CODE;
TABLE ON TABLE HOLD AS HLD_PSR
END
Hold file :CURRENCY_EXCHANGE_HOLD
Can you please advice me how to resolve this issue.

Thanks,
Raghu

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

I am getting following error.
0 NUMBER OF RECORDS IN TABLE= 248 LINES= 248
1
0 NUMBER OF RECORDS IN TABLE= 8 LINES= 8
0
1
0 NUMBER OF RECORDS IN TABLE= 248 LINES= 248
0
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Invalid root page
Thanks,
Raghu

Where is the source of RECEIPTS_DTL ? is there a master file.

I guess there is, otherwise it would have errored.

Is there a reason to join to a HOLD file with SQL ? Wouldn't FOCUS be better ?

Hi,
RECIPTS_DTL is database table.
CURRENCY_EXCHANGE_HOLD is hold file .
We need to dispaly some fields by joinig by above tables.
For your information it is working in version 767, recently we upgraded to 7611 after that the above query is not working.


Thanks,
Raghu

Is your Hold file a FOCUS file or a flat file ?

It may work if the hold file is a FOCUS file with the appropriate columns indexed, or at least the hold file should be in the right order.

The HOLD File is Coming from the other FOCUS FILE.
There we are putting as below.
TABLE FILE EXCH_DETAILS
PRINT *
BY CURRENCY_CODE NOPRINT
ON TABLE HOLD AS CURRENCY_EXCHANGE_HOLD
END

I am not aware how to indexing a column.
Can you please let me know how to indexing a column.

Thanks,
Raghu

ON TABLE HOLD AS {file name} FORMAT FOCUS INDEX {column name column name}

Indexed column names must be 12 characters or less.

Format XFOCUS does not have this limit, but I think you need to be licensed for it.

quote:

file name

Hi Waz,
Thanks for your Quick reply.
Since the hold file is using in some other Reports as well ,If i change any thing it will impact the other reports.
So can we make indexing in the below code.
SELECT MIN(RD.COMGRP_GROUP_CODE) AS COMGRP_GROUP_CODE,
MIN(RD.COMGRP_DESC) AS COMGRP_DESC,
MIN(RD.COMMODITY_CODE) AS COMMODITY_CODE,
MIN(RD.COMMODITY_NAME) AS COMMODITY_NAME,
MIN(RD.REGION_CODE) AS REGION_CODE,
MIN(RD.REGION_NAME) AS REGION_NAME,
MIN(RD.PRODUCTLINE_CODE) AS PRODUCTLINE_CODE,
MIN(RD.PRODUCTLINE_DESC) AS PRODUCTLINE_DESC,
RD.PLANT_CODE,
MIN(RD.PLANT_NAME) AS PLANT_NAME,
RD.MST_CODE,
MIN(RD.SUPPLIER_NAME) AS SUPPLIER_NAME,
RD.RLDC_PART_NUMBER,
MIN(RD.PART_DESC) AS PART_DESC,
-* SUM(CEH1.EXCHANGE_RATE * RD.SPND * (1/CEH2.EXCHANGE_RATE)) AS SPND,
SUM(RD.QTY) AS QTY
FROM RECEIPTS_DTL RD
,
CURRENCY_EXCHANGE_HOLD CEH1,
CURRENCY_EXCHANGE_HOLD CEH2
WHERE
RD.YR = CEH1.YEAR
AND
CEH1.CURRENCY_CODE = '&SELECTED_CURRENCY'
AND RD.YR = CEH2.YEAR
AND RD.RLDC_CUR_CODE = CEH2.CURRENCY_CODE
GROUP BY RD.MST_CODE,
RD.RLDC_PART_NUMBER,
RD.PLANT_CODE;
TABLE ON TABLE HOLD AS HLD_PSR
END

Thanks,
Raghu

No I don't think you can.

But you could just add an extra TABLE FILE HOLD before the SQL and create a second HOLD file that is indexed.

Hi Waz,
I tried as below but still i am getting error.
Please bare with my syntax if i written wrong since I am very new to Webfocus.
TABLE FILE CURRENCY_EXCHANGE_HOLD
PRINT CURRENCY_CODE
YEAR
EXCHANGE_RATE
ON TABLE HOLD AS CURR_EXCH FORMAT FOCUS INDEX CURRENCY_CODE YEAR EXCHANGE_RATE
-SQL
SELECT MIN(RD.COMGRP_GROUP_CODE) AS COMGRP_GROUP_CODE,
MIN(RD.COMGRP_DESC) AS COMGRP_DESC,
MIN(RD.COMMODITY_CODE) AS COMMODITY_CODE,
MIN(RD.COMMODITY_NAME) AS COMMODITY_NAME,
MIN(RD.REGION_CODE) AS REGION_CODE,
MIN(RD.REGION_NAME) AS REGION_NAME,
MIN(RD.PRODUCTLINE_CODE) AS PRODUCTLINE_CODE,
MIN(RD.PRODUCTLINE_DESC) AS PRODUCTLINE_DESC,
RD.PLANT_CODE,
MIN(RD.PLANT_NAME) AS PLANT_NAME,
RD.MST_CODE,
MIN(RD.SUPPLIER_NAME) AS SUPPLIER_NAME,
RD.RLDC_PART_NUMBER,
MIN(RD.PART_DESC) AS PART_DESC,
-* SUM(CEH1.EXCHANGE_RATE * RD.SPND * (1/CEH2.EXCHANGE_RATE)) AS SPND,
SUM(RD.QTY) AS QTY
FROM RECEIPTS_DTL RD,
CURR_EXCH CEH1,
CURR_EXCH CEH2
WHERE
RD.YR = CEH1.YEAR
AND
CEH1.CURRENCY_CODE = 'USD'
AND RD.YR = CEH2.YEAR
AND RD.RLDC_CUR_CODE = CEH2.CURRENCY_CODE
GROUP BY RD.MST_CODE,
RD.RLDC_PART_NUMBER,
RD.PLANT_CODE;
TABLE ON TABLE HOLD AS HLD_PSR
END
TABLE FILE HLD_PSR
PRINT *
END
-EXIT

Error Info :

0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 1 REJECTED= 0
SEGMENTS: INPUT = 1 UPDATED = 0 DELETED = 0
0 NUMBER OF RECORDS IN TABLE= 248 LINES= 248
0 ERROR AT OR NEAR LINE 297 IN PROCEDURE vin_psr_receipts_r
(FOC002) A WORD IS NOT RECOGNIZED: SELECT
(FOC009) INCOMPLETE REQUEST STATEMENT
BYPASSING TO END OF COMMAND
(FOC205) THE DESCRIPTION CANNOT BE FOUND FOR FILE NAMED: HLD_PSR
BYPASSING TO END OF COMMAND
-HTMLFORM END

You are experiencing one of the problems of trying to learn WebFOCUS on the fly. What you are trying to do in your last post shows that you do not understand the use of SQL passthru, also what HOLDing data achieves and how HOLD files can be used.

Your best way forward would be to read the manual section relating to HOLD files and how they can be used. It is not an area that I would suggest you attempt to learn via the forum (not that you wouldn't be able to, just that it would be more expedient to read the manual).

An even better way forward would be to insist to your line management that a basic training course is paramount to enable you to begin to understand WebFOCUS.

T

Hi Tony,
By mistake i missed some code .
Sorry for that.

Thanks,
Raghu