I need to get back one record of a player’s (Rank A players only) most recent win date (some win dates are null but we need to include them) but picking only the last place of their most recent game session. So basically in that order: get their max win_date (if null, still include them) > from there…
Tag: oracle
SQL query to add column values
TABLE T1 Name ID AMAN 10 JIM 11 SAM 12 TABLE T2 VAL ID A00 10 B0 11 The result I want: T3 Name ID VAL AMAN 10 A00 JIM 11 B0 SAM 12 NA How can I write this query? Answer
Making a table with a foreign key
I’m creating a SQL database in Oracle. I have created this table: And I tried creating a second table that has a foreign key referencing it: I get this error: ORA-00904: “LID”: invalid identifier 00904. 00000 – “%s: invalid identifier” Answer Defining a foreign key on a col…
How to select part of the CLOB column with SQL
I am trying to get part of data from CLOB column which contain XML. I need this because I want to create a report based on this data. Table structure: DIAGRAM ( ID number, XML clob ); XML example: I want get this results: Thanks in advance! Answer The main problem is that your XML is invalid. It’s missi…
How do I replace blank spaces with null values and query where values are not null? (I’m filling in a table)
I have two tables: table1: USER_NUM FIRST_NAME LAST_NAME A123 Billy Bob A124 Billy Joe Jane Doe John Doe I am trying to insert FIRST_NAME and LAST_NAME under USER_NAME into table2: OWNER_ID USER_NUM USER_NAME 111 A123 112 A124 Using this query: The problem is, if I isolate my select statement I still get blan…
select data from range of two dates
I want a query for selecting data between two dates (p_startdt,p_enddt) which is given by the user, if there is no input then by default data of last one year will be given as output. I am not able to put case for null or no input Answer Use NVL to handle the case of a NULL value. The following
oracle sql Sort dated transactions by order of transaction
I have been working on this for a few weeks and I know there’s a way like using ROW_NUMBER(), but I’m just not too familiar with it, Or maybe there’s a better way…. shows this: It needs to show this: As you can see, there’s 2 delete dates for SKU 555454, and it has to show the la…
How to select middle 80% rows in Oracle SQL with order by (top % is not working)
I tried top % but that is not working in Oracle SQL. Offset and fetch next are working but I am not able to give percentage. What will be the best way to fetch middle 80% rows? Any help will be appreciated, thanks! Answer Middle 80%? That’s between 10 and 90%, then? Let’s suppose it is. Sample dat…
How to select records from January 1, 2021 to last week
I was wondering how to make a query where I can select data from Jan 1, 21 to last week. I tried to put it in the date filter like this: but I would have to manually go in every week to change the end date, I was wondering if there is a more efficient way to select for data
Oracle trigger multiple conditions in when clause
I’m trying to create a trigger that updates a column in a table when other columns are updated. but getting the following error while saving the trigger ORA-25000: invalid use of bind variable in trigger WHEN clause My trigger is as follows, I’m not sure what is wrong with the code. Answer Althoug…