Featured Post

SQL Query: 3 Methods for Calculating Cumulative SUM

Image
SQL provides various constructs for calculating cumulative sums, offering flexibility and efficiency in data analysis. In this article, we explore three distinct SQL queries that facilitate the computation of cumulative sums. Each query leverages different SQL constructs to achieve the desired outcome, catering to diverse analytical needs and preferences. Using Window Functions (e.g., PostgreSQL, SQL Server, Oracle) SELECT id, value, SUM(value) OVER (ORDER BY id) AS cumulative_sum  FROM your_table; This query uses the SUM() window function with the OVER clause to calculate the cumulative sum of the value column ordered by the id column. Using Subqueries (e.g., MySQL, SQLite): SELECT t1.id, t1.value, SUM(t2.value) AS cumulative_sum FROM your_table t1 JOIN your_table t2 ON t1.id >= t2.id GROUP BY t1.id, t1.value ORDER BY t1.id; This query uses a self-join to calculate the cumulative sum. It joins the table with itself, matching rows where the id in the first table is greater than or

How to Resolve ORA-01722 Invalid Number Error in Oracle SQL Developer

When working with Oracle SQL Developer, you may come across an error message that reads "ORA-01722: invalid number". This error can be frustrating and difficult to understand, especially if you're new to SQL. In this post, I will explain the cause of this error and provide a solution to resolve it.


Invalid Number Error


Cause of the Error - ORA-01722


Let's consider an example where we have two tables named "my_hr" and "my_dept". The "my_hr" table contains three columns - "name", "pay_id", and "user_id", while the "my_dept" table contains two columns - "name" and "user_id".

CREATE TABLE my_hr (
name CHAR(20),
pay_id NUMBER,
user_id CHAR(6)
);

CREATE TABLE my_dept (
name CHAR(20),
user_id CHAR(6)
);

Now, let's insert some sample data into these tables:

INSERT INTO my_hr VALUES ('Srini', 567, '123456');
INSERT INTO my_dept VALUES ('Srini', '123456');

If we try to execute the following SELECT query to join these two tables using the "name" and "user_id" columns and filter the results based on the "pay_id" column, we will get the "ORA-01722: invalid number" error:

SELECT *
FROM my_hr a, my_dept b
WHERE name = 'Srini'
AND pay_id = '123456' -- This causes the error
AND a.user_id = b.user_id;

The reason for this error is that we are comparing a number column "pay_id" with a string literal value ('123456').

Solution to Resolve the Error:


To fix the error, we need to make sure that we are comparing the "pay_id" column with a number value instead of a string. We can do this by removing the single quotes from the value we are comparing:

SELECT *
FROM my_hr a, my_dept b
WHERE name = 'Srini'
AND pay_id = 123456 -- This is the corrected version
AND a.user_id = b.user_id;

By removing the single quotes from the value of the "pay_id" column, we can compare it with the number column correctly.

Comments

Popular posts from this blog

How to Fix datetime Import Error in Python Quickly

Explained Ideal Structure of Python Class

How to Check Kafka Available Brokers