[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql error

Posted on 2014-08-30
7
Medium Priority
?
311 Views
Last Modified: 2014-08-31
the below code was working well for me , then suddenly it didn't , it sent me message that sql commande not probaly ended

with p as ( select &Select_Year yr , &Select_Month mn from dual)
select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff 
from (  
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, 'Sigma' 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 , 'Beta'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn)
where  acct_type(code_trans) <> 1
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
order by sum(bal) desc;

Open in new window


what is wrong in the above code ?
0
Comment
Question by:NiceMan331
  • 4
  • 2
7 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 40295206
>>>> FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn)


That closing parentheses does not have a corresponding open parentheses "("
0
 

Author Comment

by:NiceMan331
ID: 40295221
THE opened parentheses is in line 3
0
 
LVL 35

Expert Comment

by:johnsone
ID: 40295335
The open parenthesis on line 3 is closed on line 6.  The one that is being referred to by sdstuber is on line 11.
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 

Author Comment

by:NiceMan331
ID: 40295349
yes correct , i adjust it
but it sent me another message :
"bal" invalid  identifier
0
 
LVL 74

Accepted Solution

by:
sdstuber earned 2000 total points
ID: 40295486
If you put the "(" in the wrong place it would make sense that "bal" wouldn't be known because scoping would be wrong.

what does your "corrected" code look like?
0
 

Author Comment

by:NiceMan331
ID: 40296136
here it is

with p as ( select &Select_Year yr , &Select_Month mn from dual)
select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bal) Diff 
from (  
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, 'Sigma' 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)  , 'Beta'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn
where  acct_type(code_trans) <> 1
group by code_trans
having sum(bal) > = 1 or sum(bal) < = -1
order by 4 desc;

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 40296146
it is now works , the mistake in the paranthese
thanx
with p as ( select &Select_Year yr , &Select_Month mn from dual)
select code_trans , acct_type(code_trans), get_acc_name(code_trans) Acc_Name,sum(bala) Diff 
from (  
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 bala, 'Sigma' 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)  , 'Beta'
FROM acc_bal f join p on  f.year = p.yr and f.period_no= p.mn)
where  acct_type(code_trans) <> 1
group by code_trans
having sum(bala) > = 1 or sum(bala) < = -1
order by 4 desc;

Open in new window

0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.
Suggested Courses
Course of the Month17 days, 17 hours left to enroll

831 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