I have a feedback_ques, feedback_ans table as follows: I want to join the tables as follows: This gives me all the fields. I want the query to return null for answers if they arent found in the answers table. For example member_id 20 doesnt have answers, so i would want the table to display null values as bel…
Tag: oracle
SELECT rows with a new DISTINCT from a VARCHAR with CSV in it
I have an Oracle database table with a field called Classification which is VARCHAR. The VARCHAR is a CSV(using semi colons). Example: I want to pull all the rows with ONLY a different value in the CSV from the others. It is ok if a row has a previously found value as long as it has a new different value.
How to list all schemas and tablespaces in Oracle from .dmp file without importing?
So, the problem is that I have the .DMP file that was exported by a customer using exp or expdp. I want to get some of the .dmp properties, as I do in SQLServer using the following command: RESTORE HEADERONLY FROM DISK = N”C:tempmyfile.bak”; The original tablespace and schema are the infos I need …
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 …
SQL Query to display dates weekly from current date
I have a requirement to display dates every week starting from current date for 48 months. I was wondering if it is possible through SQL or i will have to write a function to achieve it. Below is my …
Counting entries in SQL which meet a selective criteria
I have a tables which stores the type of account users create for a product. There are 3 possible paths. They can either create a trial account, then create a full account. They can create a full …
How to count non-zero numbers in a comma separated string in oracle sql
Want to count non-zero numbers from a comma separated string in oracle 12.10. If my column has data as 2000,300,0,0 then count it as 2. This gives me 2000 and 300 as result but what’s next to count it. select regexp_substr(‘2000,300,0,0′,'[^,]+’,1,level) from dual connect BY to_number(…
getting different results from the same query according to the value of a variable
suppose we have the following masterdata: and we have the order tables: what is requested is to have one single query that could return 2 different sets of data, according to the value of a given variable v_order_status: v_order_status = ‘del’ then only transport orders is retrieved: v_order_statu…
Combining MAX and SUM in an Oracle ROLLUP
Here’s my SQL: The output looks like this: I was expecting it to look like this: In other words, I was expecting the rollup to sum up the maximum weights of each food instead of taking the maximum of all the maximums in the food-type category. I do have a solution of sorts, but it means having to add an
Find sum() from two different tables and join them based on a condition?
I have two tables, Table1: ID Amount Date —————— 123 500.00 02-Sep-2020 123 240.00 02-Sep-2020 124 200.50 02-Sep-2020 125 150.70 03-Sep-2020 123 480.80 03-Sep-2020 Table2 ID …