?
Solved

SQL Server Query

Posted on 2013-06-17
13
Medium Priority
?
212 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
Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

 
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

Interactive Way of Training for the AWS CSA Exam

An interactive way of learning that will help you visualize core concepts so that you can be more effective when taking your AWS certification exam.  Built for students by a student to help them understand the concepts that they are being taught.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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 backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Suggested Courses

770 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