Re: [Microsoft][ODBC driver for Oracle][Oracle] ORA-00904: invalid column name

I don't know much about this problem, but I stopped using MS driver, because it rounded down all amount type fields from Oracle i.e. $40.20 became $40.00. Try using Oracle ODBC driver instead.

HTH Michael

Christina wrote:

>Hi,
>
>I got a problem with the SQL statements when I tried to use them to
>print out the recordsets onto the Crystal Report with Visual Basic. I
>had 3 queries to work with, when I tried to use 1 query as a time, I
>be able to retrieve the recordsets back. But couldn't when I tried to
>retrieve recordsets by combining 3 queries and using UNION operator to
>join them,
> here is what I got as the runtime error:
>
> "[Microsoft][ODBC driver for Oracle][Oracle]ORA-00904: invalid
>column name - [my class.function name, Error Number: - 2147217900]."
>
> Here are my queries in Visual Basic:
> sSQL = "SELECT TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY')INACTIVE_SINCE_DATE, " & _
> "TO_NUMBER(TO_CHAR(TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY'), 'J')) INACTIVE_SINCE_JULIAN_DATE, " & _
> "TO_NUMBER(TO_CHAR(TRUNC(TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY'), 'YEAR'), 'J')) INACTIVE_SINCE_JAN_1_JULIAN, " & _
> "BASKET.BASKET_TYPE_CODE, BASKET.BASKET_ID,
>BASKET.LAST_ACTIVITY_DATE, " & _
> "TO_NUMBER(TO_CHAR(BASKET.LAST_ACTIVITY_DATE, 'J'))
>JULIAN_DATE, " & _
> "TO_NUMBER(TO_CHAR(TRUNC(BASKET.LAST_ACTIVITY_DATE, 'YEAR'),
>'J')) JAN_1_JULIAN_DATE, " & _
> "BASKET.SIZE_CODE, BASKET.CARRIER_ID, BASKET.PART_NUM, 0 As
>EMPTY_IND, BASKET_QTY, " & _
> "TO_NUMBER(STORAGE_LOCATION.ASRS_Aisle_Num) As
>ASRS_AISLE_NUM, TO_NUMBER(STORAGE_LOCATION.BAY_NUM) As BAY_NUM, " & _
> "TO_NUMBER(STORAGE_LOCATION.TIER_NUM) As TIER_NUM,
>STORAGE_LOCATION.SIDE_CODE As SIDE_CODE, ' ' As
>Stor_Loc_String " & _
> "FROM basket, carrier, storage_location " & _
> "WHERE RTRIM(BASKET.BASKET_TYPE_CODE) IN (" &
>sDelimitedBasketTypeCodes & ") " & _
> "AND BASKET.LAST_ACTIVITY_DATE <= TO_DATE('" &
>Format$(Trim$(Str(dInactiveSince)), "dd-mm-yyyy") & "', 'DD-MM-YYYY')
>" & _
> "AND CARRIER.CARRIER_ID = BASKET.CARRIER_ID " & _
> "AND STORAGE_LOCATION.LOCATION_ID = CARRIER.LOCATION_ID "
> sSQL = sSQL & "UNION "
> sSQL = sSQL & "SELECT TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY') INACTIVE_SINCE_DATE, " & _
> "TO_NUMBER(TO_CHAR(TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY'), 'J')) INACTIVE_SINCE_JULIAN_DATE, " & _
> "TO_NUMBER(TO_CHAR(TRUNC(TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY'), 'YEAR'), 'J')) INACTIVE_SINCE_JAN_1_JULIAN, " & _
> "BASKET.BASKET_TYPE_CODE, BASKET.BASKET_ID,
>BASKET.LAST_ACTIVITY_DATE, " & _
> "TO_NUMBER(TO_CHAR(BASKET.LAST_ACTIVITY_DATE, 'J'))
>JULIAN_DATE, " & _
> "TO_NUMBER(TO_CHAR(TRUNC(BASKET.LAST_ACTIVITY_DATE, 'YEAR'),
>'J')) JAN_1_JULIAN_DATE, " & _
> "BASKET.SIZE_CODE, BASKET.CARRIER_ID, BASKET.PART_NUM, 0 As
>EMPTY_IND, BASKET_QTY, " & _
> "TO_NUMBER('') As ASRS_AISLE_NUM, TO_NUMBER('') As BAY_NUM,
>" & _
> "TO_NUMBER('') As TIER_NUM, '' As SIDE_CODE, ' ' As
>Stor_Loc_String " & _
> "FROM basket " & _
> "WHERE RTRIM(BASKET.BASKET_TYPE_CODE) IN (" &
>sDelimitedBasketTypeCodes & ") " & _
> "AND BASKET.LAST_ACTIVITY_DATE <= TO_DATE('" &
>Format$(Trim$(Str(dInactiveSince)), "dd-mm-yyyy") & "', 'DD-MM-YYYY')
>" & _
> "AND BASKET.CARRIER_ID IS NULL "
>
> sSQL = sSQL & "UNION "
> sSQL = sSQL & "SELECT TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY') INACTIVE_SINCE_DATE, " & _
> "TO_NUMBER(TO_CHAR(TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY'), 'J')) INACTIVE_SINCE_JULIAN_DATE, " & _
> "TO_NUMBER(TO_CHAR(TRUNC(TO_DATE('" & sInactiveSince & "',
>'DD-MM-YYYY'), 'YEAR'), 'J')) INACTIVE_SINCE_JAN_1_JULIAN, " & _
> "BASKET.BASKET_TYPE_CODE, BASKET.BASKET_ID,
>BASKET.LAST_ACTIVITY_DATE, " & _
> "TO_NUMBER(TO_CHAR(BASKET.LAST_ACTIVITY_DATE, 'J'))
>JULIAN_DATE, " & _
> "TO_NUMBER(TO_CHAR(TRUNC(BASKET.LAST_ACTIVITY_DATE, 'YEAR'),
>'J')) JAN_1_JULIAN_DATE, " & _
> "BASKET.SIZE_CODE, BASKET.CARRIER_ID, BASKET.PART_NUM, 0 As
>EMPTY_IND, BASKET_QTY, " & _
> "TO_NUMBER('') As ASRS_AISLE_NUM, TO_NUMBER('') As BAY_NUM,
>" & _
> "TO_NUMBER('') As TIER_NUM, '' As SIDE_CODE, ' ' As
>Stor_Loc_String " & _
> "FROM basket, carrier " & _
> "WHERE RTRIM(BASKET.BASKET_TYPE_CODE) IN (" &
>sDelimitedBasketTypeCodes & ") " & _
> "AND BASKET.LAST_ACTIVITY_DATE <= TO_DATE('" &
>Format$(Trim$(Str(dInactiveSince)), "dd-mm-yyyy") & "', 'DD-MM-YYYY')
>" & _
> "AND CARRIER.LOCATION_ID IS NULL " & _
> "AND CARRIER.CARRIER_ID = BASKET.CARRIER_ID "
>
> Can anyone help me to solve this problem? I couldn't figure how and
>why? because when I tried to use them individually, it worked perfect,
>but couldn't when used UNION to combine them.
>
>Thanks,
>Christina
>
>
Received on Tue Mar 11 2003 - 00:53:12 CET