Home » SQL & PL/SQL » SQL & PL/SQL » Build logic
Build logic [message #409639] Tue, 23 June 2009 06:48 Go to next message
mukundsahare
Messages: 21
Registered: February 2007
Junior Member
I have below set of data in one the file

Inv Num Invoice AmT Type
20057636 0 2000
20057636 1787.28 2000
20057637 0 2000
20057637 2154.65 2000
20057640 0 2000
20057640 1635 2000
55042162 0 2000
55042162 1349.5 2000

Qty Price Part num PO num Type
88 2031 RE245831 5500197609 2500
65 1597 RE217574 5500176427 2500
60 1861 LVU19159 5500216675 2500
100 1635 LVU18508 5500216676 2500
50 2699 RE210038 5500177879 2500


I want to print the o/p as
20057636 88 2031 RE245831 5500197609
20057637 65 1597 RE217574 5500176427
20057637 60 1861 LVU19159 5500216675
20057640 100 1635 LVU18508 5500216676
55042162 50 2699 RE210038 5500177879

There are four invoices but the above line types '2500' are five lines. To link the details to particular invoice use the below logic.

Quantity * Price = Invoice Amount

In the above data

88 * 20.31 = 1787.28 (This invoice amount is for Invoice #20057636)

One Invoice can have multiple PO lines

65 * 15.97 = 1038.05
60 * 18.61 = 1116.60
-----------
2154.65 (This invoice amount is for Invoice #20057637)


100 * 16.35 = 1635 (This invoice amount is for Invoice #20057640)


50 * 26.99 = 1349.5(This invoice amount is for Invoice #55042162)

How to write the code for the same.Can anyone help.

Regards,
Mukund





Re: Build logic [message #409640 is a reply to message #409639] Tue, 23 June 2009 06:51 Go to previous messageGo to next message
Maaher
Messages: 7065
Registered: December 2001
Senior Member
Sorry, you really need to use some code tags. This is really not readable.

MHE
Re: Build logic [message #409642 is a reply to message #409639] Tue, 23 June 2009 06:54 Go to previous messageGo to next message
yoursrajju
Messages: 5
Registered: September 2006
Location: Bangalore
Junior Member
I think you need to revisit the linking logic as there is no common field that can combine these two details (Invoice and PO). Please post the full details.
Re: Build logic [message #409646 is a reply to message #409640] Tue, 23 June 2009 07:09 Go to previous messageGo to next message
mukundsahare
Messages: 21
Registered: February 2007
Junior Member
I tried format the data .Hope this will help

Inv Num	   Invoice AmT	Type
20057636	0	2000
20057636	1787.28	2000
20057637	0	2000
20057637	2154.65	2000
20057640	0	2000
20057640	1635	2000
55042162	0	2000
55042162	1349.5	2000


Qty	Price	Part num	PO num	Type
88	2031	RE245831	5500197609	2500
65	1597	RE217574	5500176427	2500
60	1861	LVU19159	5500216675	2500
100	1635	LVU18508	5500216676	2500
50	2699	RE210038	5500177879	2500


O/p should be like .

20057636 - 88 -2031- RE245831 - 5500197609
20057637 - 65 - 1597 - RE217574-  5500176427
20057637 - 60  - 1861- LVU19159-  5500216675
20057640 - 100 - 1635 - LVU18508 - 5500216676
55042162 - 50 -  2699 -  RE210038 - 5500177879.



There are four invoices but the above line types '2500' are five lines. To link the details to particular invoice use the below logic.

Quantity * Price = Invoice Amount

In the above data

88 * 20.31 = 1787.28 (This inv amt is for inv. #20057636)


One Invoice can have multiple PO lines

65 * 15.97 = 1038.05
60 * 18.61 = 1116.60
-----------
2154.65 (This inv. amt is for Invoice #20057637)

100 * 16.35 = 1635 (This inv. amount is for Invoice #20057640)

50 * 26.99 = 1349.5(This inv. amount is for Invoice #55042162)

Please help to write the code for same

Regards

[mod-edit]Applied code tags.

[Updated on: Tue, 23 June 2009 07:35] by Moderator

Report message to a moderator

Re: Build logic [message #409660 is a reply to message #409646] Tue, 23 June 2009 07:37 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Post a working Test case: create table and insert statements along with the result you want with these data.

Also always post your Oracle version (4 decimals).

Regards
Michel
Re: Build logic [message #409671 is a reply to message #409660] Tue, 23 June 2009 07:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
The bigf problem here isn't the test case, it's the fact that there is no way to work out which Invoice number goes with which Part Number/Po Number.

Without that information, you've got an problem.
Re: Build logic [message #409676 is a reply to message #409660] Tue, 23 June 2009 07:59 Go to previous messageGo to next message
mukundsahare
Messages: 21
Registered: February 2007
Junior Member
Hi,

I have to read the data from the file and write into the file .I am using UTL_FILE to read and write the data.

I need help to develop the code based on the logic I mentioned.

Thanks & Regards,
Mukund
Re: Build logic [message #409688 is a reply to message #409671] Tue, 23 June 2009 08:51 Go to previous messageGo to next message
mukundsahare
Messages: 21
Registered: February 2007
Junior Member
Hi,

I have pasted the o/p based on logic I have mentioned.


Please help to code the logic .

Regards,
Re: Build logic [message #409775 is a reply to message #409688] Tue, 23 June 2009 23:45 Go to previous messageGo to next message
yoursrajju
Messages: 5
Registered: September 2006
Location: Bangalore
Junior Member
You need to find any other logic to combine these two tables. Beacuase currently you cant really match which invoice number relates to which PO. IT can work for the given entries. But what if more than one invoice have same amount?
Re: Build logic [message #409940 is a reply to message #409775] Wed, 24 June 2009 09:30 Go to previous messageGo to next message
mukundsahare
Messages: 21
Registered: February 2007
Junior Member
Hi,

Thanks for the reply.

There are no tables ..I will be reading from file and writing into file.

Can you please help me build the code for the given entries.


Regards,
Re: Build logic [message #409944 is a reply to message #409639] Wed, 24 June 2009 09:33 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
>There are no tables ..I will be reading from file and writing into file.
Then why is Oracle involved at all?
Previous Topic: Using CLOB/BFILE to store payslips
Next Topic: "ALTER TABLE ... ORGANIZATION INDEX" error
Goto Forum:
  


Current Time: Thu Feb 13 12:17:29 CST 2025