“List the “manager’s name” and the “number of employees” in that manager’s department” is the requested query for my class. I cannot embed pictures yet so I will do my best to have them formatted properly on here Here is some data from the employees table: the code I currently have is: This code seems to give me all employees
Tag: oracle12c
Need to get appropriate/expected row number in Oracle PLSQL
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 result. Sample data (simplified, as I don’t have your tables):
Oracle query, get count of records by hour
I am trying to get transaction counts for every hour. Normally it is a straight forward query by unfortunately the timestamp column I have to work with is not timestamp but varchar2! No matter what I try I get either “not a valid month” or “invalid number”, depending on the format I use. The timestamp looks like: 2021-08-08 00:00:52:632 I
Missing IN or OUT parameter in Oracle SQL
Im getting an error on this parte of SQL{ “U” = UPPER(:codigo) }. When i remove the variable and hardcode :codigo as u or U it works fine. The erro i receive is Missing In or Out parameter. I’m receiving this error on Oracle 12c Service Bus Full SQL: Answer The problem was: Each time variable :posicao was referenced my
Data inserting in between the rows in table
I have a table which has around 1,000,000 records. I noticed that when I insert a record it does not get inserted at last instead it gets inserted in between Example My table (testing) Si. Name. Address. 1. Test. 1/2 test 2. Test. 3/4 test Si has a unique index created but no primary keys defined So when I make
Oracle : Select only particular value from a row
I have a like below data : so i wanted to remove all the value after = and before | except for 1234. expected data : Answer You can use: Which, for the sample data: Outputs: VALUE 1234=A||1456=||1789= 1245=||1234=V 1234,1133 1456=||1234=1,234||1234 sqlfiddle here
Display group having certain value only on max date and not in other entries of group
I am looking to display the group which is grouped on id column where trans_cd=’Audit’ only on max trans_proc_dt of that group. There should not be any other trans_cd=’Audit’ execpt for max trans_proc_dt. My output should be I can’t think how to go about getting my result set. Answer You can use analytic functions to calculate absolute maximum date and
Oracle SQL: how to concatenate text until reaches maximum size
I have a case: some script to reproduce: And Select: What i want to achieve is concatenate texts until sum of them is less than 3000. If larger, then another text. And here goes problem: texts with group 1 when You sum them is greater than 3000. Text with id = 2 supposed to have group 2 but is 1.
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(…
Oracle hierarchical queries data
The link gives a good example for overview on how to use Oracle hierarchical queries. I was trying to generate the below combination of data with the example table tab1 given in the link but struck …