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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote

    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
  • Question
    Sign in to vote
    0
    Sign in to vote

    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