I have the below table C1 C2 C3 A1 A2 A3 B1 B2 B3 C1 C2 C3 C1 C4 C3 For each unique combination of C1 and C3, I would like to keep the first value in C2, but storing the count of unique elements …
Tag: oracle
Order of Procedure for Select statement with Where Clause and other operators
This is the table I’m working off: From this order of procedure that was provided in class: Parentheses */ +- = <= >= < > IS Between NOT AND OR I have to work out how many rows of the following statement with show up: Is this statement read in this order: QtyA+QtyB QtyA+QtyB < 5 gender = …
Print a custom message when no rows are returned by a SELECT statement
I am trying to create a report of INVALID objects in Oracle. The requirement is to print a custom message when no rows are returned (This is because the output will be redirected to an HTML report). I tried the following query: Error am seeing: While the individual queries work without an issue, it fails to e…
Oracle create league table from football results
Having the below table DATE_MATCH HOME_TEAM AWAY_TEAM HOME_GOALS AWAY_GOALS 03-APR-21 Alaves Eibar 2 1 02-APR-21 Alaves Huesca 3 0 01-APR-21 Eibar Huesca 1 1 31-MAR-21 Huesca Eibar 2 0 30-MAR-21 …
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…
Use a macro in SAS ‘to_date’ conversion
Being forced to pass my code in explicit pass though, the date conversion no longer works. I would like to use the &aaaa macro in the convert function. My code example : I got an error : “The (full) year must be between -4713 and +9999 and be different from 0”. Does anyone have a solution? Ans…
Failing to produce or update a table row using a trigger in SQL
I am trying to create a trigger in Oracle SQL 12c. The concept is that i have two tables, a storage table (Storage) that holds items and a delivery table (Supplier_Orders) that notes how many items(…
How to delete a row from a relationship table using two columns
I’m trying to delete a relationship between table T2 and T3 that is on table T1. I’ve searched but I could only find examples of a DELETE with only one INNER JOIN. I’m trying something like this, but I get a error: SQL error: ORA-00933. 00000 – “SQL command not properly endedR…
How do i transpose and grouping in PL/SQL [ORACLE]
I have this table: USER DEP USER1 DEP1 USER2 DEP2 USER3 DEP2 USER4 DEP3 But i want to transpose it and group user under DEP. DEP1 DEP2 DEP3 USER1 USER2 USER4 USER3 NULL NULL What i have tried following pivot query. The output is: 00933. 00000 – “SQL command not properly ended”. How do i use …
how to rollback tran when error occur in oracle
i try to solve 2 requests below but i only code for the first one,, can any one help to solve the second one Write SQL statements using cursor to update salary of all employees base on update conditionals Make sure all update records have been updated completely. The database transaction will be rolled back i…