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
 SQL Server 2012 Forums
 Analysis Server and Reporting Services (2012)
 2 updates within 1 query window...
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Cowski
Starting Member

USA
29 Posts

Posted - 07/17/2013 :  08:03:13  Show Profile  Send Cowski an ICQ Message  Reply with Quote
I have 2 dropdown parameters on my report. We'll call them "Update Production(Y/N)" and "Update AT(Y/N)".
Is it possible, within 1 query window, on a dataset property to have a logic in there that if the parameter is 1 (Yes) from the "Update Production(Y/N)" dropdown, update the Production table with values. And if the parameter is 1 (Yes) from the "Update AT(Y/N)" dropdown, update the AT table? Then after the updates happen (or not happen), a select statement runs to repopulate the report with the updated values.
Below is the code I'm wanting to see work within 1 query window. If this is not possible within 1 query window, what can I do to make this work:


If @UpdateProduction = 1 
    BEGIN
	   Update Orders.ProductVendorControl
	   SET ConfirmedProdDate = GETDATE(),
	   ConfirmedProdBy = @UserId
	   Where VendorId = @VendorIdLookup
    END

  
If @UpdateAT = 1
    BEGIN
	   Update Orders.ProductVendorControl
	   SET ConfirmedATDate = GETDATE(),
	   ConfirmedATBy = @UserId
	   Where VendorId = @VendorIdLookup
    END  

Select * from tables...


Thank you for your time.

Edited by - Cowski on 07/17/2013 08:03:58

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/17/2013 :  11:11:59  Show Profile  Reply with Quote
you can use the query in single query window. Didnt understand what was the issue you faced?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

Cowski
Starting Member

USA
29 Posts

Posted - 07/17/2013 :  11:44:46  Show Profile  Send Cowski an ICQ Message  Reply with Quote
I was getting errors whenever I'd throw the 2nd IF statement in there.
"Incorrect syntax near ','" error. Fixed that by implementing a "in ( )" with the where clause. Fixed that right up.

So note to self....if you get the error "Incorrect syntax near ','" fix it by implementing a "in ( )" with the where clause.

Thanks!!

Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  01:36:27  Show Profile  Reply with Quote
quote:
Originally posted by Cowski

I was getting errors whenever I'd throw the 2nd IF statement in there.
"Incorrect syntax near ','" error. Fixed that by implementing a "in ( )" with the where clause. Fixed that right up.

So note to self....if you get the error "Incorrect syntax near ','" fix it by implementing a "in ( )" with the where clause.

Thanks!!




hmm..was it a delimited list that you were trying to pass in WHERE? If yes, you need to use IN.
Incorrect syntax near ',' is a generic error message which just indicates the problem in syntax before that deimilter. Its not always due to above reason alone.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30178 Posts

Posted - 07/18/2013 :  02:19:01  Show Profile  Visit SwePeso's Homepage  Reply with Quote
UPDATE	Orders.ProductVendorControl
SET	ConfirmedProdDate =	CASE @UpdateProduction
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedProdDate
				END,
	ConfirmedProdBy =	CASE @UpdateProduction
					WHEN 1 THEN @UserID
					ELSE ConfirmedProdBy
				END,
	ConfirmedATDate =	CASE @UpdateAT
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedATDate
				END
	ConfirmedATBy =		CASE @UpdateAT
					WHEN 1 THEN @UserID
					ELSE ConfirmedATBy
				END
WHERE	VendorID = @VendorIdLookup;



N 56°04'39.26"
E 12°55'05.63"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  02:30:02  Show Profile  Reply with Quote
since its posted in 2012
you can even use IIF which is a short hand way of writing CASE


UPDATE	Orders.ProductVendorControl
SET	ConfirmedProdDate =	IIF(@UpdateProduction = 1, GETDATE() ,ConfirmedProdDate),
	ConfirmedProdBy   =	IIF(@UpdateProduction = 1, @UserID , ConfirmedProdBy),
	ConfirmedATDate   =	IIF(@UpdateAT = 1, GETDATE() , ConfirmedATDate),
	ConfirmedATBy     =	IIF(@UpdateAT = 1, @UserID , ConfirmedATBy)
WHERE	VendorID = @VendorIdLookup;


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 07/18/2013 :  02:31:12  Show Profile  Reply with Quote
quote:
Originally posted by SwePeso

UPDATE	Orders.ProductVendorControl
SET	ConfirmedProdDate =	CASE @UpdateProduction
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedProdDate
				END,
	ConfirmedProdBy =	CASE @UpdateProduction
					WHEN 1 THEN @UserID
					ELSE ConfirmedProdBy
				END,
	ConfirmedATDate =	CASE @UpdateAT
					WHEN 1 THEN GETDATE()
					ELSE ConfirmedATDate
				END,
	ConfirmedATBy =		CASE @UpdateAT
					WHEN 1 THEN @UserID
					ELSE ConfirmedATBy
				END
WHERE	VendorID = @VendorIdLookup;



N 56°04'39.26"
E 12°55'05.63"



fixed typo

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

Edited by - visakh16 on 07/18/2013 02:31:47
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.11 seconds. Powered By: Snitz Forums 2000