I am having the below require. if anyone provide the solution it would be thankful Requirements:- Transaction of the customer who purchased in the below category Customers and purchased Category Total Number of customer from category level category and customer count I want to display the output life below ou…
Tag: oracle
How to remove ‘wasted rows’ after delete in Oracle SQL database
In Oracle sql database, a process in our system deleted (not truncated) approx 2 million rows from a table. This resulted in a huge number of ‘wasted rows’ causing the queries running on that table to take more than 9 hours which usually get over in 5 minutes. Upon checking, we found that the size…
This procedure is inserting null values into my custom table – why?
Table output is like this when I pass acad_prog ‘BTRCS’ and admit_term ‘1701’ as input in procedure: Answer You need to use cursor values in if statement and in insert statement like this: This can also be done using insert into .. select .. (without cursor) as following: Cheers!!
CASE expression in WHERE clause for diferent and
Can I use case expression to build where like this? I need change where clause depending on the value of x variable. Answer An alternative to using OR is to use nested CASE statements: or you could simplify it to: However, you should check whether Oracle can use column indexes or if a separate function-based …
Transform table to smoothen records in one column
the situation (a real life situation) is: I have a table with “log” records, for simplicity let’s assume there are only columns NAME, TIME, STATE (in reality there are few more). There are cca 1 Milion rows in which there are 100 Names (computers) and cca 10 states (productive, idle, automat…
How do i use a cursor correctly
With the code below I am trying to search for certain words (SEED WORDS) inside reviews. Then depending on the number of Seed words inside each review I come up with a rating number. There are …
How to use a list/array of values as column name in select statement?
Say I have 3 list of data that I am using to build a new query. I need to take these list of data and return the values of those list where things were found. So my question is this: Is there a standard method of taking a list and using it as a column? I will need to use
Is there an equivalent to TRY_CONVERT() in Oracle?
TRY_CONVERT() in T-SQL would return a value cast to the specified data type if the cast succeeded; otherwise it would return NULL. Is there a similar function in ORACLE or a way to reproduce this …
Select single value from same columns based on condition
I have below table and using oracle sql Result Question : Need query to get it. I have Tried MAX but not working Answer Assuming these are the only three conditions, you can use conditional aggregation: In Oracle, you can simplify this to: Here is a db<>fiddle. The keep syntax is getting the last value …
Oracle SQL – Count, per month, how many times a site appears in the results
I’m not sure if I will explain this correctly so apologies in advance. I’m looking to put together a report that shows the number of times a site (central_site.site_code & central_site.site_name) …