Web Development

Ask something
Discussion Locked

PL/SQL in oracle Combining like info.

0 helpful
4 replies

by stpellet

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Combining like info into one line.

Is this possible?

Combining 2 lines of text on one line.
I have order information that I am dumping to a dataloader. I would like to have the labor combined and then one of the products added to the line as well. If there are multiple lines of products I want them on seperate lines.

So it would go like this.

Labor 5
Labor 4

labor 9

then product one 5

labor 9 product one 5

then product 2 - 6

Product 2 3
Product 3 5
Product 4 15
Product 5 3
Product 6 4

output to look like this.

labor 9 product one 5
Product 2 3
Product 3 5
Product 4 15
Product 5 3
Product 6 4

I am struggling with getting this started in PL/SQL. I can not see anyway to do this in SQL.

to further clarify.

I have service contract and order information that I combine to do an upload into an other system.

The data comes out as follows

for each line on the service request I get one line from the order.

Standard.

I would like to get the service line and the first part line to combine into one line.

If possible I would also like the service lines to combine if there are more than one.

Also if there are more than one part number lines. I would like them to remain as seperate lines without service information.

When I do the upload.

I need Service and the first product on one line and all other parts on that order to go to the bottom of the report.

Thank you for your help in advance I am new to all this,

Steven Pelletier

4 replies

by j.lupo

You will need to do this in PL/SQL because you can collect the data into cursors (collections similar to arrays but not quite the same thing) that you can open and compare information. Also, you can control when information gets written out and how. Basically you read your information and format it in memory then write out your detail to a file.

Since I am not a hundred percent certain of what you are trying to do, I haven't provided any code here.

BTW: what version of Oracle or other DB are you writing the PL/SQL for. This matters in the commands and abilities available.

by stpellet

Thank you for your reply.

We are on DB version 9.2.0.3.0
I am writing the commands to aid in report writing. I am using discoverer. If I could use the PL/SQL to help populate a view that would be great.

I am looking to have intelligence that pushes data into categories on a report.

there are two one with Labor and one without.

The one with Labor can be two conditions.

one with labor and a single product combined to a single line or

labor alone (either multiple lines of labor that I would like qty to combine to a single line or labor without a part.)

Also in the sceniro where you have labor and a part if there are multiple lines of labor have them combined into one would be great.

Then if I have mutliple lines of product having the first line combine with the service line and the rest being categorized as Product only.

Also any incidents that only have product should fall into this category as well.

Would it be helpful to see the view I have created so far and some sample data?

Thank you again for the help.

Steven

by stpellet

This is the sql statement that I am running through discoverer right now. Note the sample data below.

SELECT DISTINCT XXVSG_SERVICE_ORDER_BILLING_2.INCIDENT_NUMBER, XXVSG_SERVICE_ORDER_BILLING_2.PROBLEM_DESCRIPTION, XXVSG_SERVICE_ORDER_BILLING_2.STATUS_NAME, XXVSG_SERVICE_ORDER_BILLING_2.COMPANY_NAME, XXVSG_SERVICE_ORDER_BILLING_2.CYCLE_COUNT, XXVSG_SERVICE_ORDER_BILLING_2.ORDERED_PART, XXVSG_SERVICE_ORDER_BILLING_2.INCIDENT_TYPE, XXVSG_SERVICE_ORDER_BILLING_2.SERVIVED_ITEM_DESCRIPTION, XXVSG_SERVICE_ORDER_BILLING_2.CURRENT_SERIAL_NUMBER, XXVSG_SERVICE_ORDER_BILLING_2.INCIDENT_DATE, XXVSG_SERVICE_ORDER_BILLING_2.CREATION_DATE, XXVSG_SERVICE_ORDER_BILLING_2.CLOSE_DATE, XXVSG_SERVICE_ORDER_BILLING_2.RESOLUTION, XXVSG_SERVICE_ORDER_BILLING_2.ORDERED_PART_DESCRIPTION, XXVSG_SERVICE_ORDER_BILLING_2.LOCATION, XXVSG_SERVICE_ORDER_BILLING_2.ORDER_STATUS, XXVSG_SERVICE_DATE_TIME.INCIDENT_TIME, XXVSG_SERVICE_DATE_TIME.CREATION_TIME, XXVSG_SERVICE_DATE_TIME.CLOSE_TIME, XXVSG_SERVICE_ORDER_BILLING_2.ORDER_NUMBER, XXVSG_SERVICE_ORDER_BILLING_2.QTY_ORDERED, XXVSG_SERVICE_ORDER_BILLING_2.UNIT_COST, XXVSG_SERVICE_ORDER_BILLING_2.LINE_NUMBER
FROM APPS.XXVSG_SERVICE_ORDER_BILLING_2 XXVSG_SERVICE_ORDER_BILLING_2, APPS.XXVSG_SERVICE_DATE_TIME XXVSG_SERVICE_DATE_TIME
WHERE ( ( XXVSG_SERVICE_DATE_TIME.INCIDENT_NUMBER = XXVSG_SERVICE_ORDER_BILLING_2.INCIDENT_NUMBER ) ) AND ( ( XXVSG_SERVICE_ORDER_BILLING_2.CREATION_DATE >= :"From Date" ) ) AND ( ( XXVSG_SERVICE_ORDER_BILLING_2.CREATION_DATE <= :"To date" ) )
;

1625 51013 0005 MP300US Digital Passport Printer 01H0404G PM PM Chicago PP 01-FEB-2005 10:44AM 28-APR-2005 10:44AM 01-FEB-2005 11:07AM Preventive Maintenance for MP300US Digital Passport Printer 1 311.46 104402 2
1625 51013 0009AA MP300US Digital Passport Printer 01H0404G PM PM Chicago PP 01-FEB-2005 10:44AM 28-APR-2005 10:44AM 01-FEB-2005 11:07AM Filter 1 3.63 104402 1

In this example 1625 is the incident. I would like to combine the data as follows: (labels are for claification only)

Incident # - 1625 cycle count - 51013 Labor part - 0005
QTY labor - 1 price for labor - 311.46
serviced item - MP300US Digital Passport Printer
Serial number - 01H0404G Problem - PM Resolution - PM
Location - Chicago PP closing date - 01-FEB-2005
closing time - 10:44AM creation date - 28-APR-2005
creation time - 10:44AM incident date - 01-FEB-2005
incident time - 11:07AM ordered part - 0009AA description - Filter order qty - 1 price - 3.63 order numebr - 104402 line number - 2

If there was multiple labor lines I would like the qty combined.

if there was multiple lines I would like the first combined as above. and the rest to be added to a seperate sections with null values for the labor part and qty and price.

also for incidents withour labor or parts null values for the type not on the order.

I hope this califies the issue.

Thank you for your time,

Steven Pelleiter

by j.lupo

be in TR for this, perhaps you could send me a private e-mail. I have my profile setup for that. I would be willing to discuss this off line in the evenings (I am Eastern Time zone) with you and help you out. If you are using Oracle 9.2, this will be a snap in PL/SQL especially to populate your view.

This discussion is closed to new replies.