Focal Point
Focus elements in SQL query

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

August 31, 2004, 04:56 PM
George Brown
Focus elements in SQL query
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
END

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

Vipul

This 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

etc,
etc,
etc...

AND A.EMPLID IN (
-INCLUDE ID_HOLD1
) ;

TABLE
etc
END


EricH