locked
how to reuse the name of column in select sql query RRS feed

  • Question

  • Hi members

    SELECT Codearticle, Count(Codearticle) AS CompteDeQte, Min(PrixRevient) AS [Min1], Max(PrixRevient) AS [Max1],convert(numeric,Max(PrixRevient))/convert(numeric,Min(PrixRevient)) as coefficient
    FROM DetailReceptionFrs WHERE (Numero='st200005')
    GROUP BY Codearticle
    HAVING (Count(Codearticle)>1)

    i want reuse in place of Max(PrixRevient) just put [Max1]

    also i want use it in union select query

    SELECT "BL" AS Type, dbo_F_DOCLIGNE.DO_Date AS [Date], dbo_F_DOCLIGNE.CT_Num AS Client, 
    (dbo_F_COMPTET.CT_Intitule) AS Intitule, dbo_F_DOCLIGNE.DO_Piece AS N, 
    Sum(dbo_F_DOCLIGNE.DL_MontantTTC) AS MontantTTC
    FROM dbo_F_DOCLIGNE INNER JOIN dbo_F_COMPTET ON dbo_F_DOCLIGNE.CT_Num = dbo_F_COMPTET.CT_Num
    WHERE (((dbo_F_DOCLIGNE.DO_Type)=3) AND ((dbo_F_DOCLIGNE.DO_Date)=Date()))
    GROUP BY dbo_F_DOCLIGNE.DO_Date, dbo_F_DOCLIGNE.CT_Num, dbo_F_DOCLIGNE.DO_Piece,dbo_F_COMPTET.CT_Intitule
    ORDER BY dbo_F_DOCLIGNE.DO_Piece
    union
    SELECT "BL: RECAP" AS Type, "" AS [Date], "" AS Client, "" AS Intitule, Count(bl.N) AS N, Sum(bl.MontantTTC) AS MontantTTC
    FROM BL

    i want reuse BL in second select query but thats not work 

    please help how i can manage to use every renamed column or row 

    Saturday, February 1, 2020 10:02 AM

All replies

  • Hi Houssem12,

    You could easily put your query in a WITH CTE AS expression and then SELECT BL from CTE.

    ;with mycte as(
    select ... from ...
    ) 
    select ... from mycte

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    • Proposed as answer by Lily Lii Thursday, February 6, 2020 1:43 AM
    Monday, February 3, 2020 12:16 PM
  • Hi Houssem12,

    May I ask that if your issue has been solved? Please feel free to let us know if you have any other question.

    If you find any post in the thread is helpful, you could kindly mark it as answer. This would benefit the community, and also encourage the community member to keep working on your issues.

    Best Regards,

    Lily


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, February 6, 2020 1:42 AM