AND & OR Condition Usage [message #660029] |
Tue, 07 February 2017 14:15 |
|
vharish006
Messages: 70 Registered: August 2015 Location: Chicago
|
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 #660032 is a reply to message #660029] |
Tue, 07 February 2017 15:08 |
|
BlackSwan
Messages: 26766 Registered: January 2009 Location: SoCal
|
Senior Member |
|
|
vharish006 wrote on Tue, 07 February 2017 12:15Hi
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 #660053 is a reply to message #660029] |
Wed, 08 February 2017 01:28 |
|
Michel Cadot
Messages: 68665 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:12Welcome 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 |
cookiemonster
Messages: 13925 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
vharish006 wrote on Tue, 07 February 2017 20:15Hi
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 ....)
)
|
|
|