Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
Solved

2 conditions in select statement

Posted on 2014-01-07
Medium Priority
497 Views
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;
``````

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;
``````

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
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
• 2

LVL 28

Expert Comment

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

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

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;
``````
HTH

Ivo Stoykov
0

LVL 28

Expert Comment

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

Question has a verified solution.

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

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-orâ€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
Suggested Courses
Course of the Month10 days, 18 hours left to enroll