how to put sub query in dynamic query

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

how to put sub query in dynamic query

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2013-04-20 : 08:35:51

Hello ,

in my select query i am using sub query to get SUM() of 2 values and while i am using where condition i am unable to use that sub query column in group by and where clause using dynamic query

here is my query :


				SELECT	
									DISTINCT
					           
										L.ProfileID,
										B.Cust_ID ,
										B.FirstName + B.LastName AS Name,
										(SELECT Sum(CMM.MembershipAmount)  from Cust_MemberShips CMM
										 WHERE Cust_ID = B.Cust_ID ) As AgreedAmount,
										SUM (CAST(CP.PaidAmount AS INT)) As PaidAmount,
										(SELECT Sum(CMM.MembershipAmount)  from Cust_MemberShips CMM
										 WHERE Cust_ID = B.Cust_ID )- SUM (CAST(CP.PaidAmount AS INT)) As BalanceAmount
							FROM 
								Cust_BasicInfo B 	
							INNER JOIN	Cust_Login L 
								ON L.Cust_ID=B.Cust_ID
							INNER JOIN Cust_Details D 
								ON D.Cust_ID=B.Cust_ID
							INNER JOIN Cust_Payments CP 
								ON CP.Cust_ID= B.Cust_ID

In the where clause i need to write dynamic query if AGREED Amount and PaidAmount If PaidAmount is less than AGREED Amount i need to get that data

how can i write dynamic query for this one in where condition

and more over if we do sum() need to write group by for all columns...i am calling


  DECLARE @Amount INT 
   SET @Amount = (SELECT Sum(CMM.MembershipAmount)  from Cust_MemberShips CMM
									INNER JOIN Cust_BasicInfo BB
									ON BB.Cust_ID = CMM.Cust_ID
										 WHERE CMM.Cust_ID = BB.Cust_ID)
										 select @Amount

how i can @Amount put this declare statement in dynamic query

or how can i put this subquery in dynamic query


(SELECT Sum(CMM.MembershipAmount)  from Cust_MemberShips CMM
										 WHERE Cust_ID = B.Cust_ID )- SUM (CAST(CP.PaidAmount AS INT))

alteast i need get data of CMM.MembershipAmount less than CP.PaidAmount

my dynamic query look like this one...


	 IF @b_IsBalance = 1
  BEGIN
  SET @vc_SQL = @vc_SQL +' ' + ' AND (SELECT Sum(CMM.MembershipAmount)  from Cust_MemberShips CMM
										 WHERE Cust_ID = B.Cust_ID )- SUM (CAST(CP.PaidAmount AS INT))  '	 
  END

suggest me

P.V.P.MOhan

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-04-20 : 13:02:08

why cant you add a join to subquery instead of adding it as a correlated query above?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources