Re: ORA-00932: inconsistent datatypes

"Christina" <huonglu_at_hotmail.com> wrote in message news:40717adb.0304301114.1a3a89b3_at_posting.google.com...
> Hi,
> I used sql statements that embedded in FORTRAN, but I got an error
> message, "ORA-00932: inconsistent datatypes", when I try to run the
> executable file. Here are the sql statements that I wrote in Fortran
> program:
>
>
> SELECT CARRIER.WORKSTATION_NUM, CARRIER.ROUTING_DATE,
> BASKET.BASKET_ID,
> CARRIER.CARRIER_ID, BASKET.BASKET_QTY, BASKET.PART_NUM
> FROM CARRIER, BASKET
> WHERE CARRIER.CARRIER_ID = BASKET.CARRIER_ID
> AND TRANSIT_STATUS_CODE = 'E'
> AND CARRIER.ROUTING_DATE <= (SYSDATE)
> UNION
> SELECT CARRIER.WORKSTATION_NUM, CARRIER.ROUTING_DATE, TO_NUMBER('')
> BASKET_ID,
> CARRIER.CARRIER_ID, TO_NUMBER('') BASKET_QTY, '' PART_NUM
> FROM CARRIER
> WHERE TRANSIT_STATUS_CODE = 'E'
> AND CARRIER.ROUTING_DATE <= (SYSDATE)
> ORDER BY WORKSTATION_NUM
>
> The first query is working fine by itself. I tried to run the second
> query by itself, but still have the same error message, "ORA-00932:
> inconsistent datatypes".
> I'm wondering does the function TO_NUMBER can be use in FORTRAN?
> Is there any one can help? I really appreciated.
>
> Thanks,
> Christina

The function to_number can be used, but is usually not necessary as type conversion is implicit.
You would also better replace your statement by a statement without an Union and with an outer join:

   SELECT CARRIER.WORKSTATION_NUM, CARRIER.ROUTING_DATE,  BASKET.BASKET_ID,
     CARRIER.CARRIER_ID, nvl(BASKET.BASKET_QTY,0), BASKET.PART_NUM    FROM CARRIER, BASKET
   WHERE CARRIER.CARRIER_ID = BASKET.CARRIER_ID(+)

     AND TRANSIT_STATUS_CODE = 'E'
     AND CARRIER.ROUTING_DATE <= (SYSDATE)

That is all you need

-- 
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address