Solved

SQL Server Query

Posted on 2013-06-17
13
207 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 24

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 24

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
 
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 24

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 24

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

746 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now