Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 missing right parenthesis help (dont see it)

Author  Topic 

Chloe_19
Starting Member

44 Posts
Posted - 2012-03-22 : 20:22:56
Says missing right parenthesis on last line :
AND C.SUBJECT_CDE = D.SUBJECT_CDE))D)
I dont see the problem. I closed all brackets.
Any advice?

FROM(

--PROVIDER COUNT
select distinct
C.OUA_ID,C.PAYMENT_MTHD_COUNT,C.PROVIDER_CDE,C.PAYMENT_MTHD,C.ENROL_STATUS,C.SUBJECT_CDE,
D.OUA_DISCIPLINE_CDE
FROM
(SELECT *
from(
SELECT OUA_ID, PAYMENT_MTHD,PROVIDER_CDE, ENROL_STATUS,SUBJECT_CDE, count(*) PAYMENT_MTHD_COUNT
from OUA_ENROLMENT_MV 
GROUP BY OUA_ID, PAYMENT_MTHD, PROVIDER_CDE,ENROL_STATUS,SUBJECT_CDE))c


INNER JOIN
(SELECT *
from(
select OUA_DISCIPLINE_CDE, SUBJECT_CDE
from OUA_UNIT_VW
group by OUA_ID, OUA_DISCIPLINE_CDE
on C.OUA_ID = D.OUA_ID
AND C.SUBJECT_CDE = D.SUBJECT_CDE))D)

--where OUA_ID = '100009'
--group by OUA_ID
--ORDER BY OUA_ID ASC

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-22 : 21:04:56
if you format you code a bit by doing some indentation, you might see it much clearer. The line in red is out of place ?

Also, you missed out all the alias for the derived table.


FROM
(
	--PROVIDER COUNT
	select 	distinct
		C.OUA_ID,C.PAYMENT_MTHD_COUNT,C.PROVIDER_CDE,C.PAYMENT_MTHD,C.ENROL_STATUS,C.SUBJECT_CDE,
		D.OUA_DISCIPLINE_CDE
	FROM
	(
		SELECT *
		from
		(
			SELECT OUA_ID, PAYMENT_MTHD,PROVIDER_CDE, ENROL_STATUS,SUBJECT_CDE, count(*) PAYMENT_MTHD_COUNT
			from OUA_ENROLMENT_MV 
			GROUP BY OUA_ID, PAYMENT_MTHD, PROVIDER_CDE,ENROL_STATUS,SUBJECT_CDE
		 ) as A
	) c
	INNER JOIN
	(
		SELECT *
		from
		(
			select 	OUA_DISCIPLINE_CDE, SUBJECT_CDE
			from 	OUA_UNIT_VW
			group by OUA_ID, OUA_DISCIPLINE_CDE
	on C.OUA_ID = D.OUA_ID
	AND C.SUBJECT_CDE = D.SUBJECT_CDE
		) as A
	)D
) as A


I wonder, does your original query runs at all ? Are you using MS SQL Server ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-22 : 21:07:00
oh just recalled, you are using Oracle. Maybe you can do that in Oracle, but you sure can't do that in MS SQL Server.

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Chloe_19
Starting Member

44 Posts
Posted - 2012-03-22 : 22:28:52
Original works fine.
Am just trying to add nother variable but this time from the OUA_UNIT_VW table.

I still get missing right perenthesis this time on the BOLND bracket in the last select statemnt. 

FROM
(
	--PROVIDER COUNT
	select 	distinct
		C.OUA_ID,C.PAYMENT_MTHD_COUNT,C.PROVIDER_CDE,C.PAYMENT_MTHD,C.ENROL_STATUS,C.SUBJECT_CDE,
		D.OUA_DISCIPLINE_CDE
	FROM
	(
		SELECT *
		from
		(
			SELECT OUA_ID, PAYMENT_MTHD,PROVIDER_CDE, ENROL_STATUS,SUBJECT_CDE, count(*) PAYMENT_MTHD_COUNT
			from OUA_ENROLMENT_MV 
			GROUP BY OUA_ID, PAYMENT_MTHD, PROVIDER_CDE,ENROL_STATUS,SUBJECT_CDE
		 ) as A
	) c
	INNER JOIN
	(
		SELECT *
		from
		(			select 	OUA_DISCIPLINE_CDE, SUBJECT_CDE
			from 	OUA_UNIT_VW
			group by OUA_ID, OUA_DISCIPLINE_CDE
	on C.OUA_ID = D.OUA_ID
	AND C.SUBJECT_CDE = D.SUBJECT_CDE
		) as A
	)D
) as A
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-22 : 22:33:04
i didn't fixed it in my last post. I just highlighted the 2 lines in read that i think it is out of place. It suppose to be after the ") as D"

	INNER JOIN
	(
		SELECT *
		from
		(			select 	OUA_DISCIPLINE_CDE, SUBJECT_CDE
			from 	OUA_UNIT_VW
			group by OUA_ID, OUA_DISCIPLINE_CDE
	on C.OUA_ID = D.OUA_ID
	AND C.SUBJECT_CDE = D.SUBJECT_CDE
		) as A
	)D
	on C.OUA_ID = D.OUA_ID
	AND C.SUBJECT_CDE = D.SUBJECT_CDE


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Chloe_19
Starting Member

