Missing Right Parenthesis Oracle SQL

I am getting an error "Missing Right Parenthesis"  on this line.        ) C ON A.PLAN_ID=C.PLAN_ID AND A.SSN_ID=C.SSN_ID  

 I can't figure why - it looks like all of them are there.


____________________________________________________________________________________________


SELECT PLAN_N, PLAN_SHRT_NM, DVSN_NM, C.Plan_ID,
   SUM(Case when BAL>0 THEN Balance Else 0 end) as "Assets"
        FROM(
      SELECT A.PLAN_ID, PLAN_N, PLAN_SHRT_NM, SUM(END_ASET_BAL_A)as BAL
      FROM dbo.V_BAL_MO A      
         
          LEFT JOIN(
                SELECT
               (Sum(case when STAT_C IN ('A') AND PCNT > 1 and DEF_DOL = 0 then 1 else 0 end)
               +Sum(case when STAT_C IN('A') AND PCNT < 1 and DEF_DOL > 0
               then 1 else 0 end))/Sum(case when STAT_C IN('A')
               then 1 else 0 end) as RATE
                ,NVL(ROUND(AVG(CASE when STAT_C IN ('A')
                AND PCNT > 1 THEN PCNT end),2),0) as DRATE
          FROM(
             SELECT PlanID, NVL(DEF_A,0) as DEF_DOL, NVL(DEF_P,0) as PCNT, SSN_ID, STAT_C
             FROM dbo.V_PART
             WHERE row_exp_d=TO_DATE('01/01/9999','MM/DD/YYYY')
             AND PLAN_ID IN ('207')
             ) C ON A.PLAN_ID=C.PLAN_ID AND A.SSN_ID=C.SSN_ID  

) GROUP BY PLAN_N, PLAN_SHRT_NM, DVSN_NM
leezacAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Three left parens, two right parents.  Guessing ) GROUP BY should be )) GROUP BY
SELECT
  FROM (   -- left paren #1
      SELECT 
         LEFT JOIN ( -- left paren #2
            SELECT
                FROM ( -- left paren #3
                ) c on.. 
) GROUP BY ..

Open in new window

0
leezacAuthor Commented:
I get other errors adding more parenthesis.
0
Racim BOUDJAKDJIDatabase Architect - Dba - Data ScientistCommented:
It looks like you are missing two parenthesis in front of the rate and drate subqueries columns.
SELECT plan_n, 
       plan_shrt_nm, 
       dvsn_nm, 
       c.plan_id, 
       Sum( 
       	CASE 
              WHEN bal>0 THEN balance 
              ELSE 0 
       	END
		) AS "Assets" 
FROM  ( 
                 SELECT    a.plan_id, 
                           plan_n, 
                           plan_shrt_nm, 
                           Sum(end_aset_bal_a)AS bal 
                 FROM      dbo.v_bal_mo A 
                 	LEFT JOIN 
                           ( 
                            SELECT (
							(
							Sum( 
                                        CASE 
									WHEN stat_c IN ('A')  AND pcnt > 1 AND def_dol = 0 THEN 1 
                                        		ELSE 0 
                                        END
								)
							+
							Sum( 
                                         CASE 
                                                WHEN stat_c IN('A') 
                                                AND    pcnt < 1 
                                                AND    def_dol > 0 THEN 1 
                                                ELSE 0 
                                         END)
							)
							/
							Sum( 
                                         CASE 
                                                WHEN stat_c IN('A') THEN 1 
                                                ELSE 0 
                                         END
								)
							) AS rate, 
                                    Nvl(
								Round(
									Avg( 
                                         			CASE 
                                                		WHEN stat_c IN ('A') AND pcnt > 1 THEN pcnt 
                                                		
                                         			END
										),2)
									,0)
								) AS drate 
					FROM  ( 
							SELECT planid, 
							Nvl(def_a,0) AS def_dol, 
							Nvl(def_p,0) AS pcnt, 
							ssn_id, 
							stat_c 
 							FROM   dbo.v_part 
							WHERE  row_exp_d=To_date('01/01/9999','MM/DD/YYYY') 
							AND    plan_id IN ('207') 
						) C 
                     	ON A.plan_id=C.plan_id AND    A.ssn_id=C.ssn_id 
					) 
GROUP BY  plan_n, plan_shrt_nm, dvsn_nm

Open in new window


Also note that you have a possible divide by 0 case.  This TSQL could be simplified.  Hope this helps.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

slightwv (䄆 Netminder) Commented:
Didn't count the parans since others have done that.

I Oracle there are several reasons you get this generic error:
1: by default sqlplus and some tools don't like blank lines in the statement.  It looks like you have blank lines.

2: Trying to run newer syntax and functions against a database version that doesn't support it can also cause a paran error.  This is mainly with the datawarehouse functions but ANSI join syntax wasn't available until 9i (I think).
0
Mark GeerlingsDatabase AdministratorCommented:
I think I spent some time on your earlier question trying to solve this same problem, but there you posted only part of the query.  Seeing the whole query here makes things easier for us.

This line looks overly complex:
SUM(Case when BAL>0 THEN Balance Else 0 end) as "Assets"

That could by simply:
SUM(nvl(BAL,0)) as "Assets"

Then, this sub-query:
SELECT A.PLAN_ID, PLAN_N, PLAN_SHRT_NM, SUM(END_ASET_BAL_A)as BAL
       FROM dbo.V_BAL_MO A  

contains a group operator: "SUM", on one column, plus some un-summed columns, but I don't see a "group by" it this sub-query level.  That is a problem.

And yes, the possible "divide by 0" problem that I noticed in your other question is still here.  Maybe your data is such that now you usually find at least one record that meets this condition: STAT_C IN('A').  If that is true, you won't hit the "divide by 0" error.  But, this code still looks dangerous to me because I think sooner or later you will hit that "divide by 0" error.  I understand now that you you aren't actually dividing by 1 (which puzzled me in your other question, where we had only part of the statement to work with) but you are first summing a value that can be 1 or 0, then dividing by the sum, which I assume you expect to usually be greater than 1.
0
leezacAuthor Commented:
I did not write the code and may post the complete code to get help.  I am not a SQL guru and can use all the help I can get.  The code can probably be rewritten to be more efficient.
0
slightwv (䄆 Netminder) Commented:
>>That could by simply: SUM(nvl(BAL,0)) as "Assets"

Only if BAL is null.  What if it is -1?

I have used CASE statements in aggregates on many occasions.

>>I did not write the code and may post the complete code to get help.

Then why are you fixing the error?  Moving parans around can DRASTICALLY change the results.
0
leezacAuthor Commented:
I appreciate comments.
0
slightwv (䄆 Netminder) Commented:
I would suggest you get an editor that easily shows you matching parans.
If you are on Unix vi will find the match with a simple '%'. Place the cursor on the first paran then hit % and the cursor will jump to the match.

If on Windows, there are a few coding editors out there that will do this.  Since I am a 'vi' person, I use GViM.

Try this:
SELECT PLAN_N, PLAN_SHRT_NM, DVSN_NM,
   SUM(Case when BALANCE>0 THEN Balance Else 0 end) as "Assets",
   SUM(Case when BALANCE>0 THEN 1 Else 0 end) as "# EMPs w/ Bal",
   ROUND(SUM(Case when BALANCE>0 THEN Balance Else 0 end) /  SUM(Case when BALANCE>0 THEN 1 Else 0 end),2) as "Average"
      FROM(
      SELECT A.PLAN_ID, PLAN_N, PLAN_SHRT_NM, A.SSN_ID, STAT_C,CTRB_TXN_ELIG_I, DVSN_NM, SUM(END_ASET_BAL_A)as BALANCE, SUM(CUR_LOAN_BAL_A) as LOANBAL, A.AS_OF_D
      FROM dbo.V_SRC_BAL_MO A      
                LEFT JOIN
		(
                SELECT
                (
			Sum(case when STAT_C IN ('A') AND DEF_PCNT > 1 and DEF_DOL = 0 then 1 else 0 end)
			+
			Sum(case when STAT_C IN('A') AND DEF_PCNT < 1 and DEF_DOL > 0 then 1 else 0 end)
	 	)/
		Sum(case when STAT_C IN('A') then 1 else 0 end) as EMP_RATE,
                NVL(ROUND(AVG(CASE when STAT_C IN ('A') AND DEF_PCNT > 1 THEN DEF_PCNT end),2),0) as DEF_RATE, E.ROW_EFF_D
          FROM(
             SELECT Plan_ID, SSN_ID, SSN_N, NVL(DEF_A,0) as DEF_DOL, NVL(DEF_P,0) as DEF_PCNT, STAT_C
             FROM dbo.V_FDPAT_EMP
             WHERE row_exp_d=TO_DATE('01/01/9999','MM/DD/YYYY')
            -- AND PLAN_ID IN ('2071287')
             )
	) C ON E.PLAN_ID=C.PLAN_ID AND E.SSN_ID=C.SSN_ID    
            LEFT JOIN(
              SELECT PLAN_ID, SSN_ID,
              SUM(CUR_CTCH_UP_P + CUR_PRE_TAX_DFRL_P) As DEF_P
              FROM dbo.V_EMP_DFRL
              WHERE ROW_EXP_D=TO_DATE('01/01/9999','MM/DD/YYYY')
              Group by PLAN_ID, SSN_ID
               ) E ON A.SSN_ID=E.SSN_ID AND  A.PLAN_ID=E.PLAN_ID
            LEFT JOIN(
              SELECT *
              FROM dbo.V_FDLLN_EMP_LOAN -- use for loan balance
              WHERE row_exp_d=TO_DATE('01/01/9999','MM/DD/YYYY')
              AND CUR_LOAN_BAL_A > 0
              ) B ON A.PLAN_ID=B.PLAN_ID AND A.SSN_ID=B.SSN_ID
          LEFT JOIN(
              SELECT PLAN_ID, PLAN_N, PLAN_SHRT_NM
              FROM dbo.V_FCPLA_PLAN
              WHERE row_exp_d=TO_DATE('01/01/9999','MM/DD/YYYY')
             ) D ON A.PLAN_ID=D.PLAN_ID    
      WHERE A.AS_OF_D = '30-SEP-14'
      AND E.ROW_EFF_D = '30-SEP-14'
      AND A.PLAN_ID IN ('207')
           GROUP BY A.PLAN_ID, A.PLAN_ID, PLAN_N, PLAN_SHRT_NM, A.SSN_ID, A.AS_OF_D, STAT_C,CTRB_TXN_ELIG_I ,CUR_LOAN_BAL_A, DVSN_NM
      ORDER BY A.SSN_ID
)
GROUP BY PLAN_N, PLAN_SHRT_NM, DVSN_NM 
/

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
leezacAuthor Commented:
Oracle SQL
0
slightwv (䄆 Netminder) Commented:
>>Oracle SQL

I do not know what this is trying to tell me.
0
leezacAuthor Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for leezac's comment #a40448694
Assisted answer: 84 points for Jim Horn's comment #a40443436
Assisted answer: 84 points for Racimo's comment #a40443604
Assisted answer: 83 points for slightwv's comment #a40443632
Assisted answer: 83 points for markgeer's comment #a40443703
Assisted answer: 83 points for slightwv's comment #a40443858
Assisted answer: 83 points for slightwv's comment #a40448635

for the following reason:

Thank you!
0
leezacAuthor Commented:
Thank you!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.