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

    Unhappy 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 2003
    Location
    Warren, PA
    Posts
    52
    Try This

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

    Brent

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

    Unhappy Tried - get error

    Tried adding the bracket around year -

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

    however now I get this error:

    ORA-00923: From keyword not found where expected.

    Of course, when I remove the bracket, the AS YEAR gets
    removed, but don't get error.


    Thanks for the suggestion, however did not seem to work.

    I'm willing to try anything else you might suggest....
    Attached Thumbnails Attached Thumbnails error.bmp  
    Last edited by Quaoar; 12-03-03 at 15:43.

Posting Permissions

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