Focal Point
[SOLVED] reading column value in amp variable

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3837002576

March 19, 2015, 08:34 AM
pooja
[SOLVED] reading column value in amp variable
Hi All,
I am new to web focus and i am working on a web focus project. I need help from you guys to get it done below task.
I have a focus mart called item and it has three column as below.I have to read three columns in three variable seprated by comma and '' so that i can use in the query in the IN clause.

Ex: TABLE FILE ITEM
PRINT FROM_DEPT FROM_CL FROM_ITEM
END

output : &dept &dept = '101','201','301','401'( all the column values of FROM_DEPT)
&cl &cl = '10','20','30','40'
the above varible i need to use in the below query

ENGINE SQLDBC SET DEFAULT_CONNECTION INVOBIS
SQL SQLDBC PREPARE SQL_HOLD_S FOR

SELECT * FROM PROD_V.MDSE_ITEM_KEY_LKUP
WHERE TO_CHAR( MDSE_DEPT_REF_I ) IN ( &dept &dept ) and MDSE_class_REF_I IN ( &cl &cl) ;
END

Please help me on this.

This message has been edited. Last edited by: <Kathryn Henning>,


WebFOCUS 8
Windows, All Outputs
March 19, 2015, 07:16 PM
njsden
Assuming that your query on the ITEM table results in 1 single row and that ITEM.FROM_DEPT = '101','201','301','401' and ITEM.FROM_CL = '10','20','30','40', the following should get you going:

TABLE FILE ITEM
PRINT FROM_DEPT FROM_CL FROM_ITEM
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HPARAM
END
-RUN

-READFILE HPARAM

ENGINE SQLDBC SET DEFAULT_CONNECTION INVOBIS
SQL SQLDBC PREPARE SQL_HOLD_S FOR

SELECT * FROM PROD_V.MDSE_ITEM_KEY_LKUP
WHERE TO_CHAR( MDSE_DEPT_REF_I ) IN ( &FROM_DEPT ) and MDSE_class_REF_I IN ( &FROM_CL) ;
END




Prod/Dev: WF Server 8008/Win 2008 - WF Client 8008/Win 2008 - Dev. Studio: 8008/Windows 7 - DBMS: Oracle 11g Rel 2
Test: Dev. Studio 8008 /Windows 7 (Local) Output:HTML, EXL2K.
March 23, 2015, 02:48 AM
pooja
HI, Thanks for the note...when i try this i ma getting error saying below.

FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 0165á
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 0167á
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 0239á
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 0252 á
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 100
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 100
(FOC209) THE DATA VALUE EXCEEDS ITS LENGTH SPECIFICATION: 100
0 NUMBER OF RECORDS IN TABLE= 422 LINES= 422
0 ERROR AT OR NEAR LINE 10 IN PROCEDURE merge.fex
(FOC303) CONTROL LINE NOT RECOGNIZED IN FOCEXEC: -READFILE HPARAM


the thing is i need to read a column in to amp variable and the column look like below

FROM_DEPT FROM_CL
101 10
201 20
301 30
401 40

PLEASE HELP ON THIS


WebFOCUS 8
Windows, All Outputs
March 23, 2015, 03:10 AM
Danny-SRL
quote:
TABLE FILE ITEM
PRINT FROM_DEPT FROM_CL FROM_ITEM
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HPARAM
END
-RUN

So your values are in subsequent records. Also, I don't see you are using the values in FROM_ITEM.
Hence my advice:
TABLE FILE ITEM
PRINT FROM_DEPT  FROM_CL
ON TABLE SET ASNAMES ON
ON TABLE HOLD AS HPARAM
END
-RUN  
-SET &MAX=&LINES - 1;
-READFILE HPARAM
-SET &DEPT='''' | &FROM_DEPT || '''';
-SET &CL='''' | &FROM_CL || '''';
-REPEAT #GETVALS FOR &I FROM 1 TO &MAX;
-READFILE HPARAM
-SET &DEPT=&DEPT || ',''' || &FROM_DEPT || '''';
-SET &CL=&CL || ',''' || &FROM_CL || '''';
-#GETVALS

The rest is the same.


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

March 23, 2015, 08:26 AM
Pravin.Singh
Hi Pooja,

If you need more details on how to use looping in WebFOCUS for reading the value fron a column and appending into a variable, follow the below below link:
http://pravinsinghwebfocus.blo...cus-code-in-our.html

If you need more information, please let me know.

Thanks,
Pravin Singh


WebFOCUS 7703
Windows, All Outputs
pravinsinghwebfocus.blogspot.com
March 31, 2015, 07:42 AM
pooja
HI Praveen,

The link which you mentioned helped me a lot. Thanks


WebFOCUS 8
Windows, All Outputs