All Questions
69,440
questions
0
votes
2
answers
43
views
Joining tables with different date formats (Literal does not match format string)
I'm trying to join two Oracle tables (basically to pull in a daily rate against the date in another table), but the table I'm pulling the date from has the timestamp in it. I'm seeing a lot of ...
- 1
0
votes
2
answers
48
views
Is there any method to convert a varchar with specific format into time in Oracle SQL?
I have a table with three columns, one of the columns ateststatus_date has the following format: e.g:
28-MAY-24 11.57.20.000000 PM
and it's defined as varchar(2). I used To_date() and ...
- 11
1
vote
3
answers
48
views
Converting char to date and pulling past two years data
I need to pull data from tables with millions of rows and limit it to just the past two years. I am working on developing a SQL statement to enter into Power BI to limit what it pulls. Obviously I am ...
0
votes
2
answers
28
views
Oralce JSON_TRANSFORM, set/insert/append not working
Why this:
with j(jval) AS
(
select JSON_SERIALIZE('{"contract" :{"contract-type" : "P",
"contract-id" : 123451,
"proposal-no" : "123456&...
- 652
1
vote
0
answers
37
views
ArgumentOutOfRangeException error while assigning OracleClob value to .NET Core string
I want to read large clob data type (2 million rows in table) from a stored procedure in an Oracle database. After reading the data and trying to assign OracleClob value field to .NET Core string type,...
- 23
0
votes
1
answer
49
views
Oracle SQL gives ORA-01790 without UNION, and a workaround works strangely. Why?
We have an Oracle view "view1", which SQL is like this:
select ...,
nvl(iac.indirect_attri_cost, 0) + nvl(pc.premises_cost, 0) + nvl(cc.central_cost, 0) total_cost
from (select ..., ...
- 59
0
votes
0
answers
39
views
Error at line 9/0: ORA-00922: missing or invalid option [closed]
I get this error if anyone could guide me
Error at line 9/0: ORA-00922: missing or invalid option
7. );
8. -- Crear tabla Usuarios
9. CREATE TABLE Usuarios (
10. id_usuario NUMBER PRIMARY KEY,
...
1
vote
2
answers
44
views
SQL command works fine when run manually (SQL Developer) but gives ORA-00922 in Python's oracledb module
I am working with an Oracle SQL database, and I would like to run the command
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD';
It works fine when I run it from the SQL Developer app manually. ...
- 11
0
votes
1
answer
27
views
Oracle accumulate values with condition
below is an extract of an Oracle table with these columns:
ID
WeekStartDate
AbsentFrom
AbsentTo
1
01-JAN-24
null
null
1
08-JAN-24
09-JAN-24
17-JAN-24
1
15-JAN-24
09-JAN-24
17-JAN-24
1
22-JAN-24
...
0
votes
2
answers
65
views
EXPDP doesn't recognise user names
I'm trying run EXPDP on an Oracle 19 database but it doesn't recognize any of the schema names apart from sys.
select * from dba_users;
when run in Oracle SQL Developers returns all the users I'd ...
- 3
-2
votes
2
answers
33
views
oracle sql usage of CASE THEN [closed]
I have a set of complex queries that result in producing the result set that looks like :
TradeId | Currency | Value
1 USD 10
1 EUR 20
2 USD 30
2 ...
- 590
0
votes
1
answer
32
views
Querying Json_Object_t using dot notation in Oracle 19c
I have a case where Json document is passed as CLOB input to a stored procedure.
I am converting it into Json Object as below
l_js_obj := JSON_OBJECT_T(p_js_clob_i);
Json:
{"Key1":{"...
- 153
2
votes
1
answer
25
views
Oracle SQL pattern matching using wildcards
I'm trying to search though a large list of postal codes in various countries
Specifically, I want to identify all Canadian entries USING A CASE STATEMENT.
after all spaces have been stripped out, ...
- 690
2
votes
2
answers
34
views
Why is my timestamp getting truncated when I select the maximum, non-null value from a series of columns?
So, I have a series of columns and I need the maximum, non-null value.
Here is a sample of the problem, and it works as I would expect:
select t.*, (select max(to_timestamp(column_value)) as END_TIME ...
- 5,487
-4
votes
1
answer
44
views
Compare records within the same table and find missing setup
I'm trying to write an SQL in Oracle where I compare only two items in the same table and find the records where item2 is missing the warehouse setup where the warehouses exist in comparison to setup ...
- 31