Subqueries and Variables - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

Subqueries and Variables

Author

Topic

kazama
Starting Member

4 Posts

Posted - 2005-11-02 : 13:04:20

This is a SQL 2000 question regarding subqueries and variables. I Have a large select which looks like this:

SELECT     dbo.Sites.sitename, dbo.Member.fname, dbo.Member.lname, dbo.Member.address1, dbo.Member.address2, dbo.Member.city, dbo.Member.state, 
                      dbo.Member.zip, dbo.Member.country, dbo.Member.title, dbo.Member.phone, dbo.Member.fax, dbo.Member.salutation, dbo.Member.last_visit, 
                      dbo.Approved_Product.name AS [Product Name], dbo.Approved_Company.name AS [Company Name], dbo.Product_log.date_enter,
                          (SELECT     Response.Answer
                            FROM          response
                            WHERE      response.qid = 1 AND response.member_id = member.uid) AS Job,
                          (SELECT     Response.Answer
                            FROM          response
                            WHERE      response.qid = 2 AND response.member_id = member.uid) AS Industry
FROM         dbo.Product_log INNER JOIN
                      dbo.Member ON dbo.Product_log.uid = dbo.Member.uid INNER JOIN
                      dbo.Approved_Product ON dbo.Product_log.prod_id = dbo.Approved_Product.prod_id INNER JOIN
                      dbo.Approved_Company ON dbo.Approved_Product.company_id = dbo.Approved_Company.company_id INNER JOIN
                      dbo.Sites ON dbo.Product_log.site_id = dbo.Sites.siteid CROSS JOIN
                      dbo.Response

WHERE     (dbo.Product_log.date_enter BETWEEN '10/25/05' AND '10/26/05')

And this runs absolutely fine. However, when I change the SQL to use a variable for the start and end date like this:

SELECT     dbo.Sites.sitename, dbo.Member.fname, dbo.Member.lname, dbo.Member.address1, dbo.Member.address2, dbo.Member.city, dbo.Member.state, 
                      dbo.Member.zip, dbo.Member.country, dbo.Member.title, dbo.Member.phone, dbo.Member.fax, dbo.Member.salutation, dbo.Member.last_visit, 
                      dbo.Approved_Product.name AS [Product Name], dbo.Approved_Company.name AS [Company Name], dbo.Product_log.date_enter,
                          (SELECT     Response.Answer
                            FROM          response
                            WHERE      response.qid = 1 AND response.member_id = member.uid) AS Job,
                          (SELECT     Response.Answer
                            FROM          response
                            WHERE      response.qid = 2 AND response.member_id = member.uid) AS Industry
FROM         dbo.Product_log INNER JOIN
                      dbo.Member ON dbo.Product_log.uid = dbo.Member.uid INNER JOIN
                      dbo.Approved_Product ON dbo.Product_log.prod_id = dbo.Approved_Product.prod_id INNER JOIN
                      dbo.Approved_Company ON dbo.Approved_Product.company_id = dbo.Approved_Company.company_id INNER JOIN
                      dbo.Sites ON dbo.Product_log.site_id = dbo.Sites.siteid CROSS JOIN
                      dbo.Response
WHERE     (dbo.Product_log.date_enter BETWEEN @START_DATE AND @END_DATE)

I get "ADO error: Syntax error". I have tried looking for this and I Can't figure out what is wrong with the query or why I can't mix variables when I use subqueries. Any ideas on this one?

dupati1
Posting Yak Master

123 Posts

Posted - 2005-11-02 : 13:13:28

can you show your variables declaration and how did you set them...

i think you need to use CONVERT() or CAST() function...

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-02 : 13:16:42

ADO error sounds like this is from a client app. Are you trying to create a parameterised query?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kazama
Starting Member

4 Posts

Posted - 2005-11-02 : 13:56:23

quote:
Originally posted by dupati1

can you show your variables declaration and how did you set them...

i think you need to use CONVERT() or CAST() function...

Back info, this is report being developed for Microsoft Reporting Services so some variable declaration is not needed as with standrad Transact things. The variables are defined to convert the input to DateTime before submitting it to the query.

kazama
Starting Member

4 Posts

Posted - 2005-11-02 : 14:00:46

quote:
Originally posted by nr

ADO error sounds like this is from a client app. Are you trying to create a parameterised query?

I am not sure what you are asking but let me better explain the goal. We have a majority of data in one table and a response to a series of questions in another table. If I do a query and ask for all responses, it delivers them as multiple line entries in the results. The goal is to get those multiple line answers on a single line attached to the uid which is always the same. i.e.


uid fname lname question response
1 joe burns Industry Accounting
1 joe burns Title CEO

would become


uid fname lname Industry Title
1 joe burns Accounting CEO

nr
SQLTeam MVY

12543 Posts

Posted - 2005-11-03 : 08:31:24

The problem is your parameters.
That you are getting an ado error implies that you are calling this from a client.

Try running it in query analyser.
It's probably not an sql problem but a application bug.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

kazama
Starting Member

4 Posts

Posted - 2005-11-04 : 12:41:56

Just wanted to let everyone know that it is a bug in VS.Net. It kicks out an error on the Data View but if you ignore it and go to the preview pane, the report will function as designed.

X002548
Not Just a Number

15586 Posts

Posted - 2005-11-04 : 13:22:18

quote:
Originally posted by kazama

Just wanted to let everyone know that it is a bug in VS.Net. It kicks out an error on the Data View but if you ignore it and go to the preview pane, the report will function as designed.

riiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiiight

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources