Getting the following output when i use the below query But the expected output should be as below (Refer RowNumber column) Answer First find “groups” each of those rows belongs to (partitioned by refnum and name) (that’s what the temp CTE does), and then apply dense_rank to fetch the final …
Tag: oracle19c
how to retrive data from VARRAY and filter it
i have to retrieve specific data from Varray for example i want to retrieve the name where VARRAY includes AC1 i tried select * from exprement where seat=’AC1′; Answer You can use: or As an aside, if you defined seat as a nested table (rather than a VARRAY) then you could use the MEMBER OF operato…
single-row subquery returns more than one row in oracle in HR schema
and I found an error like the following : Answer Looks like there are “many” employees whose job_id = ‘IT_PROG’. In that case, subquery returns “many rows” and your query results in an error. It depends on what you want to do in such a case; for example, you might want to r…
How to set an autoincrement composite primary key in oracle19c?
I want to create a table, with composite primary key and autoincrement, in Oracle 19c, like this: How I do it? Answer Create trigger on this table to achieve this result. Assume table name is test123
Invalid number when converting a date to another format
I have this query, and i am trying to format all dates for the two columns mentioned in the query below to the format mentioned: and i want to convert the results from timeone and timetwo to another date format DD-MM-YYYY HH:MIPM but when I run the query I got back invalid number. The datatype of the timeone …
Oracle SQL ternary operator or function?
Is there a simple Oracle syntax like a ternary operator or function? These work: If there’s a simpler, shorter syntax I would like to know. Answer You can use a CASE expression with LIKE: or DECODE and INSTR: or just simply use LIKE: INSTR: or REGEXP_LIKE: db<>fiddle here
trim function not working on result of to_char
The query gives the result 3 as expected but the query: gives 003 I need the result to be as the first query (‘3’) but I also need the ‘TO_CHAR’ (I’m using it to create a virtual column from a NUMBER column). I can not figure why and how to fix it. Also, I can not use regex_repla…
Put a json inside another json on Oracle 19
i have the next problem. I need create a json from a table that contain another json inside in one of the columns Having this: i try with this query: But the result is not correct: Must be something like: There is a way to get the correct result? Regards. Answer after see examples and see the resuslts. I know
Oracle SQL where clause does not work for some dates
Below query works fine with the recent dates (for e.g. if the date is 20-JUN-16, 30-SEP-19). But if I query with date value set to 29-MAR-80, 01-JAN-94 it does not return any result. Wondering what could be the reason. Am not allowed to change the existing year format to YYYY. We are on Oracle 19c. Answer Her…
remove olde records grouping by three columns and ordering by date
this is the situation: CREATE SEQUENCE SEQ_tmpdata MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 2206 NOCACHE ORDER NOCYCLE NOKEEP NOSCALE GLOBAL; CREATE TABLE …