Solved

SQL Server Query

Posted on 2013-06-17
13
210 Views
Last Modified: 2013-06-17
In SQL Server 2000, I am getting an error on line 1 invalid column name for the query below. Any ideas?

SELECT
C.CustomerID
,C.Name
,C.Address
,CASE WHEN Max = 1 THEN 'A'
WHEN Max = 2 THEN 'B'
END AS [Inf]
FROM
Customer as C
JOIN
(SELECT Inf.Cust_Ky,
 MAX(CASE WHEN cmp_cd='A' THEN 1
 WHEN cmp_cd='B' THEN 2
            ) as INF
FROM INF AS I
WHERE (stat_cd = 'Y' AND Inf_cd = 'Y')
OR (Cust_stat = 'O' AND Inf_Stat = 'O')
OR (Cust_Stat = 'N' AND inf_date = cust_c_date)
GROUP BY inf.cust_ky
      )InfSub
ON C.Cust_ky = InfSub.Cust_ky
WHERE
C.Cust_ID  in ('123','124')
0
Comment
Question by:fjkaykr11
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 25

Expert Comment

by:chaau
ID: 39254917
You need to use INF in place of Max:
SELECT
C.CustomerID
,C.Name
,C.Address
,CASE WHEN InfSub.INF = 1 THEN 'A'
WHEN InfSub.INF = 2 THEN 'B'
END AS [Inf]
FROM
Customer as C
JOIN
(SELECT Inf.Cust_Ky,
 MAX(CASE WHEN cmp_cd='A' THEN 1
 WHEN cmp_cd='B' THEN 2
            ) as INF
FROM INF AS I
WHERE (stat_cd = 'Y' AND Inf_cd = 'Y')
OR (Cust_stat = 'O' AND Inf_Stat = 'O')
OR (Cust_Stat = 'N' AND inf_date = cust_c_date)
GROUP BY inf.cust_ky
      )InfSub
ON C.Cust_ky = InfSub.Cust_ky
WHERE
C.Cust_ID  in ('123','124') 

Open in new window

0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39254981
thanks for replying I am still getting the same error when I try that.
0
 
LVL 25

Expert Comment

by:chaau
ID: 39255011
Ok, I see. It looks like there is no column "Name" in your Customer  table. Can you run
SELECT * From Customer 

Open in new window

and post the column names here
0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 3

Author Comment

by:fjkaykr11
ID: 39255050
there is the column name is customer table.  I can't post the data.
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39255063
I think I found the problem but don't know how to fix. I need to add the table name of the customer table to the columns I reference within the subquery but not sure how I tried a few configurations.  The problem isn't on line 1, even though error says so.
0
 
LVL 25

Assisted Solution

by:chaau
chaau earned 75 total points
ID: 39255064
Ok then. I have not asked for data anyway.
Please check these columns in your Customer table:

CustomerID, Name, Address, Cust_ID, Cust_ky

Please check these columns in your Inf table:

cmp_cd, Cust_ky, stat_cd, Inf_cd, Cust_stat, Inf_Stat,  inf_date, cust_c_date

Do they exist? (I am not sure about cust_c_date, it may be in Customer table)
0
 
LVL 25

Expert Comment

by:chaau
ID: 39255065
what columns do you want to join?
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39255077
yes all the columns exist.  I will have to keep trying to I can get it to work. Thanks.
0
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 425 total points
ID: 39255105
Your CASE statement is missing an END it should be like this:
SELECT  C.CustomerID,
        C.Name,
        C.Address,
        CASE WHEN INF = 1 THEN 'A'
             WHEN INF = 2 THEN 'B'
        END AS [Inf]
FROM    Customer AS C
        JOIN (SELECT    Inf.Cust_Ky,
                        MAX(CASE WHEN cmp_cd = 'A' THEN 1
                                 WHEN cmp_cd = 'B' THEN 2
                            END) AS INF
              FROM      INF AS I
              WHERE     (stat_cd = 'Y' AND Inf_cd = 'Y')
                        OR (Cust_stat = 'O' AND Inf_Stat = 'O')
                        OR (Cust_Stat = 'N' AND inf_date = cust_c_date)
              GROUP BY  inf.cust_ky
             ) InfSub ON C.Cust_ky = InfSub.Cust_ky
WHERE   C.Cust_ID IN ('123', '124')

Open in new window

0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255118
yep - as acperkins states & visible here
NO points pl
0
 
LVL 3

Author Comment

by:fjkaykr11
ID: 39255138
thanks, great catch. And thanks for the link Paul, that's a handy tool.  Can I ask why do you always put, NO points pl?
0
 
LVL 48

Expert Comment

by:PortletPaul
ID: 39255150
I didn't add anything of much (additional) value and it seems fair to me to ask for no points in such cases. I was just curious and happy to share the link as I find it very useful. It doesn't always locate syntax error but does a pretty fair job.

btw: links provided by the site that are limited in size, you can paste larger queries into that site but you only get a link if it is "smallish"

+ acperkins also offers 'no points please' at times too - so I wish to reciprocate.
0
 
LVL 3

Author Closing Comment

by:fjkaykr11
ID: 39255191
thanks
0

Featured Post

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question