Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] reading column value in amp variable

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] reading column value in amp variable
 Login/Join
 
pooja
Member
posted
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
 
Posts: 8 | Registered: March 11, 2015Report This Post
njsden
Virtuoso
posted Hide Post
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.
 
Posts: 1533 | Registered: August 12, 2005Report This Post
pooja
Member
posted Hide Post
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
 
Posts: 8 | Registered: March 11, 2015Report This Post
Danny-SRL
Virtuoso
posted Hide Post
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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Pravin.Singh
Gold member
posted Hide Post
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
 
Posts: 51 | Location: WebFOCUS 7.7.03 | Registered: June 19, 2014Report This Post
pooja
Member
posted Hide Post
HI Praveen,

The link which you mentioned helped me a lot. Thanks


WebFOCUS 8
Windows, All Outputs
 
Posts: 8 | Registered: March 11, 2015Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] reading column value in amp variable

Copyright © 1996-2020 Information Builders