Home » SQL & PL/SQL » SQL & PL/SQL » How to get this table from that Database (PL/SQL Developer, unknown, Win 10)
How to get this table from that Database [message #669027] Thu, 29 March 2018 04:36 Go to next message
Nanex
Messages: 1
Registered: March 2018
Junior Member
Hey guys,

I'm fairly new to PL/SQL(Oracle), but have basic knowledge in MySQL.

So, I got a SQL database with logs which looks anything like

Package ID ||  Location ID  || Action  ||  Timestamp
       1            ||       A        ||      Req   ||     yyyy:mm:dd
       2            ||      D        ||      Arrive ||    yyyy:mm:dd
..........................................................................

As you can see, there's no way to see which package got from A to B or from A to C or something like that, from the first glance.
Only way to get this is to do it over the timestamp later on in excel with vba, which is no problem.
The package ID and location ID is more complex, of course..and there's also more actions, which are not needed.
The package ID is unique.
What I want now is to see, how many movements were done from location A to C or from A to D or from C to F and so on.
At the end of the location ID you can see, if it's an input or an output, so my table should look like

Movements   || O1  ||  O2 ||   O3  ||  O4  ||  O5 ||   O6   ....
I1                   ||  x   ||   y   ||          ||    e   ||    f  ||     s  
I2                   ||  e  ||   g   ||     s   ||          ||      ||    w
I3    .  .  . . . . .. . . . . . . . . . . .. . . . . . . . . .. . . .
I4  .  .  .. .  ..  . . . . . . . .  . . . . . . . . . . . . . . ..  . .
I5 .  . . . . . . . . . . . . . . . . .  . . .. . . . . . .. . . . . .. . 
I6
...

My Idea is to count the Movements overall on the inputs and on the outputs with the package ID.
That works in a way, I get two tables in PL/SQL Developer, where you can see how much movements were done in the different locations

Location  ||    movements
I1  ||  2345
I2  ||  2345
I3  ||  13423
.......
Location  ||   movements
O1  ||  1234234
O2 ||   234523
O3 ||   234523
...

My problem now is, how to connect those both tables to see how often a package got from point A to B for example?

You have any idea?
Is this even possible?


EDIT:
I don't know how to format this right, tried to get a better overview with '||'
I hope you get the idea

[mod-edit: code tags added by BB]

[Updated on: Thu, 29 March 2018 16:07] by Moderator

Report message to a moderator

Re: How to get this table from that Database [message #669030 is a reply to message #669027] Thu, 29 March 2018 06:57 Go to previous messageGo to next message
BlackSwan
Messages: 26649
Registered: January 2009
Location: SoCal
Senior Member
Welcome to this forum

Please read and follow the forum guidelines, to enable us to help you:
OraFAQ Forum Guide
How to use {code} tags and make your code easier to read
Re: How to get this table from that Database [message #669033 is a reply to message #669027] Thu, 29 March 2018 08:30 Go to previous message
joy_division
Messages: 4949
Registered: February 2005
Location: East Coast USA
Senior Member
I have NO IDEA what you are asking or saying. It's a complete mess. I see no rows for B or C or F, so why you even mention them is beyond me.

Why don't you start all over with create table and insert statements along with a coherent description.
Previous Topic: How to insert table alias in query
Next Topic: identifier 'PACK_GEO.TRAN001' must be declared
Goto Forum:
  


Current Time: Wed Jan 29 01:41:17 CST 2020