OraFAQ Forum: SQL & PL/SQL » Can we Write PLSQL concept in WITH clause

Home » SQL & PL/SQL » SQL & PL/SQL » Can we Write PLSQL concept in WITH clause (oracle 11g)

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

Can we Write PLSQL concept in WITH clause [message #645134]

Fri, 27 November 2015 03:57

graphe
Messages: 23
Registered: October 2014

Junior Member

I wrote some code by using WITH Clause for calculating the parent Transactions by using Child transactions.
Here i got One doubt right now i know we have only 3 level Parents only. so i wrote the code manually 3 level's of caluculating Parent transactions.
May in future if the level of parent's may increase the my code will fail. So, now i want to make my code for calculating no parent level calculate on runtime and calculate the level's in that manner only.
Here i know how to calculate parent level's by using CONNECT BY clause and LEVEL as one column to get the level information. But Here i made all of code in WITH Clause as temporary storage on runtime only. So if i want to calculate value no of times coming on run time i don;t think that can be possible in WITH clause Because it is completely belongs to SQL . I think it won't allow the variables or any PLSQL stuff in it.
Please help me in this Situation, I am attaching My Code here for clarification. Thank you to show patience on by reading my problem until now.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #645135 is a reply to message #645134]

Fri, 27 November 2015 04:38

cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester

Senior Member

You haven't actually attached any code

Report message to a moderator

Can we Write PLSQL concept in WITH clause [message #645137 is a reply to message #645135]

Fri, 27 November 2015 04:48

graphe
Messages: 23
Registered: October 2014

Junior Member

I wrote some code by using WITH Clause for calculating the parent Transactions by using Child transactions.
Here i got One doubt right now i know we have only 3 level Parents only. so i wrote the code manually 3 level's of caluculating Parent transactions.
May in future if the level of parent's may increase the my code will fail. So, now i want to make my code for calculating no parent level calculate on runtime and calculate the level's in that manner only.
Here i know how to calculate parent level's by using CONNECT BY clause and LEVEL as one column to get the level information. But Here i made all of code in WITH Clause as temporary storage on runtime only. So if i want to calculate value no of times coming on run time i don;t think that can be possible in WITH clause Because it is completely belongs to SQL . I think it won't allow the variables or any PLSQL stuff in it.
Please help me in this Situation, I am attaching My Code here for clarification. Thank you to show patience on by reading my problem until now.

Attachment: Parent&Child transactions.txt
(Size: 3.99KB, Downloaded 1558 times)

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #645149 is a reply to message #645134]

Fri, 27 November 2015 09:24

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Before, Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" or "Preview Quick Reply" button to verify.
Also always post your Oracle version, with 4 decimals.

If you don't know how to format your code, learn it using SQL Formatter.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #645275 is a reply to message #645149]

Wed, 02 December 2015 01:45

graphe
Messages: 23
Registered: October 2014

Junior Member

Hi Mr. Michel
here i am attached all of my doubt details(scripts) . Please work on it by taking sample data.

Rules :
1. Same account number would be repeat in different branches but not in the same branch.
2. By the TRAN_CD we are dividing that whether it is DEBIT or CREDIT Transaction.
3. By the combination of (account and branch) we are generating one ID(Ledger_id) . It will connect the parent and child by using ACCOUNT table and some other tables.
4. so now with child and parent information we will derive the parent Transaction information.

Attachment: WITH CLAUSE TABLE STRUCTUURES.SQL
(Size: 19.91KB, Downloaded 1381 times)

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #645571 is a reply to message #645275]

Wed, 09 December 2015 00:09

graphe
Messages: 23
Registered: October 2014

Junior Member

Hi mr.Cookie monster and Michael
I am waiting for your answer with some explanation. please find a solution as soon as possible. i am waiting for your reply.

Thanks in advance

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #645572 is a reply to message #645571]

Wed, 09 December 2015 00:17

pablolee
Messages: 2882
Registered: May 2007
Location: Scotland

Senior Member

Read Michel's last post, click on an read the links. Then post again with the appropriate information, in the appropriate medium formatted appropriately.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646376 is a reply to message #645572]

Tue, 29 December 2015 03:13

graphe
Messages: 23
Registered: October 2014

Junior Member

I am expecting the result from your website But still i didn't get any response. I hope you will come up with your answer very soon.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646377 is a reply to message #646376]

Tue, 29 December 2015 03:21

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

We are expecting you follow our guide and post what we ask but still we get no answer. I hope you will come up with your answer very soon.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646378 is a reply to message #646377]

Tue, 29 December 2015 03:29

graphe
Messages: 23
Registered: October 2014

Junior Member

I gave every thing what you need in my problem i am attached every thing (Files ) what it really needs. i attached the scripts in the by using SQLformatter only. what else i missed i don't know please consider the results what i have attached. I hope that you may give the result. Thank you advance.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646383 is a reply to message #646378]

Tue, 29 December 2015 04:59

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

OK, now where are we?
You say we have all we need, we say no.
So we are waiting for something and you are waiting for something.
We can forget this topic for ever as we did in the past 3 weeks; and you?

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646387 is a reply to message #646383]

Tue, 29 December 2015 06:24

graphe
Messages: 23
Registered: October 2014

Junior Member

Now we are at calculation of parent Account Information by using Child information on run time only we need to calculate the no of levels and we have to loop the parent transaction in the same no of times.
I am attached Script files what you need to analyse. I am waiting for your reply

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646388 is a reply to message #646387]

Tue, 29 December 2015 06:27

Michel Cadot
Messages: 68645
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

You need to pay me if you want any analysis.

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646389 is a reply to message #646387]

Tue, 29 December 2015 06:46

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

How will you & I know when correct solution has been posted here?

Report message to a moderator

Re: Can we Write PLSQL concept in WITH clause [message #646390 is a reply to message #646387]

Tue, 29 December 2015 07:43

EdStevens
Messages: 1376
Registered: September 2013

Senior Member

graphe wrote on Tue, 29 December 2015 06:24

Now we are at calculation of parent Account Information by using Child information on run time only we need to calculate the no of levels and we have to loop the parent transaction in the same no of times.
I am attached Script files what you need to analyse. I am waiting for your reply

What you don't seem to understand is that this is just a discussion forum. No one is obligated to respond to anything. If people decide, for whatever reason, to ignore a posting, they are free to do so.

Report message to a moderator

Previous Topic:	Materialized View
Next Topic:	Please help for resolving issue for UTL_FILE

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 25 14:55:28 CDT 2024