Home » SQL & PL/SQL » SQL & PL/SQL » AND & OR Condition Usage  () 1 Vote
AND & OR Condition Usage [message #660029] Tue, 07 February 2017 14:15 Go to next message
vharish006
Messages: 9
Registered: August 2015
Location: Chicago
Junior Member
Hi

The Fund values('Other_Govt','General','LT_Inc_Res') in fund_d.fund_group_cafr1 and ('Grants',''NI_SpecPur') in fund_d.fund_group_cafr2 has some amounts which we are trying to capture.This is a small issue but i'm unable to figure it out.

1)When the Fund " Other_Govt" is considered in FUND_d.FUND_GROUP_CAFR1 it has subsequent value in FUND_d.FUND_GROUP_CAFR2 !='Grants' which should not be considered
2) When Fund 'LT_Inc_Res' is considered in FUND_d.FUND_GROUP_CAFR1 it has subsequent value in FUND_d.FUND_GROUP_CAFR2 ='NI_SpecPur' which should be considered.
3) The other Value 'General' in FUND_d.FUND_GROUP_CAFR1 does not any condition to be considered.

I'm trying something like below but unable to restrict the fund_d.fund_group_cafr2 !='Grants' amount being adding.

Please let me know how can i achieve this ..

[CODE]
SELECT account_d.account,
account_d.account_group_cafr,
sum(cafr_summary_f.gl_balance_act)/1000
FROM account_d,fund_d,cafr_summary_f
where account_d.account= cafr_summary_f.account
AND cafr_summary_f.fund=fund_d.fund
AND cafr_summary_f.set_of_books='1'
AND cafr_summary_f.period='ADJ-15'
AND (fund_d.fund_group_cafr1 in ('Other_Govt','General','LT_Inc_Res') OR fund_d.fund_group_cafr2=('NI_SpecPur') AND fund_d.fund_group_cafr2 <>'Grants')
AND account_d.account_group_cafr in ('Cash')
group by account_d.account_group_cafr,account_d.account
[CODE]

Thanks in Advance

[Updated on: Tue, 07 February 2017 14:41]

Report message to a moderator

Re: AND & OR Condition Usage [message #660030 is a reply to message #660029] Tue, 07 February 2017 14:29 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: AND & OR Condition Usage [message #660031 is a reply to message #660030] Tue, 07 February 2017 14:42 Go to previous messageGo to next message
vharish006
Messages: 9
Registered: August 2015
Location: Chicago
Junior Member
Updated.please let me know if it still needs to be modified.
Re: AND & OR Condition Usage [message #660032 is a reply to message #660029] Tue, 07 February 2017 15:08 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
vharish006 wrote on Tue, 07 February 2017 12:15
Hi

The Fund values('Other_Govt','General','LT_Inc_Res') in fund_d.fund_group_cafr1 and ('Grants',''NI_SpecPur') in fund_d.fund_group_cafr2 has some amounts which we are trying to capture.This is a small issue but i'm unable to figure it out.

1)When the Fund " Other_Govt" is considered in FUND_d.FUND_GROUP_CAFR1 it has subsequent value in FUND_d.FUND_GROUP_CAFR2 !='Grants' which should not be considered
2) When Fund 'LT_Inc_Res' is considered in FUND_d.FUND_GROUP_CAFR1 it has subsequent value in FUND_d.FUND_GROUP_CAFR2 ='NI_SpecPur' which should be considered.
3) The other Value 'General' in FUND_d.FUND_GROUP_CAFR1 does not any condition to be considered.

I'm trying something like below but unable to restrict the fund_d.fund_group_cafr2 !='Grants' amount being adding.

Please let me know how can i achieve this ..

SELECT account_d.account,
       account_d.account_group_cafr,
       sum(cafr_summary_f.gl_balance_act)/1000
FROM   account_d,fund_d,cafr_summary_f
where  account_d.account= cafr_summary_f.account
AND    cafr_summary_f.fund=fund_d.fund
AND    cafr_summary_f.set_of_books='1'
AND    cafr_summary_f.period='ADJ-15'
AND    (fund_d.fund_group_cafr1 in  ('Other_Govt','General','LT_Inc_Res') OR fund_d.fund_group_cafr2=('NI_SpecPur') AND fund_d.fund_group_cafr2 <>'Grants')
AND  account_d.account_group_cafr in ('Cash')
group by account_d.account_group_cafr,account_d.account

Thanks in Advance
*BlackSwan corrected {code} tags
Re: AND & OR Condition Usage [message #660033 is a reply to message #660032] Tue, 07 February 2017 15:12 Go to previous messageGo to next message
BlackSwan
Messages: 25856
Registered: January 2009
Location: SoCal
Senior Member
We don't have your tables.
We don't have your data.

I still don't understand exactly what is expected/desired results.
>but unable to restrict the fund_d.fund_group_cafr2 !='Grants' amount being adding.
adding what to where & why?
Re: AND & OR Condition Usage [message #660053 is a reply to message #660029] Wed, 08 February 2017 01:28 Go to previous messageGo to next message
Michel Cadot
Messages: 65387
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
From your previous topic:

Michel Cadot wrote on Mon, 09 January 2017 18:50

From your previous topics:

BlackSwan wrote on Mon, 10 August 2015 19:12
Welcome to this forum.

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
...
Barbara formatted your post in your next topic and you still continued to post not formatted.

So once more:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Indent the code, use code tags and align the columns in result.
Also always post your Oracle version, with 4 decimals, as solution depends on it.
With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Re: AND & OR Condition Usage [message #660063 is a reply to message #660029] Wed, 08 February 2017 03:25 Go to previous message
cookiemonster
Messages: 13014
Registered: September 2008
Location: Rainy Manchester
Senior Member
vharish006 wrote on Tue, 07 February 2017 20:15
Hi

1)When the Fund " Other_Govt" is considered in FUND_d.FUND_GROUP_CAFR1 it has subsequent value in FUND_d.FUND_GROUP_CAFR2 !='Grants' which should not be considered
2) When Fund 'LT_Inc_Res' is considered in FUND_d.FUND_GROUP_CAFR1 it has subsequent value in FUND_d.FUND_GROUP_CAFR2 ='NI_SpecPur' which should be considered.
3) The other Value 'General' in FUND_d.FUND_GROUP_CAFR1 does not any condition to be considered.

It's not obvious what you mean by "has subsequent value .... which should not be considered"

It is obvious that if you want to do different checks based on the value of FUND_GROUP_CAFR1 then you need more than one check on that column.
So you need something in the form:
AND ((FUND_d.FUND_GROUP_CAFR1 = 'Other_Govt' AND ....)
     OR (FUND_d.FUND_GROUP_CAFR1 = 'General' AND ....)
     OR (FUND_d.FUND_GROUP_CAFR1 = 'LT_Inc_Res' AND ....)
    )
Previous Topic: Display only 10 charecters from column
Next Topic: generate dynamic query and avoiding recompiling query
Goto Forum:
  


Current Time: Thu Feb 22 08:41:48 CST 2018

Total time taken to generate the page: 0.06818 seconds