• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

SQL Server Query

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
fjkaykr11
Asked:
fjkaykr11
  • 6
  • 4
  • 2
  • +1
2 Solutions
 
chaauCommented:
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
 
fjkaykr11Author Commented:
thanks for replying I am still getting the same error when I try that.
0
 
chaauCommented:
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
fjkaykr11Author Commented:
there is the column name is customer table.  I can't post the data.
0
 
fjkaykr11Author Commented:
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
 
chaauCommented:
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
 
chaauCommented:
what columns do you want to join?
0
 
fjkaykr11Author Commented:
yes all the columns exist.  I will have to keep trying to I can get it to work. Thanks.
0
 
Anthony PerkinsCommented:
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
 
PortletPaulCommented:
yep - as acperkins states & visible here
NO points pl
0
 
fjkaykr11Author Commented:
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
 
PortletPaulCommented:
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
 
fjkaykr11Author Commented:
thanks
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 6
  • 4
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now