CODEWITHSUNDEEP
sqloracle-database
Prarrior
Prarrior

Reputation: 21

SQL Oracle 01722 Invalid Number

Iam a SQL newbie but I need to rewrite something to get data from several databases.

First, the code I try to execute:

SELECT aeabwbbn
       || aeeancode                           AS EAN,
       ( lglagerbestand - lgauftragsbestand ) AS BESTAND,
       aeform
       || aequal
       || aefb                                AS ARTIKELNUMMER,
       aefb,
       lgfb
FROM   lagerbestand
       join artikelean
         ON lgfirma = artikelean.aefirma
            AND lgform = artikelean.aeform
            AND lgqual = artikelean.aequal
            AND lggroesse = artikelean.aegroesse
            AND lgfb = artikelean.aefb

the problem is the last row. Everything is working fine till I inclute the last row. I thought it is because it has a different format, but every output is numeric. There are just numbers in this query.

So I tried CAST(LGFB AS INTEGER) = ARTIKELEAN.AEFB and LGFB = CAST(ARTIKELEAN.AEFB AS INTEGER) but that didn't work neither.

What could cause this problem?

Upvotes: 0

Views: 45

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271161

If one value is a number and the other a string, then Oracle (and SQL in general) will do the comparison as a number -- causing the conversion problem.

The simplest method is to convert the values to strings rather than integers:

TO_CHAR(LGFB) = TO_CHAR(ARTIKELEAN.AEFB)

Note that this might not do what you actually want. For instance, one value might be '0001' and the other 1 and these would not be equal as strings.

Upvotes: 2

Related Questions