logo
down
shadow

adding years getting invalid number error in case statement only - ORA-01722


adding years getting invalid number error in case statement only - ORA-01722

By : Nithu Alachery
Date : October 22 2020, 08:10 PM
With these it helps Forgive me as I am new to this. I am trying to add a year to a date in a query. The year to be added is based on the month/day in the database. If the date is prior to November 1st then the year will be 2017 if it is after November 1st then it will be 2018. I have tried this a few ways (see below) and can get the years added in a query but when I put them in a case statement I get the 'Invalid Number' error. , If AU.CREAT_TS is DATE, this works OK (meaning: it doesn't fail):
code :
SQL> with au (creat_ts) as
  2    (select date '2018-12-05' from dual
  3    )
  4  select
  5    CASE WHEN to_char(au.creat_ts, 'MMDD') >= to_char(to_date('11/01/2018', 'dd/mm/yyyy'), 'MMDD') THEN
  6              trunc(add_months(au.creat_ts, floor(months_between(SYSDATE, au.creat_ts) / 12) * 12))
  7         ELSE trunc(add_months(au.creat_ts, (floor(months_between(SYSDATE, au.creat_ts) / 12) - 1) * 12))
  8    END calcx
  9  from au
 10  ;

CALCX
-------------------
05.12.2018 00:00:00

SQL>
  CASE WHEN to_char(au.creat_ts, 'MMDD') >= to_char('11/01/2018', 'MMDD') THEN 


Share : facebook icon twitter icon
ERROR java.sql.SQLException: ORA-01722: invalid number while running a Prepared Statement to alter a Sequence

ERROR java.sql.SQLException: ORA-01722: invalid number while running a Prepared Statement to alter a Sequence


By : user3016432
Date : March 29 2020, 07:55 AM
will help you You can't use bind variables with DDL, such as ALTER SEQUENCE. You'll have to concatenate incval onto the string.
There shouldn't be any risk of SQL injection if incval is an int or a long.
Update table by case ORA-01722 Invalid Number

Update table by case ORA-01722 Invalid Number


By : user3310234
Date : March 29 2020, 07:55 AM
wish helps you The only way this statement can fail with ORA-01722 is if Col is a numeric column. '4', '35' and '36' can be automatically converted to a number. However, if none of the case conditions are met, and you end up evaluating the else branch, it will return the string Missing that cannot be converted to a number.
One way to deal with it could be to use null, which was designed to signify a missing value instead:
code :
UPDATE TableT SET 
   Col = (CASE when PER in ('1234','2134','2314','3214') AND TYPE = 4 then 4 
                     when PER in ('34','104','1004') AND TYPE = 4 then 35 
                     when PER in ('124','1204','2014') AND TYPE = 4  then 36
               ELSE NULL  
          END);
ORA-01722: invalid number in Case statement

ORA-01722: invalid number in Case statement


By : Sam
Date : March 29 2020, 07:55 AM
may help you . You didn't post table description, so - to me - this looks suspicious:
code :
MAX (TO_NUMBER (delinq_status)) AS delinq_status,
select max(to_number(delinq_status)) from master_copy
SQL> create table test (zero_bal_code varchar2(1));

Table created.

SQL> insert into test values ('1');

1 row created.

SQL> select case when zero_bal_code in (3) then 'default_foreclosure'
  2              else 'unknown'
  3         end loan_type
  4  from test;

LOAN_TYPE
-------------------
unknown
SQL> insert into test values ('B');

1 row created.

SQL> select case when zero_bal_code in (3) then 'default_foreclosure'
  2              else 'unknown'
  3         end loan_type
  4  from test;
ERROR:
ORA-01722: invalid number



no rows selected

SQL> select case when zero_bal_code in ('3') then 'default_foreclosure'
  2              else 'unknown'
  3         end loan_type
  4  from test;

LOAN_TYPE
-------------------
unknown
unknown

SQL>
             CASE
                WHEN (zero_bal_code IN (3)) THEN 'default_foreclosure'
                WHEN (zero_bal_code IN (1)) THEN 'prepaid'
                WHEN (zero_bal_code IN (1)) THEN 'default_reo'
                WHEN (zero_bal_code IN (1)) THEN 'active'
             END
Select statement Invalid number ORA-01722 Oracle DB

Select statement Invalid number ORA-01722 Oracle DB


By : Samuel E. LaSala
Date : March 29 2020, 07:55 AM
like below fixes the issue '7839','7698','7782' is a string, it is not a list of strings so you cannot use it as a list in an IN operator. Instead, check whether the emp string (with surrounding quotes) is a substring of your column1 string.
Oracle Setup:
code :
CREATE TABLE emp ( empno ) As
SELECT 7698 FROM DUAL UNION ALL
SELECT 1234 FROM DUAL;

CREATE TABLE test ( column1 ) AS
SELECT '''7839'',''7698'',''7782''' FROM DUAL;
SELECT *
FROM   emp e
WHERE  EXISTS(
  SELECT 1
  FROM   test t
  WHERE  t.column1 LIKE '%''' || e.empno || '''%'
)
| EMPNO |
| ----: |
|  7698 |
CREATE TABLE emp ( empno ) As
SELECT 7698 FROM DUAL UNION ALL
SELECT 1234 FROM DUAL;

CREATE TABLE test ( column1 ) AS
SELECT '7839,7698,7782' FROM DUAL;
SELECT *
FROM   emp e
WHERE  EXISTS(
  SELECT 1
  FROM   test t
  WHERE  ',' || t.column1 || ',' LIKE '%,' || e.empno || ',%'
)
| EMPNO |
| ----: |
|  7698 |
oracle ora-01722 invalid number where clause in update statement. I want to log this error in error log table

oracle ora-01722 invalid number where clause in update statement. I want to log this error in error log table


By : olivialove
Date : March 29 2020, 07:55 AM
wish help you to fix your issue You cannot(errors wont be logged) use error logging clause of a DML statement to log errors raised in the WHERE clause of the DML statement. Basically, DML(your update statement for instance) that includes where clause consists of two parts, writing part and reading one - so where clause is the reading part(basically a select/cursor) and you can use error logging clause to only catch errors the writing part of a DML statement raises. To be able to catch that error and write it into an error logging table, you could use after servererror trigger, but you wont be able to suppress that error:
Related Posts Related Posts :
  • SQL Query - Group consecutive items based on condition
  • Users who work in same department
  • Syntax error near column value Vb
  • Oracle Trigger BEFORE INSERT has No data found
  • What kind of join to use on SQL tables
  • Is there a way to add a constant value dynamically to all records returned in Hive?
  • SQL optimization (inner join or selects)
  • EF 6.x, LINQ-to-SQL and raw SQL clauses
  • Simple SQL Variable Assignment Only Returns One Letter: Why?
  • Converting a custom timestamp to date
  • SQL Server : inserting Player vs Player names in to new table from tblEntrants
  • invalid identifier in sql
  • PL/SQL - I keep getting this error when concatenating: PLS-00306: wrong number or types of arguments in call to '||'
  • Count records only from left side of a LEFT JOIN
  • get everything before a string including itself oracle
  • Format Data from Word Doc to SQL using RegEX
  • Conditional formatting on MAX value row
  • MS-Access : selecting data from two tables and only returning you need
  • SQL Server: optimal indexing strategies for many-to-many join
  • DBgrid column very wide
  • PostgreSQL Group values by category, count and calculate percentage
  • MS Access SQL - Most Recent Record for Each Consultant ID
  • Update table: Summary of previous rows without using cursor or while loop
  • PostgreSQL: built-in function to remove substring starting with certain pattern
  • ORA-00909: invalid number of arguments
  • How to summarize all possible combinations of variables?
  • Select Column within a Column SQL
  • PostgreSQL Inserting 2 relationships at once
  • T sql - How to store results from a dynamic query using EXEC or EXECUTE sp_executesql
  • How do I parse my json into CSV using regex?
  • Reverse foreign key cascading (or how to collect database garbage)
  • SQL Pivot Questions
  • Insert records into a table with a condition in SQL Server 2016
  • display null value using rank functions in oracle sql
  • SQL - Get count of group by column but also select top item of group
  • How to add an array of datarows into an exisitng table inside my database
  • There is no unique constraint matching given keys for referenced table "employee" 1
  • SQL: Unable to SELECT joined column
  • How to find out how much space a SQL Server table uses?
  • Window function to remove specific records from SQL Server dataset
  • How to add a column for each day in sql?
  • Create group column based on the specific rows
  • Not sure if this consistitues a transitive dependency
  • How to compare the values in a column to a long list in SQL Server
  • Preserving data format Decimal(6,5) from vba to sql
  • Oracle Query to rollup QTY by Year- only last 3 years
  • SQL - Calculate 2 columns and view result to another to column
  • Divide or Multiply according to a condition (Improving query)
  • PostgreSQL unnest() with consecutive integers grouped by number
  • SQL to limit output to certain months and years
  • VARCHAR TIME TO GET THE DIFFERENCE
  • SQL conditional constraint on multiple columns being unique
  • Optimize a SQL select query in a loop
  • BTEQ Teradata Import Multiple files into one table
  • Update SQL datetime column with oldest values of another table column?
  • Is INSERT ... SELECT an atomic transaction?
  • SQL query completed successfully but not results
  • SQL sub select returning multiple values
  • Verify condition on two columns
  • SQL conditional field, first match JOIN
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org