Announcement

Collapse
No announcement yet.

Duplicate Output Alias

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Duplicate Output Alias

    Hi,

    I am having problems with editing a cross tab query. I need to add another column into the cross tab and the column heading is in the data that i am using. I have looked at the SQL code and it is as follows:

    Code:
    TRANSFORM Count(Tbl_BASIS_STRS_RPRT.INUMBR) AS [# of OOS]
    SELECT Date() AS [Date], Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM, Count(Tbl_BASIS_STRS_RPRT.INUMBR) AS [# of OOS]
    FROM Tbl_BASIS_STRS_RPRT LEFT JOIN [Store Table] ON Tbl_BASIS_STRS_RPRT.ISTORE = [Store Table].STRNUM
    WHERE (((Tbl_BASIS_STRS_RPRT.[OOS Stores w Sls])>0))
    GROUP BY Date(), Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM
    ORDER BY Tbl_BASIS_STRS_RPRT.ISTORE
    PIVOT Tbl_BASIS_STRS_RPRT.E3SPCD In ("DAV","MKS","DSD");
    The extra colum heading i am trying to add is "WTP" but when i add that in after the DSD then it comes up with the error meesage:

    "Duplicate output alias '# of oos'

    Thanks for any help in advance.

  • #2
    Re: Duplicate Output Alias

    Give this a Shot

    TRANSFORM Count(Tbl_BASIS_STRS_RPRT.INUMBR) As [# of OOS]
    SELECT Date() As [Date], Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM
    FROM Tbl_BASIS_STRS_RPRT LEFT JOIN [Store Table] ON Tbl_BASIS_STRS_RPRT.ISTORE = [Store Table].STRNUM
    WHERE (((Tbl_BASIS_STRS_RPRT.[OOS Stores w Sls])>0))
    GROUP BY Date(), Tbl_BASIS_STRS_RPRT.ISTORE, [Store Table].STRNAM
    ORDER BY Tbl_BASIS_STRS_RPRT.ISTORE
    PIVOT Tbl_BASIS_STRS_RPRT.E3SPCD In ("DAV","MKS","DSD");
    I've taken out Count(Tbl_BASIS_STRS_RPRT.INUMBR) As [# of OOS] From SELECT as this is what was causing the Duplicate Alias

    Think of the CrossTab Query Like this...

    The TRANSFORM syntax is your Values.
    SELECT is your Row Headings (Fields)
    PIVOT is your Column Headings

    Comment


    • #3
      Re: Duplicate Output Alias

      Excellent. That works a treat!

      How come it was working as it was with the As [# of OOS] in the SELECT statement without adding the extra column heading?

      Its working fine now, just trying to get the understanding of it as well as the solution.

      Thanks again

      Dave

      Comment


      • #4
        Re: Duplicate Output Alias

        Because it's a Cross tab query you transform any Sum, Count, Avg etc as a value. In a normal SELECT statement you can just add Count(Fields)...

        z

        Comment

        Working...
        X