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, RaghuThis message has been edited. Last edited by: Kerry,
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 ?
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 23, 2010, 02:01 AM
raghuram
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
WebFOCUS 7.6.7 Windows Excel, PDF, HTML
April 23, 2010, 02:11 AM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 23, 2010, 02:28 AM
raghuram
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
WebFOCUS 7.6.7 Windows Excel, PDF, HTML
April 23, 2010, 02:36 AM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 23, 2010, 02:44 AM
raghuram
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
WebFOCUS 7.6.7 Windows Excel, PDF, HTML
April 23, 2010, 03:09 AM
Waz
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.
Waz...
Prod:
WebFOCUS 7.6.10/8.1.04
Upgrade:
WebFOCUS 8.2.07
OS:
Linux
Outputs:
HTML, PDF, Excel, PPT
In Focus since 1984
Pity the lost knowledge of an old programmer!
April 23, 2010, 03:53 AM
raghuram
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
WebFOCUS 7.6.7 Windows Excel, PDF, HTML
April 23, 2010, 06:39 AM
Tony A
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
In FOCUS since 1986
WebFOCUS Server 8.2.01M, thru 8.2.07 on Windows Svr 2008 R2
WebFOCUS App Studio 8.2.06 standalone on Windows 10
April 23, 2010, 08:50 AM
raghuram
Hi Tony, By mistake i missed some code . Sorry for that.