OraFAQ Forum: Training & Certification » zig-zag calculation in pl/sql (merged)

Home » Other » Training & Certification » zig-zag calculation in pl/sql (merged)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

zig-zag calculation in pl/sql (merged) [message #292015]

Mon, 07 January 2008 08:03

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

zig-zag calculation in pl/sql [message #292130 is a reply to message #292015]

Mon, 07 January 2008 23:40

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

Report message to a moderator

Re: sql logic [message #292131 is a reply to message #292018]

Mon, 07 January 2008 23:41

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.

Report message to a moderator

Re: sql logic [message #292145 is a reply to message #292131]

Tue, 08 January 2008 00:51

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Question about looping query [message #292179 is a reply to message #292015]

Tue, 08 January 2008 02:46

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

Report message to a moderator

Re: Question about looping query [message #292180 is a reply to message #292179]

Tue, 08 January 2008 02:53

pablolee
Messages: 2882
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

Need Sql query [message #292231 is a reply to message #292015]

Tue, 08 January 2008 04:22

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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 1525 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

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

Report message to a moderator

Re: Need Sql query [message #292244 is a reply to message #292242]

Tue, 08 January 2008 04:52

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

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 1486 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

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

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

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

Report message to a moderator

Re: Need Sql query [message #292817 is a reply to message #292265]

Wed, 09 January 2008 23:41

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 1492 times)

Report message to a moderator

Re: Need Sql query [message #292818 is a reply to message #292817]

Wed, 09 January 2008 23:47

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

1/ What did you try?
2/ Post a test case: create table and insert statements

Regards
Michel

Report message to a moderator

Re: Need Sql query [message #292902 is a reply to message #292818]

Thu, 10 January 2008 02:36

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.

Report message to a moderator

Re: Need Sql query [message #292916 is a reply to message #292902]

Thu, 10 January 2008 03:02

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Please post the solution you get, this may help others.

Regards
Michel

Report message to a moderator

Re: Need Sql query [message #293003 is a reply to message #292916]

Thu, 10 January 2008 09:24

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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 12:28:00 CST 2024