[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Server Query

Posted on 2013-06-17
13
Medium Priority
?
213 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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 300 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 1700 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 49

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 49

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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
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.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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