Solved

2 conditions in select statement

Posted on 2014-01-07
4
484 Views
Last Modified: 2014-01-09
hi
i have this view

create or replace view all_acc_bal as
with p as ( select 2013 yr , 12 mn from dual ) 
select 
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t join p on  t.year = p.yr and t.period_no= p.mn
union all
select 
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn;

Open in new window


then , this select

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
and code_trans not in('15000','15120','15550','20000','50000','11000')
order by sum(bal) desc;

Open in new window


1st of all : could i use direct select statament without creating the view every time to change parameter values ?
2nd : when i run the last select statement , it return records having values like 15000 , 11000
inspite i select not in
0
Comment
Question by:NiceMan331
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39764424
1) yes for this view query, you can use direct select statement.

create or replace view all_acc_bal as
with p as ( select 2013 yr , 12 mn from dual )
select
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t join p on  t.year = p.yr and t.period_no= p.mn
union all
select
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn;

try the below :

select
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t  where t.year = 2013 and t.period_no= 12
union all
select
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f where f.year = 2013 and f.period_no= 12;
0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 39764425
2. Try this

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
where code_trans not in('15000','15120','15550','20000','50000','11000')
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
order by sum(bal) desc;


1 . Yes you can use direct select .
0
 
LVL 22

Accepted Solution

by:
Ivo Stoykov earned 500 total points
ID: 39764427
Hi

Generally you could use your create view statement in the FROM clause.
Then you could move outer where clause into FROM clause which is better.

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from (with p as ( select 2013 yr , 12 mn from dual ) 
select 
code_trans, -op_deb op_deb, -tr_deb tr_deb,  -t_deb t_deb, -op_cr op_cr, -tr_cr tr_cr,  -t_cr t_cr,  -Bal bal, 'Alpha' Syst
FROM h2002_acc_bal t join p on  t.year = p.yr and t.period_no= p.mn
where  code_trans not in('15000','15120','15550','20000','50000','11000') -- probably here
union all
select 
acc_no, op_deb, tr_deb , op_deb+ tr_deb t_deb , op_cr , tr_cr , op_cr+ tr_cr t_cr,
(op_deb+tr_deb)-(op_cr+tr_cr) Bal , 'Intacc'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn)
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
-- and code_trans not in('15000','15120','15550','20000','50000','11000')
order by sum(bal) desc;

Open in new window

HTH

Ivo Stoykov
0
 
LVL 28

Expert Comment

by:Naveen Kumar
ID: 39764429
2) Try the below. I have moved that to the where clause.

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
where code_trans not in('15000','15120','15550','20000','50000','11000')
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
order by sum(bal) desc;

If this does not give you the expected results for some reason, then you can try the below as well. I have adjusted the paranthesis in the condition.

select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff from all_acc_bal
group by code_trans
having ( ( sum(bal) > = 1 or sum(bal) < = -1 )
and code_trans not in('15000','15120','15550','20000','50000','11000') )
order by sum(bal) desc;

Thanks,
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
How to Create User-Defined Aggregates in Oracle Before we begin creating these things, what are user-defined aggregates?  They are a feature introduced in Oracle 9i that allows a developer to create his or her own functions like "SUM", "AVG", and…
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

756 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question