Home » Other » Training & Certification » zig-zag calculation in pl/sql (merged)
zig-zag calculation in pl/sql (merged) Mon, 07 January 2008 08:03
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member
Hi experts,

I have some logic doubts in sql query, we have bank statements data in that we want to calculate credit and debit
for closed balance sheet:

format
-------
a+b+c = z
Z+a+b+c=z1
z1+a+b+c=z2

please anyone help me how to write query to calculate closed balance output(z) from using debit,credit and open balancing columns.

[Updated on: Mon, 07 January 2008 23:32]

Report message to a moderator

Re: sql logic [message #292018 is a reply to message #292015] Mon, 07 January 2008 08:13
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

You can do it using analytic form of SUM function.

Regards
Michel
zig-zag calculation in pl/sql [message #292130 is a reply to message #292015] Mon, 07 January 2008 23:40
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member

Hi experts,

I have 3 columns in table x,y,z ,we want to calculate this
column in following format

x+y+z = a
a+x+y+z =a1
a1+x+y+z =a2
....
...
a(n)+x+y+z =a+1

please help me how to write pl/sql for this following above concept.Actually i have very basic knoweledge in pl/sql

Re: sql logic [message #292131 is a reply to message #292018] Mon, 07 January 2008 23:41
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member
sorry sir,

I have some logic doubts in sql query, we have bank statements data in that we want to calculate credit and debit
for closed balance sheet:

format
-------
a+b+c = z
Z+a+b+c=z1
z1+a+b+c=z2

please anyone help me how to write query to calculate closed balance output(z) from using debit,credit and open balancing columns.

Re: sql logic [message #292145 is a reply to message #292131] Tue, 08 January 2008 00:51
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 Michel Cadot wrote on Mon, 07 January 2008 15:13 First, please read and follow OraFAQ Forum Guide, especially "How to format your post?" section. Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button. You can do it using analytic form of SUM function. Regards Michel

Did you try to search and SUM?

Regards
Michel
Question about looping query [message #292179 is a reply to message #292015] Tue, 08 January 2008 02:46
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member

Dear friends,

i have tried the following concept in sql query,actually x (no of rows)and y(only one value) are the inputs

we have all x series values and only y value we want to find y1,y2,y3 within single query.

table datas:
X=100,200....n ,y=2000

eg:
x + y =y1
x1+ y1 =y2
x2+ y2 =y3

- thanks
Re: Question about looping query [message #292180 is a reply to message #292179] Tue, 08 January 2008 02:53
 pablolee Messages: 2844Registered: May 2007 Location: Scotland Senior Member
In order to make sense of your requirements, and to prevent lots of to-ing and fro-ing with guess and counter guess. Please provide create table and insert scripts to create a valid test case along with expected results and oracle version as well as what you have tried already.
Ahhh, I see that what you have tried already is to repeatedly ask the same question. Your on your own here. Please go back to you original post and do as Michel asked you.
here

[Updated on: Tue, 08 January 2008 02:56]

Report message to a moderator

Need Sql query [message #292231 is a reply to message #292015] Tue, 08 January 2008 04:22
 swamitvk Messages: 14Registered: November 2007 Junior Member

Hi

we are maintaining Accounting statement in which

data are to be maintainted in the following manner.

EX.

opening balance --- 10000
===============

Debit ######credit#######current balance

100 ######## --- ###### 900

--- ########200 #######1100

( We couldn't align data in column wise that's why
entered HASH symbol among columns)

current balance = opening balance + credit - debit

can Anyone help me to write sql query to get the above result.

Regards

swami

[Updated on: Tue, 08 January 2008 04:28]

Report message to a moderator

Re: Need Sql query [message #292234 is a reply to message #292231] Tue, 08 January 2008 04:27
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Still the same answer to your previous same topics today, yesterday and so on.

STOP REPEATING THE SAME QUESTION OVER TOPIC SEVERAL TIMES PER DAY.

And format your post as requested OraFAQ Forum Guide, "How to format your post?" section.

Regards
Michel

[Updated on: Tue, 08 January 2008 04:30]

Report message to a moderator

Re: Need Sql query [message #292239 is a reply to message #292231] Tue, 08 January 2008 04:41
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member
sorry sir,

i dont know what is my mistake,because we have wide datas in the table .so that only we gave some logic example.

- Thank you.

• Attachment: test.doc

[Updated on: Tue, 08 January 2008 04:52]

Report message to a moderator

Re: Need Sql query [message #292242 is a reply to message #292234] Tue, 08 January 2008 04:42
 swamitvk Messages: 14Registered: November 2007 Junior Member

Hi

Today only I asked this question. Any way,

if my questions troubled you ,I sorry for that.

Regards

swami
Re: Need Sql query [message #292244 is a reply to message #292242] Tue, 08 January 2008 04:52
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
@swamitvk,
Read the whole topic and you will the same question even if it is asked with 2 different member names that are sharing the same IP.

@SRIVASUWIN,
Create a small test case and try to do it with the SUM function.
We don't donwload DOC file, create a TXT one.

Regards
Michel

[Updated on: Tue, 08 January 2008 04:53]

Report message to a moderator

Re: Need Sql query [message #292247 is a reply to message #292244] Tue, 08 January 2008 05:03
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member
ok sure sir,

But sum function give total values of table columns but we need single outout for each rows.

Now i have attached the txt formate please verify the test table object.

- Thank you sir.
• Attachment: test.txt

[Updated on: Tue, 08 January 2008 05:07]

Report message to a moderator

Re: Need Sql query [message #292249 is a reply to message #292247] Tue, 08 January 2008 05:08
 dhananjay Messages: 635Registered: March 2002 Location: Mumbai Senior Member
Michel wrote :

 Quote: You can do it using analytic form of SUM function.

search for the above function.

[edit:message body]

regards,

[Updated on: Tue, 08 January 2008 05:09]

Report message to a moderator

Re: Need Sql query [message #292265 is a reply to message #292247] Tue, 08 January 2008 05:39
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
 Quote: Now i have attached the txt formate please verify the test table object.

You should also post the create table and insert statements to make the test.

The answer is still the same as the one I repeated: SUM analytic function and there is an example in the link I posted.

Regards
Michel
Re: Need Sql query [message #292817 is a reply to message #292265] Wed, 09 January 2008 23:41
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member

Thank you sir,

I have tried this concept by using scott.emp tables it generate correct answer ,but this concept not working for our production datas

Here with i have attached our production data plz check the attachement

we want to partitoned by cc (c1,c2) by v_date but this is not possible in this concept.

• Attachment: new1.txt
Re: Need Sql query [message #292818 is a reply to message #292817] Wed, 09 January 2008 23:47
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
1/ What did you try?
2/ Post a test case: create table and insert statements

Regards
Michel
Re: Need Sql query [message #292902 is a reply to message #292818] Thu, 10 January 2008 02:36
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member
Thanks a lot sir

I got the result by using the sum() fuction .

Really thank you very much.

Re: Need Sql query [message #292916 is a reply to message #292902] Thu, 10 January 2008 03:02
 Michel Cadot Messages: 65144Registered: March 2007 Location: Nanterre, France, http://... Senior MemberAccount Moderator
Please post the solution you get, this may help others.

Regards
Michel
Re: Need Sql query [message #293003 is a reply to message #292916] Thu, 10 January 2008 09:24
 SRIVASUWIN Messages: 45Registered: October 2006 Location: TRICHY Member
Sure sir,

Table contains column(vrno,cc,amount)

cc contain = 2 values c1,c2 i have trim 1,2 in the cc column
then partition the cc and order by rownum i got the result perfectly.
```select b.* ,amount1
from (select a.*,
sum(amount)
over (partition by a.cc1
order by rownum
range unbounded preceding) amount1
from (select x.*, rtrim(cc,'12') cc1
from (SELECT * from test_table) x
) a
)b```

[Edit MC: format and code tags, next time do it yourself]

[Updated on: Thu, 10 January 2008 10:14] by Moderator

Report message to a moderator

 Previous Topic: D2K interview questions Next Topic: After passing the OCA exam !!!
Goto Forum:

Current Time: Sat Aug 19 13:51:36 CDT 2017

Total time taken to generate the page: 0.05405 seconds