I know its been asked a lot but didn’t know how to apply it in a variable; this is my code DECLARE Main_time_elap varchar2(1000); time_elap varchar2(1000); time_elape number(5); BEGIN main_time_elap:=to_char(systimestamp,’HH24:MI:SS’); time_elap:=to_char(systimestamp,’HH24:MI:SS’); time_elape:=to_number(time_elap)-to_number(main_time_elap); –time_elape :=time_elap-main_time_elap ; end; / >Solution : It is much simpler: DECLARE Main_time_elap TIMESTAMP; time_elap TIMESTAMP; time_elape INTERVAL DAY TO SECOND; sec_elape NUMBER;… Read More ORA-06502: PL/SQL: character to number conversion error while using variable

I have 3 tables that looks a bit like this. PersonTable ID Person 1 name1 2 name2 3 name3 TypeToPersonTable SPECID PersonID 4 1 8 1 10 3 RequestSatusTable SPECID StatusID 4 1 4 1 4 0 8 0 8 1 I need to calculate how many of which status id there are for each… Read More How to get 2 distinct rows from 1 row with 3 tables in sql oracle?

I have a database for cars. Each car has one to many assignments. Each assignment has a status. The status needs to be renewed every fiscal year, so a status can be the same through many fiscal years. I’d like to compile all the statuses with the years they were active for each car. Like… Read More How to Group Dates based on another variable?

I have a query that involves a where clause which i compare two columns that have DATE as their data type. these are last_collected_date and update_date. SELECT * FROM mytable WHERE last_collected_date < update_date; The weird thing is that this does not work as expected and I get dates of last_collected_date that have the same… Read More two date columns comparison oracle do not work as expected

I have this oracle sql query which selects several columns containing values: SELECT DISTINCT name, age, weight, height FROM my_table I want to exclude rows from the report that contain no values but they should be reported if they have 0.00 as a value. EDIT: I add another case that I missed, the rows should… Read More Oracle SQL exclude rows when coloumns are empty

SELECT To_Char(registrationdate, ‘Month’) AS "REGDATE", COUNT(*) AS "NUMSTUDENTS" FROM registration r INNER JOIN students s ON r.studentid = s.studentid GROUP BY registrationdate ORDER BY NUMSTUDENTS; Results are coming back like this: May 1 April 1 April 1 May 1 April 1 May 1 April 1 May 1 And the expected results are June 213 March… Read More Why is this query returning each student – month pairing individually?

I want to generate this result with the input of start date and end date. For example the start date is 2/6/2023 6:00 and end date is 2/8/2023 7:40, it should output the following >Solution : It is about a row generator. (Setting date format so that it matches yours; you don’t have to do… Read More Oracle Sql split per day based on date range and compute for total hours

What is the correct way to rebuild an index of a specific constraint, without knowing the index name, since it might different between environments. In this case, prefer not knowing the index name (existing condition), and not renaming it first. ALTER INDEX (select index_name from user_constraints where constraint_name = upper(‘constraint123’)) REBUILD; I tried casting, string,… Read More Rebuilding an index, without knowing the index name (SQL)

create table customer (cif number, name varchar(20),mobile number); insert into table customer values(121,’ANT’,789); insert into table customer values(122,’ANT’,789); insert into table customer values(123,’ENT’,789); insert into customer values(124,’ENT’,789); insert into customer values(125,’BEE’,123); insert into customer values(126,’BEE’,123); insert into customer values(127,’BRO’,789); insert into customer values(128,’FIO’,789); commit; I want retrieve data from customer table based on name and… Read More More than one occurrence from table

I have three tables: Users, Rights and UserRights, which is mapping values from two first tables. Example: Table ‘Users’ ID LOGIN 1 test1 2 test2 Table ‘Rights’ ID NAME 1 read 2 write Table ‘UserRights’ USER_ID RIGHT_ID 1 1 2 1 2 2 I need to check whether the user has the appropriate right and… Read More SQL Query to select if a value from another exists in current table