can use Column Alias in column query

[Waqas ali]

dear all,

how can i use red column alias in sub query?

SELECT CDate(Format([ETW],"dd-mmm-yy")) AS D_Date,


YardJobMng.CNTRNo, YardJobMng.Job,


(SELECT format(MAX(ETW),"dd-mmm-yy") FROM YARDJOBmng AS YJ WHERE yj.job = "DS" and yj.CNTRNo = YARDJOBMNG.CNTRNo AND FORMAT(YJ.ETW,"DD-MMM-YY") = YardJobMng.D_Date) AS DS




FROM YardJobMng


GROUP BY CDate(Format([ETW],"dd-mmm-yy")), YardJobMng.CNTRNo, YardJobMng.Job
HAVING (((CDate(Format([ETW],"dd-mmm-yy"))) Between #2/1/2016# And #2/29/2016#) AND ((YardJobMng.CNTRNo)="03650") AND ((YardJobMng.Job) In ("DS")))
ORDER BY CDate(Format([ETW],"dd-mmm-yy")), YardJobMng.CNTRNo;

 

[Joe4]

I don't believe so. I believe that Sub-Queries are typically evaluated before the main query, so that order would not work.

I have actually don't think I have ever seen anyone try to do a Sub Query in the Select clause of a query before. It seems to be a rather odd design to me. I usually see them in the WHERE clause.

 

[xenou]

Assuming your dates are all true dates, stored the same way with the same data type, etc. it will probably be better to leave out all that formatting as it's unnecessary for the accuracy of the query and may slow it down. If you aren't storing dates as dates or are storing them with different data types then of course you have to do what you can to make it work.

I believe Joe is correct that the alias of the column won't work in the subquery but of course you can just try it and see what happens.

 

[xenou]

Note that in your original query, you can just use the unaliased column name (possibly) (but like Joe said it might be necessary to move it into a where clause, where you would also use the unaliased column name to make it work):

SELECT CDate(Format([ETW],"dd-mmm-yy")) AS D_Date,


YardJobMng.CNTRNo, YardJobMng.Job,


(
SELECT format(MAX(ETW),"dd-mmm-yy") FROM YARDJOBmng AS YJ WHERE yj.job = "DS" and yj.CNTRNo = YARDJOBMNG.CNTRNo AND FORMAT(YJ.ETW,"DD-MMM-YY") = CDate(Format(YardJobMng.[ETW],"dd-mmm-yy"))
) AS DS