I have few combinations of records in my table but i want to generate Multiple combinations from original table. Records in table codeset codevalue column system test1 1 null sys1 test2 2 col2 sys1 test3 3 col3 sys1 test3 4 col4 sys1 test3 5 col5 sys1 test4 null col6 sys1 test5 6 col7 sys2 test5 7 col8 sys2 A…
Tag: oracle
sql select everything with maximum date (that that is smaller than a specific date) without subqueries
I would like to write a sql query where I choose all rows grouped by id where the column date is the latest date for this id but still smaller than for example 16-JUL-2021. I would like to do this without using subqueries (in oracle), is that possible? I tried the below but it doesn’t work. Answer You c…
How to add two conditions in merge statement using rowid and rownum
Query to load the sequence for e_uuid 13 & 15 Then I want to merge into the same table for e_uuid 14 & 16 For 14: It should check e_uuid = 13 and maximum seq_cnt. Here(after executing my merge statement) maximum seq_cnt is 2 then the seq_cnt for 14 will come as 3 & 4. And if there are any
Unable to Convert to Date
I am trying to return just the date from a datetime field When I run the Select Claus with just the first line, it work as expected. But when I add the second line, I receive From keyword not found error. ORA-00923: FROM keyword not found where expected Answer In Oracle, DATE is a reserved word and you cannot…
Oracle INSERT with a CASE statement
I have the following table declaration and I’m trying to use a case statement to INSERT some rows and I’m getting the following error ORA-00976: Specified pseudocolumn or operator not allowed here. Can someone please let me know how to rectify the INSERT code. Thanks in advance to all who answer. …
When I update the same row in a table in SQL more than once, it gives an ORA-00001: unique constraint error
I am trying to update a row in a table in SQL back to back and am getting an error when running the second update statement. I’m getting an error in the second update statement. ORA-00001: unique constraint (ADMIN.JHIST_EMP_ID_ST_DATE_PK) violated ORA-06512: at “ADMIN.ADD_JOB_HISTORY”, line …
How can I limit the max value of an SQL field?
I am trying to force the value of the field PRICE to be under 500 if is it paid with cash (PAYMENT = “CASH”), to make impossible to insert anything over that. The table (SALES) is something like this: ID (PK) [INT] | DATE [VARCHAR] | SHOP [VARCHAR] | PRICE [FLOAT] | PAYMENT [VARCHAR] I am almost n…
How to pass table name as a parameter in update procedure in Oracle?
I am new to Oracle so please sorry the question that seems to be very easy for you. I need to get the following procedure with UPDATE query with replace function This procedure removes all spaces. But when I call it I’ve got the following error How can I modify my code to handle the problems? Thank you.…
How do I update two variables in a oracle sql server update using case statement
I am trying to update two variables in a Oracle server as follows: I want to update why_update column only if the user provided a reason for update otherwise leave the column as it is (which is varchar type and can be NULL). Or any other better solution? Answer THis will work:
Oracle Hierarchical Query at depth level
I have a requirement to build a table from a hierarchical table. Table structure as below: emp_hier table: emp_id supervisorId 100 null 1 100 2 1 3 2 New table: I have to write a select query on the emp_heir table and the selected data should look like this: sel_emp_id rel_emp_id relation depth_lvl 100 100 se…