Problem finding a column name.

0 pts.
Tags:
SQL
T-SQL
The following SQL is giving me the message: Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'ProductCode'. How can I correct this code. The column name does exist in the table. I also want to group by Product Code and not by Container Name. Thank you. SELECT a.ProductCode AS "Product Name", a.TestContainerName as "Test Container", Sum (a.Passed + a.Failed) AS Executed, Sum(a.Passed) AS Passed, Sum(a.Failed) AS Failed, Sum(a.NotExecuted) AS "Not Executed", Sum(a.NotScheduled) AS "Not Scheduled" FROM ( SELECT td.TestContainerName, th.TreeOrder, CASE when td.TestCovStatusID=1 then 1 else 0 end AS Passed, CASE when td.TestCovStatusID=2 then 1 else 0 end AS Failed, CASE when td.TestCovStatusID=3 then 1 else 0 end AS NotExecuted, CASE when td.TestCovStatusID=4 then 1 else 0 end AS NotScheduled, CASE when td.TestCovStatusID=5 then 1 else 0 end AS Executed FROM ( SELECT DISTINCT tdc.ProductCode, tds.TestContainerID, tds.TestContainerName, tds.TestID, CASE when tds.CurrentExecStatus=8 then 1 else CASE when tds.CurrentExecStatus=9 then 2 else tds.CurrentExecStatus end end AS TestCovStatusID FROM TM_V_TestDefinitions tds, RTM_V_TestHierarchie th, RTM_V_TestDefsComponents tdc WHERE tds.ProjectID = th.ProjectID_fk and tdc.ProjectID = th.ProjectID_fk ) td INNER JOIN RTM_V_TestHierarchie th ON (th.NodeID_pk = td.TestContainerID) ) a GROUP BY a.TestContainerName, a.TreeOrder ORDER BY a.TreeOrder
ASKED: August 15, 2006  3:58 PM
UPDATED: August 20, 2006  5:39 AM

Answer Wiki

Thanks. We'll let you know when a new response is added.

Each column name, when there is a GROUP BY must appear in the GROUP BY list, or in a column function, such as MIN, SUM, or whatever. ProductCode does neither.

Sheldon Linker (sol@linker.com)
Linker Systems, Inc. (www.linkersystems.com)
800-315-1174 (+1-949-552-1904)

Discuss This Question: 1  Reply

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • 9873098877
    dear captnskirk, If you remember GROUP BY can not use with views. In your query a is alias, the inner query is inline view. Finally the error "INVALID COLUMN" is the reason of this. The above invalid column can be solved by another way. If you remember by using GROUPING() in oracle. If you require further please call me. hopefully this will solve your problem. -- TP Singh
    0 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following