G
Greg Bell
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?
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?