Is it possible to use focus elements in an sql query?
I have a complicated query that works in sql joining several oracle tables and retreiving the correct fields I need.
I also have a tabt file that has the unique ids of all the people we want to pull. Now I could just join them after I extract the information in the query but that would bring back around 200 pieces of information for several hundred thousand ids, then it would limit them.
I'd like to limit them in the sql query.
Is is possible to do something like
FILEDEF user_ids disk /location/to/file/user_ids.mas SQL SELECT * FROM some_oracle_table where criteria = 'met' AND USERID IN (TABLE FILE user_ids PRINT EMPLID) END I have no problems reading in the text file, I just need to know if webfocus has a way to pass these through to sql (maybe a variable?)
This is obvious psuedo-code but I think the idea is clear enough.This message has been edited. Last edited by: <Mabel>,
August 31, 2004, 05:30 PM
<Vipul>
Yes it can for example you can do a check on a dm var in your where clause like this:
and cororg.cororgky = &CUSTKEY
and also do an in on a hold file as
WHERE field1 IN FILE HOLDFILE1;
Vipul
August 31, 2004, 05:46 PM
George Brown
When I try the below code I get the error
"(FOC14069) SYNTAX ERROR ON LINE 12 AT 'ID_HOLD1' -- Subquery or Value List expected"
Did I mess something up? Acording to the above post this should be possible.
Any help is greatly appreciated.
FILEDEF smartcall_emplids DISK /opt/ibi/apps/smartcall/smartcall_emplids.tab TABLE FILE smartcall_emplids PRINT EMPLID ON TABLE HOLD AS ID_HOLD1 END
SQL SELECT * FROM cr_AV_RCG_DES A, cr_AV_ACCOUNT_TBL B, cr_av_gift_dtl C WHERE A.INSTITUTION = B.INSTITUTION AND A.DESIGNATION = B.DESIGNATION AND A.INSTITUTION = C.INSTITUTION AND A.SESSION_NO = C.SESSION_NO AND A.EMPLID = C.EMPLID AND A.GIFT_NO = C.GIFT_NO AND A.STATUS = 'A' AND A.AV_SESS_TYPE IN ('G', 'AG') AND A.RCG_DES_AMT > 0 AND A.EMPLID IN ID_HOLD1 ORDER BY A.EMPLID, A.GIFT_DT DESC; TABLE ON TABLE HOLD AS GFTHOLD ENDThis message has been edited. Last edited by: <Mabel>,
August 31, 2004, 06:39 PM
<Vipul>
It is like this (see the word FILE)
WHERE EXTERNALSYSCD IN FILE SMRYFLTR;
But I do not think you can mix this in embedded sql statement. For sql statement use something like this
select * from xyz where abc in (select abc from wyz)
Vipul
August 31, 2004, 07:04 PM
George Brown
I tried the above code both with and without the word "FILE".
The whole point is that I need to do this within an sql statement. If this is not possible I'll find another way but if I can just get the sql statement to read from the list of values in the file I'd be set.
Alternatively I'll settle for the correct syntax for writing a hold file out to an Oracle Database (I've tried on table hold as blah with setting the type to sqlora, it tells me I don't have rights to write to the database but I don't have syntax on how to put that username and password in there).
Thanks for your responses.
August 31, 2004, 07:18 PM
<Vipul>
Hey George,
Try this in sql plus:
SELECT * FROM cr_AV_RCG_DES A, cr_AV_ACCOUNT_TBL B, cr_av_gift_dtl C WHERE A.INSTITUTION = B.INSTITUTION AND A.DESIGNATION = B.DESIGNATION AND A.INSTITUTION = C.INSTITUTION AND A.SESSION_NO = C.SESSION_NO AND A.EMPLID = C.EMPLID AND A.GIFT_NO = C.GIFT_NO AND A.STATUS = 'A' AND A.AV_SESS_TYPE IN ('G', 'AG') AND A.RCG_DES_AMT > 0 AND A.EMPLID IN (select distinct emplid from smartcall_emplids)
Please check the syntax in oracle documentation.
Let me know...we will go from there.
VipulThis message has been edited. Last edited by: <Mabel>,
September 01, 2004, 04:25 AM
TerryW
Ok, give this a try, and remember Focus File Names are very limited in length:
FILEDEF SCEMPS DISK /opt/ibi/apps/smartcall/smartcall_emplids.tab TABLE FILE SCEMPS PRINT EMPLID ON TABLE HOLD AS ID_HOLD1 END
SQL SELECT * FROM cr_AV_RCG_DES A, cr_AV_ACCOUNT_TBL B, cr_av_gift_dtl C WHERE A.INSTITUTION = B.INSTITUTION AND A.DESIGNATION = B.DESIGNATION AND A.INSTITUTION = C.INSTITUTION AND A.SESSION_NO = C.SESSION_NO AND A.EMPLID = C.EMPLID AND A.GIFT_NO = C.GIFT_NO AND A.STATUS = 'A' AND A.AV_SESS_TYPE IN ('G', 'AG') AND A.RCG_DES_AMT > 0 AND A.EMPLID IN ID_HOLD1 ORDER BY A.EMPLID, A.GIFT_DT DESC; TABLE ON TABLE HOLD AS GFTHOLD END
Now, you're going to have to have a master file for the SCEMPS file. If not, and the smartcall_emplids.tab file contains ONLY the employee ID's, 1 per line, then use it directly in the query --> WHERE A.EMPLID IN SCEMPS
Hope this helps
September 01, 2004, 02:26 PM
EricH
George,
You're almost there. The trick is the SQL has to be syntactically correct. This means that the EMPLIDs have to go into a file which
a) Can be -INCLUDE'd in your code b) Will create syntactically correct SQL
This is a 2 step process. The syntax below may not be 100% correct, but it will go somewhat like this:
FILEDEF smartcall_emplids etc etc
-* NOTE that HOLD file ends in .fex FILEDEF ID_HOLD1 DISK id_hold1.fex
DEFINE FILE SMARTCALL_EMPLIDS CNTR/I5 = LAST CNTR + 1 ; PRINT_LINE/A30 = IF (CNTR EQ 1) THEN EMPLID ELSE ', ' | EMPLID ; END TABLE FILE SMARTCALL_EMPLIDS PRINT PRINT_LINE BY CNTR NOPRINT ON TABLE SET HOLDLIST PRINTONLY ON TABLE SAVE AS ID_HOLD1 END -RUN SQL SELECT