I’ve been trying to improve my SQL and was playing around with a ‘NOT EXISTS’ function. I needed to find the names of salespeople who did not have any sales to company ‘RED’. I tried this and it did not work: This query ran but returned a NULL. Then I changed it to this and it wo…
Tag: oracle11g
How to use wm_concat one a column that already exists in the query?
So… I am currently using Oracle 11.1g and I need to create a query that uses the ID and CusCODE from Table_with_value and checks Table_with_status using the ID to find active CO_status but on different CusCODE. This is what I have so far – obviously does not work as it should unless CusCODE and ID…
How to transpose row to columns in Oracle as shown below using Unpivot?
Source Table Cost Category Cost Category Type Q1-2020 Q2-2020 Q3-2020 Q4-2020 Employee Impacted Period Cost 10 20 0 4000 Achieved Result Cost Category Cost Category Type Quarter Year Value Employee Impacted Period Cost Q1-2020 10 Employee Impacted Period Cost Q2-2020 20 Employee Impacted Period Cost Q3-2020 0…
Oracle 11g SQL Query – Specify which duplicates to Exclude from Select Statement
I have a question about excluding duplicate values with my Oracle SQL query. Using select distinct does not work in this case. I only want to select one row for each event#, the row where Mobile = Yes. Actual Result of Current Query: Desired Result: In this ideal result, one of the rows for Event# 1 has not b…
How to select columns on the basis of column names in Oracle
I tried the following, but that doesn’t work. Answer In this example, I’m composing a select statement based on list of columns from a table (passed to function as a parameter) whose name satisfies certain condition. As it is Scott’s emp table and its columns don’t have any suffix, I&#…
Split column value by semicolon into individual rows based on another column’s value
I need to generate rows based on the quantity value. The barcode column will have value separate by semicolon “;” I want to turn each separated value into individual column. If the barcode value is less than the quantity, other rows of barcode column will be null. The barcode value will not be mor…
How to distribute stock quantity from a single cell to different aging brackets based on the the aging brackets quantities?
We have a table with item codes and current stock quantity and quantities in aging brackets whenever they were received (1-90, 91-120, etc) as follows: I need to distribute the STOCK_AS_ON_DATE quantity in the age brackets until no balance quantity is left. Example: Item A’s STOCK_AS_ON_DATE is 40377, i…
Oracle sql query to group two sets of dates if sequencials
I have the next table: EMPLOYEE CODE START_DATE END_DATE 02097368 F7H3 09/07/2018 20/10/2018 02097368 F7H3 21/10/2018 05/01/2019 02097368 F7H3 06/01/2019 12/01/2019 02097368 F7H3 13/01/2019 02/02/…
Updating all the record for a column in a table with value from another table
Hi I want to update all the values of RequestId column of IIL_CHANGE_REQUEST Table with the RequestId of TABLE_NAME_4MINS Table where REQUESTBY column in both tables are same. I am trying to do this in oracle daatabalse(sql developer) My query: But every time I do this I get an error saying: Error report R…
Using Right() or Substr() in an UPDATE statement
I have two tables : Second Table : I need to update the field HC_ID of Add_T Table and I use the following SQL statement: It doesn’t work. Also, I used the right() function and also got incorrect results. Could anyone say where is the mistake? Answer Looks like you need the last 4 characters of a string…