I have string variables and these variables differ in length. Let’s assume that I have the following strings: I want to convert my string variables to float. So, to do that, I call TO_NUMBER() function. The problem is when I call TO_NUMBER() function from my .NET web application instead of calling it di…
Tag: oracle
ORA-06502: PL/SQL: numeric or value error when concatenating
I am getting error as numeric or value error Answer The problem is with operator precedence: Oracle evaluates operators with equal precedence from left to right within an expression. … and as the table shows + and || have equal precedence. So in this statement: this is interpreted as ‘sum = ‘…
Sql query with max value in where condition
I have a query which should return one row if the conditions are met. This query compares columns from 3 tables and should use the max value of DEGREE_HIERARCHY since there can be more than one value. …
ORA-01417: a table may be outer joined to at most one other table
I have this query below for oracle sql: When i execute it, i got ORA-01417: a table may be outer joined to at most one other table. Need help for this error. Answer Please use below query. In the current query, your aggregation is incorrect, you have to use same function as you use in the select query. And ch…
SQL query to add the state value dynamically
The below query returns value such as – ‘GA’,’CA’,’AL’ Query#1 (SELECT Upper(hzg.geography_name) FROM hz_geographies HZG, hz_geography_identifiers HZGI, …
How to joins in oracle based on condition
I have two tables as below: Table Apple: Table KEEY: Desired Output: Code Tried So far: This is giving me : I guess i need to use pivot instead of Union to get both the ids on same row. Have you ever encountered such scenarios? Any pointers to proceed will be very helpful. Thanks in Advance! DDL used for the
Update column in Oracle table with value from another table with duplicates
I am trying to update the column (REPT_IND) from table A to the value in table B where A.ID = B.ID and some conditions in table B. There are some duplicates in table B, but nonetheless the REPT_IND is …
SQL – Matching only the values in IN clause (not less or more)
I am having the following ManyToMany table TEAM_USERS with the below data: So, I want to get the TEAM_ID that is having only two user with the USER_ID IN (3, 4), in this case it’s team 11. But I am getting team 1, 11, and 12. And I want only team 11. I did the following code but I am
Automatically updating a field after a manuall update in Oracle SQL
I have a table that has a bunch of columns, one of which is a ‘last_update_time’ column. Whenever I change the value of any of the other columns, I would like ‘last_update_time’ to automatically update to SYSDATE. I know that you would usually use triggers for this kind of thing, but a…
Where should I modify?
This is an oracle sql. SQL servel says “there is no USERS Table” but user table exists in my db. Answer You don’t need the table name.