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. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at myibi@ibi.com and provide your corporate email address, company, and name.


Connect to myibi
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     Focus elements in SQL query

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Focus elements in SQL query
 Login/Join
 
Gold member
posted
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, 2004Report This Post
<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
 
Report This Post
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, 2004Report This 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
 
Report This Post
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, 2004Report This 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>,
 
Report This Post
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, 2004Report This Post
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, 2003Report 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     Focus elements in SQL query

Copyright © 1996-2020 Information Builders