Solved

Missing Right Parenthesis Oracle SQL

Posted on 2014-11-14
13
573 Views
Last Modified: 2014-11-22
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
0
Comment
Question by:leezac
13 Comments
 
LVL 65

Assisted Solution

by:Jim Horn
Jim Horn earned 84 total points
ID: 40443436
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
 

Author Comment

by:leezac
ID: 40443558
I get other errors adding more parenthesis.
0
 
LVL 23

Assisted Solution

by:Racim BOUDJAKDJI
Racim BOUDJAKDJI earned 83 total points
ID: 40443604
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
 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40443632
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
 
LVL 34

Assisted Solution

by:Mark Geerlings
Mark Geerlings earned 83 total points
ID: 40443703
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
 

Author Comment

by:leezac
ID: 40443825
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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 76

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 250 total points
ID: 40443858
>>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
 

Author Comment

by:leezac
ID: 40446336
I appreciate comments.
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 250 total points
ID: 40448635
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
 

Author Comment

by:leezac
ID: 40448694
Oracle SQL
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40448762
>>Oracle SQL

I do not know what this is trying to tell me.
0
 

Author Comment

by:leezac
ID: 40459496
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
 

Author Closing Comment

by:leezac
ID: 40459497
Thank you!
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now