Skip to Content
0
Former Member
Jun 03, 2013 at 04:07 PM

How do you use an alias for a table?

425 Views

Hi. I have an old Crystal Report that I need to modify. The original query is not in a commandtext, but is using the table designer in Crystal. This is the current internal query:

SELECT "DMHISTORY"."WONO", "DMHISTORY"."DESCRIPT", "DMHISTORY"."DATECOMPLETED",
"LABORHD"."ACTHOURS0", "LABORHD"."ACTHOURS1", "LABORHD"."ACTHOURS2", "LABORHD"."ACTHOURS3",
"LABORHD"."ACTHOURS4", "DMHEQUIP"."EQUIPNO", "DMHISTORY"."WORKTYPE",
"DMHEQUIP"."EQUIPNO", "DMHEQUIP"."EQUIPNAME","DMHEQUIP"."LOCATION","DMHEQUIP"."WARDATE",
"DMHEQUIP"."DAILYRUNTIME", "DMHEQUIP"."VENCODE", "DMHEQUIP"."VENNAME", "DMHEQUIP"."CUSTOMERNO",
"DMHEQUIP"."CUSTOMERNAME"
FROM ("LABORHD" "LABORHD" LEFT OUTER JOIN "DMHISTORY" "DMHISTORY" ON "LABORHD"."DMHID"="DMHISTORY"."DMHID")
LEFT OUTER JOIN "DMHEQUIP" "DMHEQUIP" ON "LABORHD"."DMHID"="DMHEQUIP"."DMHID"
ORDER BY "DMHEQUIP"."EQUIPNO", "DMHISTORY"."WORKTYPE", "DMHISTORY"."WONO"

I need to radically change this query. The DMHEQUIP table has two fields that relate to two fields in the EQUIPMENT table. From that record I need a parentid. Then I need to get the record from EQUIPMENT that is associated with this ID.

The representation of this in SQL Management Studio looks like this:

SELECT DM.*, LH.*,DME.EQUIPNO, DME.EQUIPNAME,
E.EQUIPNO AS PARENTNO, E.EQUIPNAME AS PARENTNAME,
DME.LOCATION, DME.WARDATE, DME.DAILYRUNTIME,
DME.VENCODE, DME.VENNAME,
DME.CUSTOMERNO, DME.CUSTOMERNAME
FROM DMHISTORY DM
LEFT OUTER JOIN DMHEQUIP DME ON (DM.DMHID = DME.DMHID)

LEFT OUTER JOIN LABORHD LH ON (DM.DMHID = LH.DMHID)
LEFT OUTER JOIN EQUIPMENT E1 ON (DME.EQUIPNO = E1.EQUIPNO) AND
(DME.EQUIPNAME = E1.EQUIPNAME)
LEFT OUTER JOIN EQUIPMENT E ON (E1.PARENTID = E.EQUIPID)

SQL Management Studio allows me to pull in the same table twice, but use table aliases to indicate fields within them. Note how I use E and E1 in the join and in the field selection. I am using Crystal 11. Is there any way in the Crystal designer to bring in the same table twice like this and use an Alias?

Thanks in Advance for Any Help!