Solved

Invalid field error

Posted on 2009-05-19
4
179 Views
Last Modified: 2012-05-07
I have the query below throwing an error about AMS_Ref being an invalid column name. Can anyone shed some light on this please?

Thanx as always for any advice.
SELECT     RIGHT('00' + CAST(a.ARCD11 AS varchar(2)), 2) + '-' + RIGHT('00000' + CAST(a.RCNO11 AS varchar(5)), 5) AS AMS_Ref , b.CustomerNumber
FROM         dbo.SON_Archive4Sentinel_PTP11 AS a LEFT OUTER JOIN
                      ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS b ON SUBSTRING(AMS_Ref, 1, 8) = b.CustomerNumber
WHERE     (a.EVNO11 = 50)

Open in new window

0
Comment
Question by:Steven O'Neill
[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
4 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 50 total points
ID: 24421544
SELECT     RIGHT('00' + CAST(a.ARCD11 AS varchar(2)), 2) + '-' + RIGHT('00000' + CAST(a.RCNO11 AS varchar(5)), 5) AS AMS_Ref , b.CustomerNumber
FROM         dbo.SON_Archive4Sentinel_PTP11 AS a
LEFT OUTER JOIN  ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS b ON SUBSTRING(RIGHT('00000' + CAST(a.RCNO11 AS varchar(5)), 5), 1, 8) = b.CustomerNumber
WHERE     (a.EVNO11 = 50)
0
 
LVL 57

Accepted Solution

by:
Raja Jegan R earned 450 total points
ID: 24421554
Hope this helps
SELECT RIGHT('00' + CAST(a.ARCD11 AS varchar(2)), 2) + '-' + RIGHT('00000' + CAST(a.RCNO11 AS varchar(5)), 5) AS AMS_Ref , b.CustomerNumber
FROM dbo.SON_Archive4Sentinel_PTP11 AS a 
LEFT OUTER JOIN ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS b ON SUBSTRING(RIGHT('00' + CAST(a.ARCD11 AS varchar(2)), 2) + '-' + RIGHT('00000' + CAST(a.RCNO11 AS varchar(5)), 5), 1, 8) = b.CustomerNumber
WHERE     (a.EVNO11 = 50)

Open in new window

0
 
LVL 31

Expert Comment

by:RiteshShah
ID: 24421557
try this.

SELECT     RIGHT('00' + CAST(a.ARCD11 AS varchar(2)), 2) + '-' + RIGHT('00000' + CAST(a.RCNO11 AS varchar(5)), 5) AS AMS_Ref , b.CustomerNumber
FROM         dbo.SON_Archive4Sentinel_PTP11 AS a LEFT OUTER JOIN
                      ANCHORSERV.S3CUSTDB.dbo.CustomerTable AS b ON SUBSTRING(a.AMS_Ref, 1, 8) = b.CustomerNumber
WHERE     (a.EVNO11 = 50)
0
 
LVL 2

Author Closing Comment

by:Steven O'Neill
ID: 31582974
Thanx again for this guys, thought I could use the ASM_Ref within the FROM condition but obviously couldn't do this. Have given some points to @aneeshattingal as you were on the right track but the query wasn't complete (missing the first part of the SELECT query whilst within the FROM Condition) and @rrjegan17 had the complete query. Thanx again to all.
0

Featured Post

Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

Question has a verified solution.

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

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…
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

729 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