I have two tables : Customer: id name address_id 1 John 4 2 Kate 5 3 Bob 2 4 Michael 2 5 Adriana 3 6 Ann 1 Address: id detail_str_name city district street_name 1 France,Paris,str.2,N5 Paris Paris str.2 2 France,Parise,str.2 ,N3 Paris Paris str.2 3 France, Lille ,str.3,N4 Lille Lille str.3 4 France,Paris,str.…
Tag: oracle
How to use DENSE_RANK and order by aditional column?
I am trying to use dense_rank to get element order for instance: I have table seq_test with data: after I run the code above, I’ve got : I want to achieve: So DENSE_RANK() function use its own order defined in function definition I need to order the SEQ column by sequence column. Update: I want to get: …
count business days in calculation
Continuing from my previous question: case when date column is greater than sysdate then ‘Y’ One thing I forgot to consider was including working days in the calculation. Here is the query: So I need to exclude Saturday and Sunday in the calculation above. I tried to add this in the where clause: …
Getting a not valid month and need to insert records in incremental manner
The problem which I am facing: I need to convert timestamp to date and then insert it into the deligate_details_trans for the completed_date column of the deligate_details_main table. When I am converting it is giving ORA-01843: not a valid month error. Currently, deligate_details_main has 3 rows that are goi…
choose from a time period oracle sql
I have timestamp date format in column FIRST_DATE and i need to choose time period from a certain hour, for ex. all from 18:00 10.05.21 to 18:00 11.05.2021 the problem is that date column in timestamp format – FIRST_DATE: 10/05/2020 0:00:03,000000 TIMESTAMP(6) so i tried to use it: so with this way i wa…
SQL file executes with no error but also no results through shell script
I have a sqlfile that I want to run via shell script against an Oracle pluggable database. The sql file basically just loops through table names to apply grants to a role. When I run the script, it looks like it executes the file, but it really doesnt, no grants are applied. When I log into sqlplus and execut…
How to join multiple tables and printing the result for same id which are populating more than one in a single row
The problem facing in joining the tables : Table ques_ans has ref_ques_id column that is being populated from ques_ref table. So, If ref_ques_id = 3 and ref_ans_id = 1 then it should display ‘Yes’ i.e populating from ans_ref table. Likewise for ref_ques_id = 2 then it should display ‘No̵…
ORA-01756: quoted string not properly terminated, a part of my query it’s failing
Is there something bad in this Query? It’s not the full query it’s just a part of it. I’ts throwing this error: Answer Use the q-quoting mechanism. By the way, your code can be simplified to No need to to_char and then to_date current_date; it already is DATE datatype, just remove (truncate …
Oracle sql, filling the missing values in the join between the table and the dictionary
A request for help in solving problem, example below on image: Example: Answer The data densification technique using “partition by” claude after an “outer join” clause comes very handy for that purpose. demo on db<>fiddle
Limit number of occurrences of value in a table oracle sql
I have oracle sql table of students, students are assigned to groups. I have to set constraint so there will be no more than 10 students in a group (ideally no more than N students in a group). Basically restrict table from having more than N rows with the same GROUP_ID value. Here is table declaration. IR…