Solved

Concatenate fields and use the Alias field to link another field in another table

Posted on 2012-12-31

Microsoft SQL Server

1 solution

253 Views

Last Modified: 2013-02-22

I have two tables that I would like to link together for making labels. I would like to link the tables together to get my results. The issue lies when I inner join the tables. table a has two fields and table b has one field that I need to link together to get my results. I was able to get the alias to link the tables together, but I get stuck on how to link an alias to another table in the same query.

Here is the tables and my query
Work_Order
Base_ID Lot_ID Part_ID Printed_Date
WO-00009 1 112A7000 12/31/2012

Trace
Part_ID AProperty_1 SN
112A7000 WO-00009/1 SN1223

I would like the results to be
Part_ID SN Printed_Date
112A7000 SN1223 12/31/2012

I think my issue is with the Alias . .

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID, WORK_ORDER.PART_ID, [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] AS WO
FROM WORK_ORDER
INNER JOIN TRACE ON WORK_ORDER.PART_ID = TRACE.PART_ID AND WO = TRACE.APROPERTY_1
WHERE WORK_ORDER.PRINTED_DATE > 12/31/12

Thanks so much!! Happy New Year!!

Comment

Question by:DannerCorp

[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

Join The Community

5 Comments

LVL 12

Expert Comment

by:Saurabh Bhadauria

ID: 387339362012-12-31

Try like this.... You can not use aliases in where clause....need to write full expression

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID, WORK_ORDER.PART_ID, [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] AS WO
FROM WORK_ORDER
INNER JOIN TRACE ON WORK_ORDER.PART_ID = TRACE.PART_ID AND [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] = TRACE.APROPERTY_1
WHERE WORK_ORDER.PRINTED_DATE > 12/31/12

Open in new window

LVL 39

Expert Comment

by:Pratima Pharande

ID: 387339632012-12-31

You said you need result like this
Part_ID SN Printed_Date
112A7000 SN1223 12/31/2012

for this result Query will be

Select Work_Order.Part_ID,SN ,Printed_Date From Work_Order
inner join Trace on Work_Order.Part_Id = Trace.Part_ID

To Correct your given query try this

SELECT WORK_ORDER.BASE_ID, WORK_ORDER.LOT_ID, WORK_ORDER.PART_ID, [WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID] AS WO
FROM WORK_ORDER
INNER JOIN TRACE ON WORK_ORDER.PART_ID = TRACE.PART_ID AND ([WORK_ORDER].[BASE_ID]+ '/' + [WORK_ORDER].[LOT_ID]) = TRACE.APROPERTY_1
WHERE WORK_ORDER.PRINTED_DATE > '12/31/12'

LVL 7

Expert Comment

by:armchang

ID: 387339802012-12-31

You certainly cannot use a column alias but you could try using subquery aliases if you're heavily using custom columns on your queries like this:

SELECT A.BASE_ID, A.LOT_ID, A.PART_ID, A.WO FROM 
(SELECT *, [WORKORDER].[BASE_ID]+ '/' + [WORKORDER].[LOT_ID] AS WO
FROM WORKORDER) A
INNER JOIN TRACE ON A.PART_ID = TRACE.PART_ID AND A.WO = TRACE.APROPERTY_1
WHERE A.PRINTED_DATE > 12/31/12

Open in new window

A is the subquery alias and replaces the Work_Order table on the outer query.

LVL 32

Expert Comment

by:awking00

ID: 387371562013-01-02

select t.part_id, t.sn, x.printed_date
from trace t,
(select base_id + '/' + lot_id as aproperty_1, part_id, printed_date
from work_order
where x.printed_date > 12/31/2012) as x
where t.part_id = x.part_id and t.aproperty_1 = c.aproperty_1

LVL 7

Accepted Solution

by:armchang

armchang earned 500 total points

ID: 387421032013-01-03

Though if the query I've posted is not working you can add apostrophe's or specify a long date format:

SELECT A.BASE_ID, A.LOT_ID, A.PART_ID, A.WO FROM 
(SELECT *, [WORKORDER].[BASE_ID]+ '/' + [WORKORDER].[LOT_ID] AS WO
FROM WORKORDER) A
INNER JOIN TRACE ON A.PART_ID = TRACE.PART_ID AND A.WO = TRACE.APROPERTY_1
WHERE A.PRINTED_DATE > '12/31/12'

Open in new window

SELECT A.BASE_ID, A.LOT_ID, A.PART_ID, A.WO FROM 
(SELECT *, [WORKORDER].[BASE_ID]+ '/' + [WORKORDER].[LOT_ID] AS WO
FROM WORKORDER) A
INNER JOIN TRACE ON A.PART_ID = TRACE.PART_ID AND A.WO = TRACE.APROPERTY_1
WHERE A.PRINTED_DATE > '2012-12-31'

Open in new window

Featured Post

Question has a verified solution.

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

How to Reset a Lost SA Password in Microsoft SQL Server

Article by: Joseph

This article explains how to reset the password of the sa account on a Microsoft SQL Server. The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.

The world is on the move: Electronic commerce to Connected commerce

Article by: Shakshi

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.

Connecting To SQL Server From Oracle Using Heterogeneous Services Generic Gateway

Video by: Steve

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.