Styjun

What kind of floor tile is this?

What are some good ways to treat frozen vegetables such that they behave like fresh vegetables when stir frying them?

How to explain what's wrong with this application of the chain rule?

awk assign to multiple variables at once

Were Persian-Median kings illiterate?

What features enable the Su-25 Frogfoot to operate with such a wide variety of fuels?

Creating two special characters

Why does the Sun have different day lengths, but not the gas giants?

What is the highest possible scrabble score for placing a single tile

Pre-mixing cryogenic fuels and using only one fuel tank

How could a planet have erratic days?

How to get directions in deep space?

Is this toilet slogan correct usage of the English language?

Taxes on Dividends in a Roth IRA

Are Captain Marvel's powers affected by Thanos breaking the Tesseract and claiming the stone?

How do you make your own symbol when Detexify fails?

How can I write humor as character trait?

US tourist/student visa

Did the UK lift the requirement for registering SIM cards?

How to preserve electronics (computers, iPads and phones) for hundreds of years

What to do when eye contact makes your coworker uncomfortable?

Is there any evidence that Cleopatra and Caesarion considered fleeing to India to escape the Romans?

C++ copy constructor called at return

Can you use Vicious Mockery to win an argument or gain favours?

MYSQL - How do I unpivot my data to get the following output below?

DB Error=ORA-01407: cannot update (“XXX”.“XXX”.“VALUE”) to NULL.How to get the following output?Materialized View involving UNION ALL operator in FROM with ON COMMITPassing Numeric Variable into Simple oracle queryHow to get output like below?how to get following output in oracle using query?Updating all rows in one table who have 1 match, more than 1 match, or 0 matches in other tableSELECT WHERE Query Spanning Numerous Rows Linked by RotationID columnIndex is not getting usedOracle index barely speeding up aggregate calculations

0

Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID

 SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
 CASE 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
 ELSE 'DUE IN 4+ WEEKS'
 END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot 
( 
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC

oracle

share|improve this question

edited 16 hours ago

Raymond Nijland

8,88121329

asked 17 hours ago

Dean NewsteadDean Newstead

51

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  MySQL doesn't have an UNPIVOT... this looks more like oracle to me...
  
  – Nick
  16 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  " this looks more like oracle to me." it is @Nick notice the (+) operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use (+) annymore by the way
  
  – Raymond Nijland
  16 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
  
  – Nick
  16 hours ago
  

  
  
  
  

add a comment | 

0

Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID

 SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
 CASE 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
 ELSE 'DUE IN 4+ WEEKS'
 END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot 
( 
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC

oracle

share|improve this question

edited 16 hours ago

Raymond Nijland

8,88121329

asked 17 hours ago

Dean NewsteadDean Newstead

51

• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  MySQL doesn't have an UNPIVOT... this looks more like oracle to me...
  
  – Nick
  16 hours ago
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  " this looks more like oracle to me." it is @Nick notice the (+) operator which is Oracle's LEFT or RIGHT join feature operator on top off the old comma join SQL standard.. Topicstarter Oracle advices not to use (+) annymore by the way
  
  – Raymond Nijland
  16 hours ago
  
  

  
  
  
  


• 
  
  
  

  
  1
  

  
  
  
  
  
  

  
  
  @RaymondNijland I wasn't 100% sure it was oracle (definitely sure it wasn't MySQL though!) so I didn't want to edit the tags. Thanks for confirming and doing that.
  
  – Nick
  16 hours ago
  

  
  
  
  

add a comment | 

Never used MYSQL unpivot much and its a complex example so thanks in advance.
I am looking to get the following query to be Unpivoted by COD_DUE_DATE. So I want the CASE_ID's down the column and along the column I want 4 parts:- Less than a week, due in a week, due in 2 week, due in 3 weeks ect.
Then the values in the middle will be a count of CASE_ID

 SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
 CASE 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
 ELSE 'DUE IN 4+ WEEKS'
 END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot 
( 
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC

oracle

share|improve this question

edited 16 hours ago

Raymond Nijland

8,88121329

asked 17 hours ago

Dean NewsteadDean Newstead

51

 SELECT DISTINCT a.CASE_ID, b.BUCKET, a.CUSTOMER_OCCUPATION_DATE,
 CASE 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE-10000 AND SYSDATE+7 THEN 'LESS THAN 1 WEEK'
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+8 AND SYSDATE+14 THEN 'DUE IN 1 WEEK' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+15 AND SYSDATE+21 THEN 'DUE IN 2 WEEKS' 
 WHEN a.CUSTOMER_OCCUPATION_DATE BETWEEN SYSDATE+22 AND SYSDATE+28 THEN 'DUE IN 3 WEEKS'
 ELSE 'DUE IN 4+ WEEKS'
 END as COD_DUE_DATE
FROM FND_COMPLAINTS a, FTTP_NEWSITES_DWELL_DETAIL b,
unpivot 
( 
a.CUSTOMER_OCCUPATION_DATE
for COD_DUE_DATE in (LESS THAN 1 WEEK)
)
WHERE a.NAD_KEY = b.NAD(+) AND a.CUSTOMER_OCCUPATION_DATE IS NOT NULL
ORDER BY a.CUSTOMER_OCCUPATION_DATE ASC

share|improve this question

edited 16 hours ago

Raymond Nijland

8,88121329

asked 17 hours ago

Dean NewsteadDean Newstead

51

share|improve this question

edited 16 hours ago

Raymond Nijland

8,88121329

asked 17 hours ago

Dean NewsteadDean Newstead

51

edited 16 hours ago

Raymond Nijland

8,88121329

edited 16 hours ago

Raymond Nijland

8,88121329

asked 17 hours ago

Dean NewsteadDean Newstead

51

asked 17 hours ago

Dean NewsteadDean Newstead

51