Refer to alias in the same view

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Refer to alias in the same view

Author

Topic

Chaoboy
Starting Member

15 Posts

Posted - 2010-02-15 : 17:29:36

Hello, I am trying to move a query from access to sql. in Access you can refer to an alias created in the same query, apperently this is not done the same way in SQL. here is my code.

INSERT INTO [Reference: MRP 44]
( InwhStat, DNR, IntbGrup, Item, InitDesc1, QOH1, PO1, FCT1, QOH2, PO2, FCT2, QOH3, PO3, FCT3, QOH4, PO4, FCT4,
QOH5, PO5, FCT5, QOH6, PO6, FCT6, QOH7, PO7, FCT7, QOH8, PO8, FCT8, QOH9, PO9, FCT9, QOH10, PO10, FCT10,
QOH11, PO11, FCT11, QOH12, PO12, FCT12, QOH13, PO13, FCT13, QOH14, PO14, FCT14, QOH15, PO15, FCT15,
QOH16, PO16, FCT16, QOH17, PO17, FCT17, QOH18, PO18, FCT18, QOH19, PO19, FCT19, QOH20, PO20, FCT20,
QOH21, PO21, FCT21, QOH22, PO22, FCT22, QOH23, PO23, FCT23, QOH24, PO24, FCT24, QOH25, PO25, FCT25,
QOH26, PO26, FCT26, QOH27, PO27, FCT27, QOH28, PO28, FCT28, QOH29, PO29, FCT29, QOH30, PO30, FCT30,
QOH31, PO31, FCT31, QOH32, PO32, FCT32, QOH33, PO33, FCT33, QOH34, PO34, FCT34, QOH35, PO35, FCT35,
QOH36, PO36, FCT36, QOH37, PO37, FCT37, QOH38, PO38, FCT38, QOH39, PO39, FCT39, QOH40, PO40, FCT40,
QOH41, PO41, FCT41, QOH42, PO42, FCT42, QOH43, PO43, FCT43, QOH44, PO44, FCT44 )
SELECT [Index: Whse 01 Status].Status AS InwhStat, "Step Query: Reference-MRP 44 (Step 02)".DNR,
"Step Query: Reference-MRP 44 (Step 02)".IntbGrup, "Step Query: Reference-MRP 44 (Step 02)".Item,
"Step Query: Reference-MRP 44 (Step 02)".InitDesc1, "Step Query: Reference-MRP 44 (Step 02)".QOH1,
"Step Query: Reference-MRP 44 (Step 02)".PO1, "Step Query: Reference-MRP 44 (Step 02)".FCT1, [QOH1]+[PO1]-[Fct1] AS QOH2,
"Step Query: Reference-MRP 44 (Step 02)".PO2, "Step Query: Reference-MRP 44 (Step 02)".FCT2, [QOH2]+[PO2]-[FCT2] AS QOH3,
"Step Query: Reference-MRP 44 (Step 02)".PO3, "Step Query: Reference-MRP 44 (Step 02)".FCT3, [QOH3]+[PO3]-[FCT3] AS QOH4,
"Step Query: Reference-MRP 44 (Step 02)".PO4, "Step Query: Reference-MRP 44 (Step 02)".FCT4, [QOH4]+[PO4]-[FCT4] AS QOH5,
"Step Query: Reference-MRP 44 (Step 02)".PO5, "Step Query: Reference-MRP 44 (Step 02)".FCT5, [QOH5]+[PO5]-[FCT5] AS QOH6,
"Step Query: Reference-MRP 44 (Step 02)".PO6, "Step Query: Reference-MRP 44 (Step 02)".FCT6, [QOH6]+[PO6]-[FCT6] AS QOH7,
"Step Query: Reference-MRP 44 (Step 02)".PO7, "Step Query: Reference-MRP 44 (Step 02)".FCT7, [QOH7]+[PO7]-[FCT7] AS QOH8,
"Step Query: Reference-MRP 44 (Step 02)".PO8, "Step Query: Reference-MRP 44 (Step 02)".FCT8, [QOH8]+[PO8]-[FCT8] AS QOH9,
"Step Query: Reference-MRP 44 (Step 02)".PO9, "Step Query: Reference-MRP 44 (Step 02)".FCT9, [QOH9]+[PO9]-[FCT9] AS QOH10,
"Step Query: Reference-MRP 44 (Step 02)".PO10, "Step Query: Reference-MRP 44 (Step 02)".FCT10, [QOH10]+[PO10]-[FCT10] AS QOH11,
"Step Query: Reference-MRP 44 (Step 02)".PO11, "Step Query: Reference-MRP 44 (Step 02)".FCT11, [QOH11]+[PO11]-[FCT11] AS QOH12,
"Step Query: Reference-MRP 44 (Step 02)".PO12, "Step Query: Reference-MRP 44 (Step 02)".FCT12, [QOH12]+[PO12]-[FCT12] AS QOH13,
"Step Query: Reference-MRP 44 (Step 02)".PO13, "Step Query: Reference-MRP 44 (Step 02)".FCT13, [QOH13]+[PO13]-[FCT13] AS QOH14,
"Step Query: Reference-MRP 44 (Step 02)".PO14, "Step Query: Reference-MRP 44 (Step 02)".FCT14, [QOH14]+[PO14]-[FCT14] AS QOH15,
"Step Query: Reference-MRP 44 (Step 02)".PO15, "Step Query: Reference-MRP 44 (Step 02)".FCT15, [QOH15]+[PO15]-[FCT15] AS QOH16,
"Step Query: Reference-MRP 44 (Step 02)".PO16, "Step Query: Reference-MRP 44 (Step 02)".FCT16, [QOH16]+[PO16]-[FCT16] AS QOH17,
"Step Query: Reference-MRP 44 (Step 02)".PO17, "Step Query: Reference-MRP 44 (Step 02)".FCT17, [QOH17]+[PO17]-[FCT17] AS QOH18,
"Step Query: Reference-MRP 44 (Step 02)".PO18, "Step Query: Reference-MRP 44 (Step 02)".FCT18, [QOH18]+[PO18]-[FCT18] AS QOH19,
"Step Query: Reference-MRP 44 (Step 02)".PO19, "Step Query: Reference-MRP 44 (Step 02)".FCT19, [QOH19]+[PO19]-[FCT19] AS QOH20,
"Step Query: Reference-MRP 44 (Step 02)".PO20, "Step Query: Reference-MRP 44 (Step 02)".FCT20, [QOH20]+[PO20]-[FCT20] AS QOH21,
"Step Query: Reference-MRP 44 (Step 02)".PO21, "Step Query: Reference-MRP 44 (Step 02)".FCT21, [QOH21]+[PO21]-[FCT21] AS QOH22,
"Step Query: Reference-MRP 44 (Step 02)".PO22, "Step Query: Reference-MRP 44 (Step 02)".FCT22, [QOH22]+[PO22]-[FCT22] AS QOH23,
"Step Query: Reference-MRP 44 (Step 02)".PO23, "Step Query: Reference-MRP 44 (Step 02)".FCT23, [QOH23]+[PO23]-[FCT23] AS QOH24,
"Step Query: Reference-MRP 44 (Step 02)".PO24, "Step Query: Reference-MRP 44 (Step 02)".FCT24, [QOH24]+[PO24]-[FCT24] AS QOH25,
"Step Query: Reference-MRP 44 (Step 02)".PO25, "Step Query: Reference-MRP 44 (Step 02)".FCT25, [QOH25]+[PO25]-[FCT25] AS QOH26,
"Step Query: Reference-MRP 44 (Step 02)".PO26, "Step Query: Reference-MRP 44 (Step 02)".FCT26, [QOH26]+[PO26]-[FCT26] AS QOH27,
"Step Query: Reference-MRP 44 (Step 02)".PO27, "Step Query: Reference-MRP 44 (Step 02)".FCT27, [QOH27]+[PO27]-[FCT27] AS QOH28,
"Step Query: Reference-MRP 44 (Step 02)".PO28, "Step Query: Reference-MRP 44 (Step 02)".FCT28, [QOH28]+[PO28]-[FCT28] AS QOH29,
"Step Query: Reference-MRP 44 (Step 02)".PO29, "Step Query: Reference-MRP 44 (Step 02)".FCT29, [QOH29]+[PO29]-[FCT29] AS QOH30,
"Step Query: Reference-MRP 44 (Step 02)".PO30, "Step Query: Reference-MRP 44 (Step 02)".FCT30, [QOH30]+[PO30]-[FCT30] AS QOH31,
"Step Query: Reference-MRP 44 (Step 02)".PO31, "Step Query: Reference-MRP 44 (Step 02)".FCT31, [QOH31]+[PO31]-[FCT31] AS QOH32,
"Step Query: Reference-MRP 44 (Step 02)".PO32, "Step Query: Reference-MRP 44 (Step 02)".FCT32, [QOH32]+[PO32]-[FCT32] AS QOH33,
"Step Query: Reference-MRP 44 (Step 02)".PO33, "Step Query: Reference-MRP 44 (Step 02)".FCT33, [QOH33]+[PO33]-[FCT33] AS QOH34,
"Step Query: Reference-MRP 44 (Step 02)".PO34, "Step Query: Reference-MRP 44 (Step 02)".FCT34, [QOH34]+[PO34]-[FCT34] AS QOH35,
"Step Query: Reference-MRP 44 (Step 02)".PO35, "Step Query: Reference-MRP 44 (Step 02)".FCT35, [QOH35]+[PO35]-[FCT35] AS QOH36,
"Step Query: Reference-MRP 44 (Step 02)".PO36, "Step Query: Reference-MRP 44 (Step 02)".FCT36, [QOH36]+[PO36]-[FCT36] AS QOH37,
"Step Query: Reference-MRP 44 (Step 02)".PO37, "Step Query: Reference-MRP 44 (Step 02)".FCT37, [QOH37]+[PO37]-[FCT37] AS QOH38,
"Step Query: Reference-MRP 44 (Step 02)".PO38, "Step Query: Reference-MRP 44 (Step 02)".FCT38, [QOH38]+[PO38]-[FCT38] AS QOH39,
"Step Query: Reference-MRP 44 (Step 02)".PO39, "Step Query: Reference-MRP 44 (Step 02)".FCT39, [QOH39]+[PO39]-[FCT39] AS QOH40,
"Step Query: Reference-MRP 44 (Step 02)".PO40, "Step Query: Reference-MRP 44 (Step 02)".FCT40, [QOH40]+[PO40]-[FCT40] AS QOH41,
"Step Query: Reference-MRP 44 (Step 02)".PO41, "Step Query: Reference-MRP 44 (Step 02)".FCT41, [QOH41]+[PO41]-[FCT41] AS QOH42,
"Step Query: Reference-MRP 44 (Step 02)".PO42, "Step Query: Reference-MRP 44 (Step 02)".FCT42, [QOH42]+[PO42]-[FCT42] AS QOH43,
"Step Query: Reference-MRP 44 (Step 02)".PO43, "Step Query: Reference-MRP 44 (Step 02)".FCT43, [QOH43]+[PO43]-[FCT43] AS QOH44,
"Step Query: Reference-MRP 44 (Step 02)".PO44, "Step Query: Reference-MRP 44 (Step 02)".FCT44
FROM "Step Query: Reference-MRP 44 (Step 02)" LEFT JOIN [Index: Whse 01 Status] ON "Step Query: Reference-MRP 44 (Step 02)".Item = [Index: Whse 01 Status].InitItemNbr;

can anyone tell me how to convert the calculations I do in here to SQL? Thanks fotr any help you can give

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-16 : 00:38:10

you need to wrap the main query into a derived table and then you can access the calculations as columns in sql . something like


SELECT col1,col2,..
FROM
(
SELECT calculations... AS col1,
calculations... AS col2,
..
FROM table
...
)t

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources