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
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