SQL counter flag is not filtered-CodePudding

I'm using SQL developer and I'm getting an error I don't fully understand.

Due to data privacy, I cannot share the real query, but the question is fairly simple.

I have a query to obtain table T (which works if ran alone) and I want to filter a Y/N flag so I have the following query:

select * from T where flag ='Y'

While this query works, I want the observations which do not fullfil the condition but neither of the following queries works:

select * from T where flag <>'Y'

select * from T where flag ='N'

*I get the following error: 01722. 00000 - "invalid number" *Cause: The specified number was invalid. Action: Specify a valid number . Vendor code 1722

I tried to export the table to analyze it on another language, but I still get the same error.

Can anyone help?

CodePudding user response：

As you confirmed T in your example is not a table, your error simply seems to be caused by a conversion error somewhere on 1 or several records WHERE flag <> 'Y'. This is why the query works with flag = 'Y' but not with your other conditions.

Minimal case to reproduce would be something like:

CREATE TABLE MyTable (
    Field VARCHAR(10),
    Flag VARCHAR(1)
);
CREATE VIEW T AS SELECT to_number(Field), Flag FROM MyTable;
INSERT INTO MyTable VALUES ('1', 'Y'); /* OK */
INSERT INTO MyTable VALUES ('No way', 'N'); /* Fails */

In your case, the error may not be caused by a call to to_number but this is the best clue I can give given the limited information you provide. It might not be a call to a function at all but the result of a calculation using operators only.

You simply need to make sure the fields can be converted/used in a calculation before you actually attempt to do so (CASE WHEN ... THEN ... ELSE NULL for instance).