Results 1 to 8 of 8

Thread: Derived tables

  1. #1
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401

    Unanswered: Derived tables

    I am new,migrating from SQL server to oracle

    Can you help me find out whats wrong in this query ???

    Code:
    SELECT * FROM
    (SELECT 'TASK' "Task1",  tt.name "Type", TRUNC(tt.CREATION_DATE) "Date", COUNT(1) "Total"
    FROM jtf_task_types_tl tt, jtf_tasks_b t
    WHERE tt.task_type_id = t.task_type_id
    AND t.SOURCE_OBJECT_TYPE_CODE = 'TASK'
    --AND TASK='Media'
    --AND tt.creation_date BETWEEN <START DATE parameter> AND <END DATE paramerter>
    GROUP BY tt.name, tt.task_type_id, TRUNC(tt.creation_date)  
    UNION
    SELECT 'Media' "Task1", t.media_type_name "Type", TRUNC(t.CREATION_DATE) "Date", COUNT(1) "Total"
    FROM apps.ieu_uwq_media_types_tl t, apps.ieu_uwq_media_actions a
    WHERE a.media_type_id = t.media_type_id
    --AND t.creation_date BETWEEN <START DATE parameter> AND <END DATE parameter>
    GROUP BY t.media_type_name, TRUNC(t.creation_date)) xx  WHERE xx.Task1='Media'
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    what errors are you getting?


    for one, you group by "tt.task_type_id" but it is not in your select statement

    plus, your where clause at the end eliminates the upper half of the Union, so why bother even having that half at all?
    Last edited by The_Duck; 12-19-03 at 12:09.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by The_Duck
    what errors are you getting?
    It says that invalid column name for XX.Task1
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208
    Remove the double qoutes "

    SELECT * FROM
    (SELECT 'TASK' Task1, tt.name Type, blah blah blah.

    Just out of curiousity, this is just a test query right? You dont really restrict the inline view to only those records equal to 'Media'? If so, there is no need for the union.

  5. #5
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Originally posted by carloa
    Remove the double qoutes "

    SELECT * FROM
    (SELECT 'TASK' Task1, tt.name Type, blah blah blah.

    Just out of curiousity, this is just a test query right? You dont really restrict the inline view to only those records equal to 'Media'? If so, there is no need for the union.
    it will depend on user selection, is user passes task1 value Media then I have to show Media data if user passes Task then xx.Task1=Task there are many more subquery to join he Union band wagon
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  6. #6
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    removing quotes has not solved my problem
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

  7. #7
    Join Date
    Jul 2003
    Posts
    2,296
    I agree with above as I mentioned in my edited post.

    No quotes seems to work in this test-case:
    PHP Code:
    11:15:06 kod:integrationSELECT FROM (
    11:15:07   2  SELECT
    11
    :15:07   3    'Media' Task1 FROM dual
    11
    :15:07   4    UNION
    11
    :15:07   5  SELECT 
    11
    :15:07   6    'TASK1' Task1 FROM duala
    11
    :15:07   7  WHERE a.Task1 'Media';

    TASK1
    -----
    Media

    Elapsed
    00:00:00.00
    11
    :15:09 kod:integration
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    Thanx man it has worked please discard the previous message
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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