Home » Other » Training & Certification » zig-zag calculation in pl/sql (merged)
zig-zag calculation in pl/sql (merged) [message #292015] Mon, 07 January 2008 08:03 Go to next message
SRIVASUWIN
Messages: 45
Registered: 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.

- Thanx in Advance.

[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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
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
zig-zag calculation in pl/sql [message #292130 is a reply to message #292015] Mon, 07 January 2008 23:40 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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.

- Thanx in Advance.
Re: sql logic [message #292145 is a reply to message #292131] Tue, 08 January 2008 00:51 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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

please help me if it is not possible in sql then please help me to guide in pl/sql

- thanks
Re: Question about looping query [message #292180 is a reply to message #292179] Tue, 08 January 2008 02:53 Go to previous messageGo to next message
pablolee
Messages: 2836
Registered: 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

icon6.gif  Need Sql query [message #292231 is a reply to message #292015] Tue, 08 January 2008 04:22 Go to previous messageGo to next message
swamitvk
Messages: 14
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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.


please help me.please verify the attachement.


- Thank you.


  • Attachment: test.doc
    (Size: 0.16KB, Downloaded 212 times)

[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 Go to previous messageGo to next message
swamitvk
Messages: 14
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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.
Then post your tries.
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 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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
    (Size: 0.91KB, Downloaded 217 times)

[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 Go to previous messageGo to next message
dhananjay
Messages: 635
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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
    (Size: 0.66KB, Downloaded 210 times)
Re: Need Sql query [message #292818 is a reply to message #292817] Wed, 09 January 2008 23:47 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous messageGo to next message
SRIVASUWIN
Messages: 45
Registered: 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 Go to previous messageGo to next message
Michel Cadot
Messages: 64151
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account 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 Go to previous message
SRIVASUWIN
Messages: 45
Registered: 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: new to this forum
Next Topic: Interesting SQL Query
Goto Forum:
  


Current Time: Fri Dec 09 19:43:22 CST 2016

Total time taken to generate the page: 0.10232 seconds