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

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.

MartinY has eagle´s eyes!

something happened when cut and pasted it should be ( not [


Have corrected 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

 

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

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

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

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.

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

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.

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

whats the error you get when trying to run it in SQL wizard?

If it runs in toad it should run in WF.

and it looks like you have multiple queries. Each one needs to be its own request and held in its own hold file.

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

The magic of the ' - '