I have a scenario which needs to be handled in Oracle SQL – that is a requirement. This is a book lending scenario which I am trying to create. Each student is entitled only so many number of books which is based on a credit system. In this sample Student ID ‘100’ is entitled for 4 books. Ne…
Tag: oracle
How to use a calculated value in Oracle SQL count
I have a column called BIRTH_DATE in my view. I want to calculate the age using that column and today’s date, and count the number of staff in an age bracket. For example: staff between 40 and 49 etc. Other answers in plain SQL statements are also welcome. My code is shown below: I am calculating the ag…
How can I load only unique data into my oracle table without specifying PK manually?
Let’s say I have a table real_table with a primary key on column1. I can load data into it like this: But this means I have to specify the PKs manually. Doing it manually is not a huge problem for a relatively small table. I can also do this: But what do I do for a table that has 10
Get range of columns with specific value in Oracle
I wasn’t sure how to title this question, but I’ve got an Oracle table that has a column for each day of the week. These represent the days a business is open. The values in these columns is either ‘Y’ or ‘N’. This is how the table was setup years ago and I am unable to cha…
Get array size in oracle sql
I have a table in oracle with this definition Document is stored in a json format. How can i extract size for array parameters in sql? I need extract array size for each table record, and after sum all this values. ex: 1 step: 2 step: Can someone please give me an idea how to do that ? Answer Provided
sql query to get employee sum and employee count
The above query gives me the data like I want the count of employees and total of each of the above columns in the below format – what function should I use for this ? Answer
Oracle SQL – Sum next X number of Rows
I have a table in Oracle database whith projected sales per week and would like to sum the next 3 weeks for each week. Here is an example of the table for one product and what I would like to achieve in the last column. I tried the Sum(Proj Sales) over (partition by Product order by Date), but I am
can I use different hints in one clause of union in oracle
I have a merge query of below format – now I have to do union in this query My question is do I have to give parallel hint in second union clause also . Also use_hash hint which was running good earlier would not have impact on adding union clause in the query Answer The hints apply to each select
How do I fix ORA-01843: not a valid month?
So at the query level, I have it: And I’ve tried looking at a few articles with one most notable: How to change the date format in Oracle BI Publisher? I have also tried using: While going through structure and XML I noticed my date is in string format: element name=”DATETIME” value=”D…
Using COALESCE correctly in WHERE clause
Can someone explain why coalesce doesn’t work in the where clause given the following conditions below? How do we use coalesce correctly in this case without changing the below coalesce conditions and only for spoiled = Y? Table Fruit: Query: Using the query above will not return anything. Desired outpu…