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

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:42:37 CET