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

Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004

<Vipul>

posted

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

George Brown

Gold member

posted

Hide Post

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

Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004

Ignored post by George Brown posted

Show Post

<Vipul>

posted

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

George Brown

Gold member

posted

Hide Post

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.

Posts: 77 | Location: Chicago, IL | Registered: May 06, 2004

Ignored post by George Brown posted

Show Post

<Vipul>

posted

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

TerryW

Silver Member

posted

Hide Post

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

Posts: 40 | Registered: March 10, 2004

Ignored post by TerryW posted

Show Post

EricH

Platinum Member

posted

Hide Post

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

Posts: 164 | Registered: March 26, 2003

Ignored post by EricH posted

Show Post

Please Wait. Your request is being processed...

Read-Only Topic

Focal Point

Focal Point Forums

WebFOCUS/FOCUS Forum on Focal Point

Focus elements in SQL query

Focal Point | Privacy Statement | Terms of Service