Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Unanswered: Question about Select query

    Hi! Can you help me to write an Oracle query to return the result as I will explain below?

    I try to write a query to return a result with a database as follow

    Day Area Variable Value
    04/01/08 A 1 50
    04/02/08 B 2 100
    04/03/08 A 3 10
    04/04/08 C 4
    04/05/08
    .....
    .....
    04/31/08

    I only want to return day and Value. Value has a 2 columns Good and Bad. The requirement are

    1. If the Variable = 3 is consider Bad it will go to bad column
    2. Should display all the day event the data is null
    3. If the value > 100 then maximum should be 60 in the good column. So the result of the query should be looked like this

    Day Value Good Value Bad
    04/01/08 50
    04/02/08 60
    04/03/08 10
    04/04/08
    04/05/08
    .....
    .....
    04/31/08

    Return result for whole month


    Thanks
    Alan

  2. #2
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking

    This looks like homework...Have you tried coding anything yet?

    HINT: Check out the "CASE" statement.
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    Haha Yah you are right. Thank for your hint yes using case is working but now I run into another problem. How can I group the same information togeter. Assume that I have 3 test result from 3 different location where 2 locations have the same result. How can I group it together?

    SELECT Day,
    CASE WHEN Variable<> 3 THEN CASE WHEN Value >100 THEN 60 ELSE Value END AS GoodValue,
    CASE WHEN Variable= 3 THEN Value END AS BadValue
    FROM YourTable
    Where Day BETWEEN @Start AND DATEADD(mm,1,@Start)

    but

    When I ran the query there are duplicate information on the day of 04/02/08 which I want it only show up one. By using case how can I group information together?

    Day_______Area_____Variable______Value
    04/01/08___A_________ 1___________50
    04/02/08___B_________ 2___________50
    04/02/08___C_________ 2___________50
    04/02/08___D_________ 3___________50
    04/03/08___A_________ 1___________50
    .....
    .....
    04/31/08

    Thanks again.

    Alan

  4. #4
    Join Date
    May 2006
    Posts
    132
    You weren't too far off.

    This is assuming you meant "if the value >= 100"

    Code:
    SQL> select * from t1;
    
    DAY       A   VARIABLE      VALUE
    --------- - ---------- ----------
    01-APR-08 A          1         50
    02-APR-08 B          2        100
    03-APR-08 A          3         10
    04-APR-08 C          4
    
    SQL> select day
      2     , case when variable != 3 and value >= 100 then 60
      3            when variable != 3 and value < 100 then value end "Good Value"
      4     , case when variable = 3 then value end "Bad Value"
      5  from t1;
    
    DAY       Good Value  Bad Value
    --------- ---------- ----------
    01-APR-08         50
    02-APR-08         60
    03-APR-08                    10
    04-APR-08

  5. #5
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Cool

    Quote Originally Posted by alanhuro
    ... How can I group it together?
    Maybe using the GROUP BY function?
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

  6. #6
    Join Date
    Apr 2008
    Posts
    5
    Thank you all for your help. Really appreciated this. I will Ace this course with all your help.

    Everything so far work great except for grouping. I try to use group by function but it does not work. How can you group the SELECT CASE?

    Another question I have is cascading the column. The Union function only work for cascading the row. Is there any function work the same as Union but for Column. Here is the situation. I have a table which has the data structure like this

    DateTime___Characteristic___Variable____________Te st_Result
    04-05-08-------Soft------------A--------------------5
    04-05-08-------Medium---------B--------------------10
    04-06-08-------Soft -----------C--------------------30
    04-07-08-------Hard------------A--------------------12
    04-07-08-------Hard------------A--------------------17

    How can I write a query with the condition are.
    1. If the variable is A return the test result with a hard charac.
    2. If the variable is B return with all test result
    3. If the variable is C return the test result with a soft charac.
    4. Need to give an average result for the same day for each variable or charateristic
    5. Sorted by Most recent day on the top.

    I will lose a lot of hair for this if I don't get help from you.

    Thanks
    Alan

  7. #7
    Join Date
    Apr 2008
    Posts
    5
    Another thing I forgot to mention for the requirements

    6. Do not return any result for any others variable exect A, B,C. if there isn't any test result for a specific variable the query should return null. The average of test result should show at the bottom of the result. Here is the format of the report

    DateTime______Result A____Result B____Result C
    04-05-08-------Null-----------Null--------Null
    04-05-08-------Null-----------10---------Null
    04-06-08-------Null-----------Null--------30
    04-07-08-------avg(12,17)---Null--------Null
    Average-------Avg(ResultA)--Avg(ResultB)--Avg(ResultC)

    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •