Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2005
    Posts
    18

    Unanswered: "And/Or" part ..not working in sql query.

    Hi Friends,
    This is my first post,I hope you guys would help me out.I have a query and a small question.I don't know why the "and/or" part in the query is not working.When I make it work separately,it works,but together it doesnt works.Please help me if you can cause this is really important for me,thanks.

    select
    tr.tournament_id,tr.tournament_datetime,t.tourname nt_name,ft.transaction_type,ftd.fee_currency,
    ftd.fee,ftpd.currency,ftpd.amount

    from
    finalteam as ft,
    final_team_details as ftd,
    final_player_details as ftpd,
    team_results as tr,
    team as t

    where
    tr.tournament_id=t.tournament_id

    and ((ft.transaction_id = ftd.transaction_id and ftd.transaction_date=ft.transaction_date)
    or (ft.transaction_id = ftpd.transaction_id and ft.transaction_date=ftpd.transaction_date))

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I don't really know what you are trying to do, but since you have 5 tables, you need to join all the tables All the join predicates should be "ANDed" and not just with an "OR". Of course, as I said, I really don't know what you are trying to achieve.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jan 2005
    Posts
    18

    thanks marcus

    thanks marcus,
    I didnt posted the whole query for simplicity,here goes the full query:

    select
    tr.tournament_id,tr.tournament_datetime,t.tourname nt_name,ft.transaction_type,ftd.fee_currency,
    sum(ftd.fee) as FEES,ftpd.currency,sum(ftpd.amount) as MONEYPAYED

    from
    finalteam as ft,
    final_team_details as ftd,
    final_player_details as ftpd,
    team_results as tr,
    team as t


    where
    (
    (
    tr.tournament_id=t.tournament_id
    and tr.status='co'
    and tr.tournament_id=ftd.tournament_id
    and ft.transaction_status='finalize'
    and ftd.fee_currency is not null
    and ft.transaction_type in ('group1','group2')
    )

    and
    (
    (
    ft.transaction_id = ftd.transaction_id
    and ftd.transaction_date=ft.transaction_date)
    or (ft.transaction_id = ftpd.transaction_id
    and ftpd.transaction_date=ft.transaction_date
    )
    )
    )



    group by
    tr.tournament_id,
    tr.tournament_datetime,
    t.tournament_name,
    ft.transaction_type,
    ftd.fee_currency,
    ftpd.currency


    Actually,I want that if transaction_type is in 'group1' then it should pick up details from
    final_team_details matching on transaction_id and transaction_date or else if type is in 'group2'
    it should pick details from final_player_details same matching criteria as before.
    Now can you help me,it's really really important for me.

  4. #4
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Don't miss to analyze the query using Visual Explain .. I have come across circumstances when the db2 optimizer is 'confused' when you give too many join conditions .. Of course, this depends on the size of the tables, cardinality, data distribution etc, but just in case it helps

    Cheers
    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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