I have a table who looks like this: Pam_A Week Value_1 A 1 10 A 2 13 B 3 15 B 4 10 B 5 11 B 6 10 I want to achieve …
Tag: plsql
Why PLSQL optimize level is not providing expected result?
I am trying to understand complier optimizations in plsql in depth. Theoretically, default optimisation level PLSQL_OPTIMIZE_LEVEL is set to 2. To get better performance, we can set it to 3. To …
how to check a date is in current financial year
I am trying to check a condition if given date is in current financial year i.e april to march. but not getting any idea how to do code select nvl(Count(1), 0)+1 from ASET where IPE=’O’ and irt in (…
How to implement AFTER INSERT Trigger in Oracle PL/SQL?
I am trying to implement after insert trigger in PLSQL. The goal is to check if there are multiple (>1) rows having specific status for each client. If so I’d like to rise an exception and roll …
How to insert from old table to new table using trigger in pl/sql
There is a column which calls as text (column’s name) inside a table. I want to create different columns from this column. How to insert to another table these columns using trigger. I tried few …
Use select inside ‘filename’ property of utl_file.fopen
I have a procedure that extracts data from a view and stores it on disc in CSV format. I’d like a name of the file to contain the most recent timestamp from table MY_TIMESTAMPS. For now it looks like this: The part with (select to_char(max(EVENT_TS)) from MY_TIMESTAMPS) throws an error. How can I set this dynamic filename property? Maybe write
Promote a clerk if his salary is greater than 1000 and update his salary by 10%
Write a Parameter Cursor program that promotes CLERK who earn more than 1000 to SR CLERK and increase the salary by 10%. Pass CLERK as a parameter to the Cursor. Use cursor with Update Clause. My code: My code does the work but I don’t find it correct so can someone suggest an alternative? Answer You can create a small
subtraction of 2 select statements
I have a simple table: Neighbourhood Some countries have the same neighborhood name. All I want is the total minus the distinct. but I have a problem with the operator; Here is what I am doing. Answer So you want: I might suggest a further breakdown: This shows the frequency of duplication along with example value of neighbourhood.
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 change it. I need
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 output should be grapes. Desired Output: Answer We can use ROW_NUMBER