SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 how to put sub query in dynamic query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 04/20/2013 :  08:35:51  Show Profile  Reply with Quote
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

Edited by - mohan123 on 04/20/2013 08:44:25

visakh16
Very Important crosS Applying yaK Herder

India
52249 Posts

Posted - 04/20/2013 :  13:02:08  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000