44 Posts
Posted - 2012-03-22 : 22:44:56
Ohh ok, but still get the same error on the same line.
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-22 : 22:46:59
can you post the query that you used ?

KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Chloe_19
Starting Member

44 Posts
Posted - 2012-03-22 : 23:05:01
Here is what i got and it works 100% fine.

Select OUA_ID,

Count(Case When PAYMENT_MTHD = 'CASH'    Then 1 End)  As CASH,
Count(Case When PAYMENT_MTHD = 'Credit Card'    Then 1 End)  As "Credit Card",
Count(Case When PAYMENT_MTHD = 'Cheque / Money Order'    Then 1 End)  "Cheque / Money Order",
Count(Case When PAYMENT_MTHD = 'FEE-HELP'    Then 1 End)  "FEE-HELP"

,ROUND (COALESCE (MAX( DECODE( PAYMENT_MTHD, 'FEE-HELP', PAYMENT_MTHD_COUNT, NULL ) ), 0 ) * 100.0 / (COALESCE (MAX( DECODE( PAYMENT_MTHD, 'Credit Card', PAYMENT_MTHD_COUNT, NULL ) ), 0 )
+ coalesce (max( DECODE( PAYMENT_MTHD, 'CASH', PAYMENT_MTHD_COUNT, null ) ), 0 )+ coalesce (max( DECODE( PAYMENT_MTHD, 'FEE-HELP', PAYMENT_MTHD_COUNT, null ) ), 0 )
+ COALESCE (MAX( DECODE( PAYMENT_MTHD, 'Cheque / Money Order', PAYMENT_MTHD_COUNT, null ) ), 0 ))) "PAYMENT FEE-HELP %",

Count(Case When PROVIDER_CDE = 'ACS'    Then 1 End)  As ACS   ,       
Count(Case When PROVIDER_CDE=  'ACU'    Then 1 End)  As ACU   ,       
Count(Case When PROVIDER_CDE = 'AITAF'  Then 1 End)  As AITF  ,       


Count(                                1    )  As "Total_Enrolments"    


FROM(

--PROVIDER COUNT
select distinct
C.oua_id,c.PAYMENT_MTHD_COUNT,C.PROVIDER_CDE,C.PAYMENT_MTHD,C.ENROL_STATUS
FROM
(SELECT *
FROM(
SELECT OUA_ID, PAYMENT_MTHD,PROVIDER_CDE,ENROL_STATUS, count(*) PAYMENT_MTHD_COUNT
FROM oua_enrolment_mv 
--WHERE Provider_cde = 'RMIT'
GROUP BY OUA_ID, PAYMENT_MTHD, PROVIDER_CDE,ENROL_STATUS))c
) 
WHERE OUA_ID = '100009'
GROUP BY OUA_ID
ORDER BY OUA_ID ASC



I am trying to add another INNER JOIN from a DIFFERENT TABLE to display another variable.
I tried:

	INNER JOIN
	(
		SELECT *
		from
		(
			select 	OUA_DISCIPLINE_CDE, SUBJECT_CDE
			from 	OUA_UNIT_VW
			group by OUA_ID, OUA_DISCIPLINE_CDE


But i get wrong perenthesis.

(also if i add nother inner join from the oua_enrolment_mv Table (original) i get an added count to "Total_Enrolments")
How do i avoid and put it in an different count column?
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-22 : 23:27:56
this part

	select 	OUA_DISCIPLINE_CDE, SUBJECT_CDE
	from 	OUA_UNIT_VW
	group by OUA_ID, OUA_DISCIPLINE_CDE

should it be ?

SELECT OUA_ID, SUBJECT_CDE
FROM   OUA_UNIT_VW
GROUP BY OUA_ID, SUBJECT_CDE


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Chloe_19
Starting Member

44 Posts
Posted - 2012-03-22 : 23:31:18
yes,
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts
Posted - 2012-03-22 : 23:37:04
try

FROM
(
	-- PROVIDER COUNT
	select 	distinct
		C.oua_id,c.PAYMENT_MTHD_COUNT,C.PROVIDER_CDE,C.PAYMENT_MTHD,C.ENROL_STATUS
	FROM
	(
		SELECT *
		FROM
		(
			SELECT 	OUA_ID, PAYMENT_MTHD,PROVIDER_CDE,ENROL_STATUS, count(*) PAYMENT_MTHD_COUNT
			FROM 	oua_enrolment_mv 
			--WHERE Provider_cde = 'RMIT'
			GROUP BY OUA_ID, PAYMENT_MTHD, PROVIDER_CDE,ENROL_STATUS
		)
	)C
	INNER JOIN
	(
		SELECT *
		from
		(
			SELECT OUA_ID, SUBJECT_CDE
			FROM   OUA_UNIT_VW
			GROUP BY OUA_ID, SUBJECT_CDE
		)
	)D
		on 	C.OUA_ID 	= D.OUA_ID
		AND 	C.SUBJECT_CDE 	= D.SUBJECT_CDE
)


KH
[spoiler]Time is always against us[/spoiler]
Go to Top of Page

Chloe_19
Starting Member

44 Posts
Posted - 2012-03-22 : 23:51:33
UNIT Table does not contain oua_id.
Can only be matched by Subject_cde (and if i add catalogue_no)
(Subject_cde and Catalogue_no are in both tables (linked))

I tried this and now i get not a single-group group function.

Select OUA_ID,

Count(Case When PAYMENT_MTHD = 'CASH'    Then 1 End)  As CASH,
Count(Case When PAYMENT_MTHD = 'Credit Card'    Then 1 End)  As "Credit Card",
Count(Case When PAYMENT_MTHD = 'Cheque / Money Order'    Then 1 End)  "Cheque / Money Order",
Count(Case When PAYMENT_MTHD = 'FEE-HELP'    Then 1 End)  "FEE-HELP"

,ROUND (COALESCE (MAX( DECODE( PAYMENT_MTHD, 'FEE-HELP', PAYMENT_MTHD_COUNT, NULL ) ), 0 ) * 100.0 / (COALESCE (MAX( DECODE( PAYMENT_MTHD, 'Credit Card', PAYMENT_MTHD_COUNT, NULL ) ), 0 )
+ coalesce (max( DECODE( PAYMENT_MTHD, 'CASH', PAYMENT_MTHD_COUNT, null ) ), 0 )+ coalesce (max( DECODE( PAYMENT_MTHD, 'FEE-HELP', PAYMENT_MTHD_COUNT, null ) ), 0 )
+ COALESCE (MAX( DECODE( PAYMENT_MTHD, 'Cheque / Money Order', PAYMENT_MTHD_COUNT, null ) ), 0 ))) "PAYMENT FEE-HELP %",

Count(Case When PROVIDER_CDE = 'ACS'    Then 1 End)  As ACS   ,       
Count(Case When PROVIDER_CDE=  'ACU'    Then 1 End)  As ACU   ,       
Count(Case When PROVIDER_CDE = 'AITAF'  Then 1 End)  As AITF  ,       


Count(                                1    )  As "Total_Enrolments"    


FROM
(
	-- PROVIDER COUNT
	select 	distinct
		C.oua_id,c.PAYMENT_MTHD_COUNT,C.PROVIDER_CDE,C.PAYMENT_MTHD,C.ENROL_STATUS,C.SUBJECT_CDE 
	FROM
	(
		SELECT *
		FROM
		(
			SELECT 	OUA_ID, PAYMENT_MTHD,SUBJECT_CDE ,PROVIDER_CDE,ENROL_STATUS, count(*) PAYMENT_MTHD_COUNT
			FROM 	oua_enrolment_mv 
			--WHERE Provider_cde = 'RMIT'
			GROUP BY OUA_ID, PAYMENT_MTHD, SUBJECT_CDE, PROVIDER_CDE, ENROL_STATUS
		)
	)C
	INNER JOIN
	(
		SELECT *
		from
		(
			SELECT  SUBJECT_CDE
			FROM   OUA_UNIT_VW
			GROUP BY  SUBJECT_CDE
		)
	)D
		on 	C.SUBJECT_CDE 	= D.SUBJECT_CDE
)
Go to Top of Page
   

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources

Articles

Forums

Blogs

Contact Us

About the Site