Subquery problem - 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)

Subquery problem

Author

Topic

eilob
Starting Member

6 Posts

Posted - 2008-04-22 : 11:52:51

Hi all, I am having a problem with a query, I am trying to create a chart using fusion charts in Visual studio, I have a subquery, but it is giving me an error

The column prefix 'd' does not match with a table name or alias name used in the query.

Here is my query

sql1 = "Select (d.WeekofYr),"

+ "(select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='1 Jan 2008') AS Week1,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='7 Jan 2008') AS Week2,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='14 Jan 2008') AS Week3,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='21 Jan 2008') AS Week4,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='28 Jan 2008') AS Week5,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='4 Feb 2008') AS Week6"

+ " from Bid_Desk_France_Cumulative_Margin_Approved d";

Any ideas??

Thanks & Regards

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-04-22 : 13:55:12

Remove the "d." alias from the sub queries, but because your sub queries are returning more than one column it sill is not going to work. Can you explain what you are trying to do?

sql1 = "Select (d.WeekofYr),"

+ "(select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='1 Jan 2008') AS Week1,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='7 Jan 2008') AS Week2,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='14 Jan 2008') AS Week3,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='21 Jan 2008') AS Week4,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='28 Jan 2008') AS Week5,"

+ " (select PSC_Approved, Field_Approved, All_Requests from Bid_Desk_France_Cumulative_Margin_Approved where d.WeekOfYr='4 Feb 2008') AS Week6"

+ " from Bid_Desk_France_Cumulative_Margin_Approved d"

chandan_joshi80
Starting Member

30 Posts

Posted - 2008-04-24 : 07:47:24

TRYING TO ACCESS ALIASNAME.COLNAME
BUT ALIAS IS NOT DEFINE.
THAT IS THE REASON OF YOUR PROBLEM.CHECK YOUR QUERY CAREFULLY.

chandan Joshi

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 08:26:24

This is SO WRONG ON SO MAY LEVELS so I don't really know where to begin...

1) Subqueries can only return a scalar value in SQL Server 2000. You want to return 3 columns.
2) Subqueries can only return a scalar value in SQL Server 2000. You want to return an unknown number of records.
3) Dynamic SQL?

E 12°55'05.25"
N 56°04'39.16"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2008-04-24 : 08:41:31

Let me add:

4) Are you using DateTime data types for your dates? Doesn't look like it.
5) Hard-coded dates values in the SQL code
6) What happens to your data and/or your SQL when you have data for more than 1 year?

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-24 : 08:55:28

Do you want something like this?

SELECT		WeekOfYr,
		SUM(CASE WHEN WeekOfYr = '1 Jan 2008' THEN PSC_Approved ELSE 0 END) AS Week1,
		SUM(CASE WHEN WeekOfYr = '7 Jan 2008' THEN PSC_Approved ELSE 0 END) AS Week2,
		SUM(CASE WHEN WeekOfYr = '14 Jan 2008' THEN PSC_Approved ELSE 0 END) AS Week3,
		SUM(CASE WHEN WeekOfYr = '21 Jan 2008' THEN PSC_Approved ELSE 0 END) AS Week4,
		SUM(CASE WHEN WeekOfYr = '28 Jan 2008' THEN PSC_Approved ELSE 0 END) AS Week5,
		SUM(CASE WHEN WeekOfYr = '4 Feb 2008' THEN PSC_Approved ELSE 0 END) AS Week6
FROM		Bid_Desk_France_Cumulative_Margin_Approved
GROUP BY	WeekOfYr
ORDER BY	WeekOfYr

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources