Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    7

    Angry Unanswered: Excel XP removes AS part of statement

    I am trying to help an individual with a MS Query.

    In Excel 97 MS Query the script was written:

    TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS YEAR

    In Excel XP I can run an existing Query and it returns the data as expected, but when I view the query it is:

    TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'

    However if I try to create a new query using XP MS Query then the AS YEAR or AS 'YEAR' will be removed by MS Query and my column header will now be:

    TO_CHAR(LABTRANS.STARTDATE, 'YYYY')

    Here are my examples:

    MS Excel 97 Query

    SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC,
    TO_CHAR(LABTRANS.STARTDATE, 'YYYY') AS 'YEAR'
    FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
    WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
    AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
    AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE

    MS Excel XP Query

    SELECT LABTRANS.LABORCODE, LABTRANS.REGULARHRS, LABTRANS.OTHRS, LABTRANS.OTSCALE, LABTRANS.STARTDATE, LABTRANS.TRANSTYPE, VALUELIST.VALDESC,
    TO_CHAR(LABTRANS.STARTDATE, 'YYYY')
    FROM MAXIMO.LABTRANS LABTRANS, MAXIMO.VALUELIST VALUELIST
    WHERE LABTRANS.TRANSTYPE = VALUELIST.VALUE
    AND (LABTRANS.TRANSTYPE IN (SELECT VALUE FROM VALUELIST WHERE LISTNAME = 'LTTYPE' AND MAXVALUE = 'NON-WORK'))
    AND LABTRANS.LABORCODE IN (SELECT LABORCODE FROM LABOR WHERE LABOR.TYPE = 'CUPE' AND LABOR.CREWID IS NOT NULL AND LABOR.LA9 = 'N' AND LABOR.LA16 = 'WW' OR LABORCODE = '210308') AND VALUELIST.MAXVALUE = 'NON-WORK' ORDER BY LABTRANS.STARTDATE

    In Excel XP, no matter what I put in the AS part of the statement when I execute the script it runs and removes the AS part from the statement. This only happens when I run the script from MS Query, if I stay in Excel and just refresh the data, then it works fine.

    I did not have this problem in Excel 97.

    Any suggestions?

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    I think your linking to a wrong database

    TO_CHAR is an Oracle Function...not SQL Server

    CONVERT(varchar(4),Col1) is SQL Server
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Nov 2003
    Location
    Canada
    Posts
    7

    Red face Your right

    Your right, he is connecting to an Oracle server. I placed this topic under the wrong heading.....

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •