?
Solved

2 conditions in select statement

Posted on 2014-01-07
4
Medium Priority
?
496 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 2000 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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
From implementing a password expiration date, to datatype conversions and file export options, these are some useful settings I've found in Jasper Server.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

771 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