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.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED]nested subquery

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED]nested subquery
 Login/Join
 
Platinum Member
posted
I am looking for suggestions to convert this nested subquery to WF code. It generates a missing parentheses message in the SQL passthru wizard so that is out


I believe I am going to have to use hold files but am looking for some assistance as the SQL is above my level

Thanks

select 5 , 'Balance of New FY Pledges' row_name, 0, sum(greatest(0, agrpdes_amt -
nvl((select nvl(sum(agrgdes_amt),0) from agrgdes, agbgift
where agrgdes_pidm = agbpldg_pidm and agrgdes_pidm = agbgift_pidm and agrgdes_gift_no = agbgift_gift_no
and agrgdes_pledge_no = agrpdes_pledge_no
and agrgdes_campaign = agrpdes_campaign
and agrgdes_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0) -
nvl((select nvl(sum(agrgmlt_3pp_amt),0)
from agrgmlt, agbgift
where agrgmlt_3pp_pledge_no = agrpdes_pledge_no and agrgmlt_pidm = agbgift_pidm and agrgmlt_gift_no=agbgift_gift_no
and agrgmlt_campaign = agrpdes_campaign
and agrgmlt_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <='31-JAN-2015' ),0) -
nvl((select nvl(sum(agrgmmo_3pp_amt),0)
from agrgmmo, agbgift
where agrgmmo_3pp_pledge_no = agrpdes_pledge_no and agrgmmo_pidm = agbgift_pidm and agrgmmo_gift_no = agbgift_gift_no
and agrgmmo_campaign = agrpdes_campaign
and agrgmmo_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0)

))
from AGBPLDG, AGRPDES WHERE (AGBPLDG_PIDM = AGRPDES_PIDM) AND (AGBPLDG_PLEDGE_NO = AGRPDES_PLEDGE_NO) AND
AGBPLDG_PLDG_CODE IN (SELECT ATVPLDG_CODE FROM ATVPLDG WHERE NVL(ATVPLDG_PLANNED_GIFT_IND,'0') <> '1' AND ATVPLDG_CODE NOT IN ('PO'))
AND AGBPLDG_PSTA_CODE IN ('A','P') AND AGBPLDG_FISC_CODE = '2015' AND TRUNC(AGBPLDG_PLEDGE_DATE ) <= '31-JAN-2015' and agrpdes_desg not in [select adbdesg_desg from adbdesg where ADBDESG_DSTP_CODE = 'NG')

Oh and the SQL will work in TOAD and Oracle Developer , just not in WebFOCUS

This message has been edited. Last edited by: <Emily McAllister>,


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Virtuoso
posted Hide Post
Look at
  and agrpdes_desg not in [select adbdesg_desg from adbdesg where ADBDESG_DSTP_CODE = 'NG')


You have a [ instead of ( before the select.


WF versions : Prod 8.2.04M gen 33, Dev 8.2.04M gen 33, OS : Windows, DB : MSSQL, Outputs : HTML, Excel, PDF
In Focus since 2007
 
Posts: 2409 | Location: Montreal Area, Qc, CA | Registered: September 25, 2013Report This Post
Guru
posted Hide Post
Good One

MartinY has eagle´s eyes!


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
something happened when cut and pasted it should be ( not [


Have corrected post


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Guru
posted Hide Post
My contribution is:
 

SET ASNAMES=ON
SET EMPTYCELLS = OFF
SET NODATA = 0

SET HOLDLIST=PRINTONLY


-SET &ORA_CON = 'YOUR_DESIRED_CONNECTION_LISTED_AT_TNSNAMES.ORA';

-****************************************
-* SET SQLORA ENGINE TO RUN SQL QUERIES
-****************************************

ENGINE SQLORA SET DEFAULT_CONNECTION &ORA_CON
SQL SQLORA PREPARE QUERY1 FOR
select 5 , 'Balance of New FY Pledges' row_name, 0, sum(greatest(0, agrpdes_amt - 
nvl((select nvl(sum(agrgdes_amt),0) from agrgdes, agbgift
 where agrgdes_pidm = agbpldg_pidm and agrgdes_pidm = agbgift_pidm and agrgdes_gift_no = agbgift_gift_no 
 and agrgdes_pledge_no = agrpdes_pledge_no
 and agrgdes_campaign = agrpdes_campaign
 and agrgdes_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0) -
nvl((select nvl(sum(agrgmlt_3pp_amt),0)
 from agrgmlt, agbgift
 where agrgmlt_3pp_pledge_no = agrpdes_pledge_no and agrgmlt_pidm = agbgift_pidm and agrgmlt_gift_no=agbgift_gift_no
 and agrgmlt_campaign = agrpdes_campaign
 and agrgmlt_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <='31-JAN-2015' ),0) -
 nvl((select nvl(sum(agrgmmo_3pp_amt),0)
 from agrgmmo, agbgift
 where agrgmmo_3pp_pledge_no = agrpdes_pledge_no and agrgmmo_pidm = agbgift_pidm and agrgmmo_gift_no = agbgift_gift_no
 and agrgmmo_campaign = agrpdes_campaign
 and agrgmmo_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0) 

))
from AGBPLDG, AGRPDES WHERE (AGBPLDG_PIDM = AGRPDES_PIDM) AND (AGBPLDG_PLEDGE_NO = AGRPDES_PLEDGE_NO) AND 
 AGBPLDG_PLDG_CODE IN (SELECT ATVPLDG_CODE FROM ATVPLDG WHERE NVL(ATVPLDG_PLANNED_GIFT_IND,'0') <> '1' AND ATVPLDG_CODE NOT IN ('PO')) 
AND AGBPLDG_PSTA_CODE IN ('A','P') AND AGBPLDG_FISC_CODE = '2015' AND TRUNC(AGBPLDG_PLEDGE_DATE ) <= '31-JAN-2015' and agrpdes_desg not in [select adbdesg_desg from adbdesg where ADBDESG_DSTP_CODE = 'NG')
;

END

-RUN

TABLE FILE QUERY1
ENJOY
END

 


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
Master
posted Hide Post
quote:
...suggestions to convert this nested subquery to WF code...

This thread has an example of a nested subquery using the WebFOCUS reporting language:
http://forums.informationbuild...257043876
 
Posts: 822 | Registered: April 23, 2003Report This Post
Platinum Member
posted Hide Post
quote:
SET ASNAMES=ON SET EMPTYCELLS = OFF SET NODATA = 0 SET HOLDLIST=PRINTONLY -SET &ORA_CON = 'YOUR_DESIRED_CONNECTION_LISTED_AT_TNSNAMES.ORA'; -**************************************** -* SET SQLORA ENGINE TO RUN SQL QUERIES -**************************************** ENGINE SQLORA SET DEFAULT_CONNECTION &ORA_CON SQL SQLORA PREPARE QUERY1 FOR select 5 , 'Balance of New FY Pledges' row_name, 0, sum(greatest(0, agrpdes_amt - nvl((select nvl(sum(agrgdes_amt),0) from agrgdes, agbgift where agrgdes_pidm = agbpldg_pidm and agrgdes_pidm = agbgift_pidm and agrgdes_gift_no = agbgift_gift_no and agrgdes_pledge_no = agrpdes_pledge_no and agrgdes_campaign = agrpdes_campaign and agrgdes_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0) - nvl[[select nvl[sum[agrgmlt_3pp_amt),0) from agrgmlt, agbgift where agrgmlt_3pp_pledge_no = agrpdes_pledge_no and agrgmlt_pidm = agbgift_pidm and agrgmlt_gift_no=agbgift_gift_no and agrgmlt_campaign = agrpdes_campaign and agrgmlt_desg = agrpdes_desg and TRUNC[agbgift_gift_date) <='31-JAN-2015' ),0) - nvl[[select nvl[sum[agrgmmo_3pp_amt),0) from agrgmmo, agbgift where agrgmmo_3pp_pledge_no = agrpdes_pledge_no and agrgmmo_pidm = agbgift_pidm and agrgmmo_gift_no = agbgift_gift_no and agrgmmo_campaign = agrpdes_campaign and agrgmmo_desg = agrpdes_desg and TRUNC[agbgift_gift_date) <= '31-JAN-2015' ),0) )) from AGBPLDG, AGRPDES WHERE [AGBPLDG_PIDM = AGRPDES_PIDM) AND [AGBPLDG_PLEDGE_NO = AGRPDES_PLEDGE_NO) AND AGBPLDG_PLDG_CODE IN [SELECT ATVPLDG_CODE FROM ATVPLDG WHERE NVL[ATVPLDG_PLANNED_GIFT_IND,'0') <> '1' AND ATVPLDG_CODE NOT IN ('PO')) AND AGBPLDG_PSTA_CODE IN ('A','P') AND AGBPLDG_FISC_CODE = '2015' AND TRUNC(AGBPLDG_PLEDGE_DATE ) <= '31-JAN-2015' and agrpdes_desg not in [select adbdesg_desg from adbdesg where ADBDESG_DSTP_CODE = 'NG') ; END -RUN TABLE FILE QUERY1 ENJOY END


THROWS ERROR MESSAGE

(FOC1400) SQLCODE IS 907 (HEX: 0000038B)
: ORA-00907: missing right parenthesis
SAME AS WITH sql WIZARD AND I corrrected [ TO (

bit thanks anyway for contributing to discussion


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Platinum Member
posted Hide Post
What i am really looking for is to break this into smaller components I believe as SQL wizrd is choking on all the nesting My preference would be to break


-* subselect 1
nvl((select nvl(sum(agrgdes_amt),0) from agrgdes, agbgift
where agrgdes_pidm = agbpldg_pidm and agrgdes_pidm = agbgift_pidm and agrgdes_gift_no = agbgift_gift_no
and agrgdes_pledge_no = agrpdes_pledge_no
and agrgdes_campaign = agrpdes_campaign
and agrgdes_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0) -

-*subselect 2
nvl((select nvl(sum(agrgmlt_3pp_amt),0)
from agrgmlt, agbgift
where agrgmlt_3pp_pledge_no = agrpdes_pledge_no and agrgmlt_pidm = agbgift_pidm and agrgmlt_gift_no=agbgift_gift_no
and agrgmlt_campaign = agrpdes_campaign
and agrgmlt_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <='31-JAN-2015' ),0) -

-* subselect 3
nvl((select nvl(sum(agrgmmo_3pp_amt),0)
from agrgmmo, agbgift
where agrgmmo_3pp_pledge_no = agrpdes_pledge_no and agrgmmo_pidm = agbgift_pidm and agrgmmo_gift_no = agbgift_gift_no
and agrgmmo_campaign = agrpdes_campaign
and agrgmmo_desg = agrpdes_desg and TRUNC(agbgift_gift_date) <= '31-JAN-2015' ),0)

into smaller pieces (HOLDS) preferably with WF code then join to parent


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Guru
posted Hide Post
Just fix your query and be happy!

I bet you can run this query from sql plus. When you are able to run it from sql plus you will be able to run it from a FEX.


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
it will run from TOAD but not in SQL wizard. Im not the owner of the SQL and don't have access to SQL PLUS but I would certainly be willing to entertain any suggestions re: the SQL code as I am no SQL expert


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Guru
posted Hide Post
How do you connect on Oracle then? Using TOAD you might provide a username and password.

Is this query working on TOAD?

sqlplus comes with Oracle Client.

Open a terminal/console and type sqlplus /? or sqlplus --help
You might be able to connect on oracle using the same user/passwd you use on TOAD.


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report This Post
Platinum Member
posted Hide Post
the query works in TOAD , however when I run this statement in SQL PLUS from within TOAD it does not

and I connect with a TNSNAMES file if that's any help

and yes I have to sign into TOAD


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Master
posted Hide Post
whats the error you get when trying to run it in SQL wizard?

If it runs in toad it should run in WF.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Master
posted Hide Post
and it looks like you have multiple queries. Each one needs to be its own request and held in its own hold file.


Eric Woerle
8.1.05M Gen 913- Reporting Server Unix
8.1.05 Client Unix
Oracle 11.2.0.2
 
Posts: 750 | Location: Warrenville, IL | Registered: January 08, 2013Report This Post
Platinum Member
posted Hide Post
Solved

changed

select 5 , 'Balance of New FY Pledges' row_name, 0, sum(greatest(0, agrpdes_amt -


to select 5 , 'Balance of New FY Pledges' row_name, 0, sum(greatest(0, agrpdes_amt - -

and it now works don't ask me to splain it Lucy

MY ETL person suggested it


809 DevStudio, MRE, Report Caster , Report Library
Output: Excel PDF, HTML
 
Posts: 171 | Registered: April 28, 2008Report This Post
Guru
posted Hide Post
The magic of the ' - '


WebFOCUS 8.1.05 / APP Studio
 
Posts: 272 | Location: Brazil | Registered: October 31, 2006Report 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     [SOLVED]nested subquery

Copyright © 1996-2020 Information Builders