Consider I have a table like this How can I select rows from it so that I have equal number of 1s and 0s, like below can be a result The rows removed/left out are at random and deleting from an existing table would work as well. Answer For each col2 partition, you can give each row a row number
Tag: oracle
ORA-00922: missing or invalid option , can’t create tables
I’m trying to create some tables in Oracle LiveSQL and I’m getting ORA-00922: missing or invalid option but I can’t figure it out why it doesn’t work; here is the code(the comments stand for the tables in the relational model).I apologize for eventual stupid mistakes, I’m a compl…
Oracle APEX master detail page creation: ORA-06531 error
I am creating a flock management application with APEX with the following PL/SQL scheme: In this scheme, the SALE and EVENT tables reference the EID field of the SHEEP table (unique identifier of 5 numbers and one letter – representing the yellow tag they have in their ears). I am trying to create a Mas…
How to declare a cursor inside another cursor
How declare another cursor inside cursor c_employees? Note: cursor c_employees return employees_id and this id pass to other cursor in the where clause. For example Answer Cursors can take parameters hence But generally, when you start seeing cursors with cursors etc etc…its also time to look at whether…
Is there a way to include a DELETE FROM statement in a nested WITH statement?
I am trying to delete duplicates by creating another table but I am unable to include the delete from statement. I have the code here in SQL Server but I am trying to convert it to Oracle. I tried replacing the select with the delete from but I’m getting the error: missing SELECT keyword. Here is the co…
Simple sql script fails on execution
Below is a sample bash script and it seems to error out when i execute the bash. The sql works on its own but in a script oddly. Please advise JOB_RUNNING=’SELECT count(1) from gv b,gv a WHERE b.paddr = a.addr AND type=”’USER”’ AND b.status=”’ACTIVE”’ AND …
Add new column with Boolean in PL/SQL
I’m learning PL/SQL right now and I have a doubt. I have created the following table called tbProducts: And I have inserted some values so the table is like this: Now what I’m looking for is a boolean variable that can be called bUpdate that returns FALSE if today’s date (26-AUG-2021) is gre…
Change + ordered to + leading
I’m trying try to find the most selective criteria to start joining in SQL. I tried this: But this + ordered is deprecated. How I can implement this query using + leading? Answer Use the leading hint and put the table aliases between parentheses in the order you want the database to join them, for examp…
How to split and convert to pascal case oracle column names?
I want to selec column names and convert to pascal case. My column names are like this: IMG_SAR_NAME INT_AKT_DESC I want to split “_” names and convert to: ImgSarName IntAktDesc I can get column names with sql But can not convert to pascalcase. Answer I don’t know if always the underscore is…
oracle sql – get difference between 2 dates (sysdate minus the datevalue of a other table)
I want to know the difference betweeen 2 dates. I need something like this: “sysdate minus the datevalue of a other table” Currently I have something like this, but it’s not working The 2nd select statement prints the date of the latest export. Example “27-JUL-21”. thanks for you…