4 table query...maybe 5?? 1

[tridith]

I don't know if I will be able to describe this very well, I hardly understand it...who am I kidding, I don't understand it.

On the page that I have to make selections from that are used to build the query are. StartDate, EndDate, List of trucks, drop down with sensor names populated from cust_truck_sensors (select distinct sensor_name from cust_truck_sensors)

So the user can search between some date on any number of trucks on only one sensor.


4 Tables
cust_trucks ( oid = matches with cust_positions asset_id, truck_name )
(ex: 2, Truck1)
(ex: 4, Truck2)

cust_positions ( (OID = PK), (date_time = time of the record), (asset_id = truck we are searching on), (asset_type_id = 2), Latitude, Longitude, Location )

cust_truck_sensors ((OID = PK), (cust_truck_id = FK from cust_positions asset_id), sensor_num(a truck can have multiple sensors in it, this field is a number that relates to what sensor it is in the truck), sensor_name (Text name of the sensor, there can be multiple OID's with the same sensor_name(ex:Sensor1, Sensor2,SensorA) )
(ex: 2, 2, 1, Door Open) Truck number 2's first sensor tracks when the door opens
(ex: 5, 4, 2, Ignition) Truck number 4's second sensor tracks when the ignition is triggered.

cust_position_detail( (OID = PK), (position_id = FK from OID in cust_positions), (position_detail_type_id = 1, because 1 is always a sensoc), (detail_value_1 = what sensor from the truck went off)
Every time a sensor is set off, there will be an entry in this table.
(ex: 2, 140, 1, 1) At position_id from cust_positions oid, the sensor number 1 went off
(ex: 3, 143, 1, 2) At position_id from cust_positions oid, the sensor number 2 went off


In the end I want to display
- Truck Name
- Latitude
- Longitude
- Date
- Location


I know that this is all part of my where

FROM
  QATech_DataStore."cust_positions" "p",
  QATech_DataStore."cust_trucks" 
WHERE
  QATech_DataStore."cust_trucks"."OID" = "p"."asset_id"
AND
    ("p"."date_time" between to_date('07/18/2006 12:00AM', 'MM/DD/YYYY HH:MIAM')
  and
    to_date('07/18/2006 4:30PM', 'MM/DD/YYYY HH:MIAM'))
AND
  "p"."asset_type_id" = 2

WOW...lol

Any Questions??

Thanks

 

[tridith]

Oh ya,

My Manager tried to explain this a little to me, here is what she wrote down.

select <all needed fields>
from cust_positions CP
where asset_type_id = 2 and Asset_id in ( truck id from user list)
inner join on Cust_position_detail CPD
on cp.oid = cpd.position_id
where detail_type_id = 1
join on cust_trucks_sensors
cpd.value_1 = select sensor_num 
where sensor_name = <name user selected>

got it figured out...hehe

 

[SantaMufasa]

...got it figured out...hehe

So, tridith, I can't tell if you are serious or facetious. Do you still need help? If so, please be very specific in restating what you need.


If you do not need additional help, and since (at this point) no one down the road could benefit from this thread without any additional value added, I propose that you hit the "...Red flag..." link at the bottom of your original post.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[tridith]

oops, I forgot the question mark after I wrote that, what I meant to say was

Got it figured out??

 

[SantaMufasa]

Tridith,

I have a few questions:

1) In your manager's query version, above, she did not mention the cust_trucks (which provides the TRUCK_NAME. Does she not want the TRUCK_NAME (as you specified) or was she being careless?

2) How does LOCATION differ from LONGITUDE/LATITUDE combination?

3) Can you please confirm the table names from which each of your SELECT <expression-list> items originate?

4) For each relationship that exists amongst your four source tables, can you please confirm which columns (fully qualified by their owning table names) match which columns (again, fully qualified with owning table name) in the related table?

5) For each additional filtering characteristic (e.g. <some_column_name> = 1), could you please confirm the table name from whence <some_column_name> comes?

6) Why are you enclosing table names, column names, and even aliases in double quotes? Unless you actually created the tables and columns with double quotes, you do not want double quotes (since you will then always and forever need double quotes to successfully reference those items).

7) Unless the table descriptions are uncomfortably long, could you please post (from SQL*Plus) a "DESCRIBE <table_name>" of each of your tables?

I'm certain we can offer you a completed query that will do what you want.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[tridith]

1.) careless

2.) Loaction is a number, (ex: 43). There is actually location3distance and location3, location3distance is the number and locaion is a word that represents the name of a city. With both together it would read, "43 km From Toronto"

3.)
In the end I want to display
- Truck Name - comes from QATech_DataStore."cust_trucks" where the oid of the truck equals the asset_id from cust_positions
- Latitude - comes from QATech_DataStore."cust_positions"
- Longitude - comes from QATech_DataStore."cust_positions"
- Date - comes from QATech_DataStore."cust_positions"
- Location - comes from QATech_DataStore."cust_positions"

4.)
I havn't really done this before, and I am not really sure what you mean, I know it would be more work on your behalf, but I should be able to understand you by example. Sorry.

- Cust_trucks has a list of trucks in it.

- cust_positions has a list of positions that each truck has made, the asset_id is the truck number from cust_trucks.oid

- cust_position_detail has a list of instances when a sensor went off, everytime a sensot goes off it will be stored in this table, cust_position_detail.position_id comes from cust_positions.OID

- cust_position_detail.position_detail_type_id can equal multiple numbers, but we only want it when it is equal to 1, 1 means that it was a sensor.

- cust_position_detail.detail_value_1, can equal a number (say 1 - 5) ( a truck can have multiple different sensors on it, door open, ignition, etc.) which will relate back to cust_truck_sensors.sensor_num

- cust_truck_sensors has a list of all possible sensors and the truck they are located on, sensor_name is the actual name of the sensor, more then one truck can have a sensor with the same name.


5.)
- detail_type_id = 1 this is from QATech_DataStore."cust_position_detail" position_detail_type_id
- asset_type_id = 2 this is from QATech_DataStore."cust_positions" asset_type_id can equal other numbers, but I only want the ones that are equal to 2, because 2 represents a truck.

6.) I would say, dont worry about the quotes, I seem to always need everything quoted to get it to run.

7.)Well some of them are long and you wont need a lot of the columns but.....

"cust_trucks"

 Name                    Null?    Type
 ----------------------- -------- ----------------
 GCRecord                         NUMBER(38)
 OID                     NOT NULL NUMBER
 system_Oid                       NUMBER(38)
 truck_name                       NVARCHAR2(100)
 enabled                          NUMBER(1)
 maintenance_group_id             NUMBER(38)
 division_id                      NUMBER(38)
 truck_owner_type_id              NUMBER(38)
 terminal_id                      NUMBER(38)
 GVW                              NUMBER(38)
 cab_style_id                     NUMBER(38)
 truck_make_id                    NUMBER(38)
 truck_model_id                   NUMBER(38)
 serial_number                    NVARCHAR2(100)
 year                             NUMBER(38)
 axles                            NUMBER(38)
 rear_tire_make_id                NUMBER(38)
 rear_tire_size_id                NUMBER(38)
 front_tire_make_id               NUMBER(38)
 front_tire_size_id               NUMBER(38)
 max_torque                       NUMBER(38)
 max_horsepower                   NUMBER(38)
 transmission                     NVARCHAR2(100)
 num_gears                        NUMBER(38)
 axle_ratio                       NUMBER(38)
 rear_suspension_type_id          NUMBER(38)
 region_id                        NUMBER(38)
 OptimisticLockField              NUMBER(38)

"cust_positions"

Name                    Null?    Type
 ----------------------- -------- ----------------
 GCRecord                         NUMBER(38)
 OID                     NOT NULL NUMBER
 lat                              FLOAT(126)
 lon                              FLOAT(126)
 date_time                        DATE
 asset_id                         NUMBER(38)
 asset_type_id                    NUMBER(38)
 operator_id                      NUMBER(38)
 trek_id                          NUMBER(38)
 odometer                         NUMBER(38)
 speed                            NUMBER(38)
 heading                          NUMBER(38)
 fuel                             NUMBER(38)
 idle_fuel                        NUMBER(38)
 idle_time                        NUMBER(38)
 prov_state_id                    NUMBER(38)
 rpm                              NUMBER(38)
 brake                            NUMBER(1)
 brake_pressure                   NUMBER(38)
 accel_pressure                   NUMBER(38)
 location1                        NVARCHAR2(100)
 location1distance                NUMBER(38)
 location2                        NVARCHAR2(100)
 location2distance                NUMBER(38)
 location3                        NVARCHAR2(100)
 location3distance                NUMBER(38)
 is_valid                         NUMBER(1)
 OptimisticLockField              NUMBER(38)
 edited                           NUMBER(1)

"cust_trucks_sensors"

 Name                    Null?    Type
 ----------------------- -------- ----------------
 GCRecord                         NUMBER(38)
 OID                     NOT NULL NUMBER
 cust_truck_id                    NUMBER(38)
 sensor_num                       NUMBER(38)
 sensor_name                      NVARCHAR2(100)
 OptimisticLockField              NUMBER(38)

"cust_position_detail"

 Name                    Null?    Type
 ----------------------- -------- ----------------
 GCRecord                         NUMBER(38)
 OID                     NOT NULL NUMBER
 position_id                      NUMBER(38)
 position_detail_type_id          NUMBER(38)
 detail_value_1                   NVARCHAR2(100)
 detail_value_2                   NVARCHAR2(100)
 detail_value_3                   NVARCHAR2(100)
 OptimisticLockField              NUMBER(38)
 edited                           NUMBER(1)

I hope I have somewhat answered you questions.
Also note that the manager that went trough that little snipit of code, doesnt really know sql that well.

Thanks.

Any more questions??

 

[tridith]

Was there any other questions that you had SantaMufasa???

 

[SantaMufasa]

I'm sorry for the delay in responding, tridith. I'm certain we can produce a solution for you. My delay results from my being on vacation with my family in San Francisco. For some reason, the family thinks that going to Chinatown, Fisherman's Wharf, the GG Bridge, et cetera, is more fun than designing a solution for you...Go figure.

In any case, if my fine colleagues from Tek-Tips have not contributed a solution by the time I get everyone to sleep (so I can get back on Tek-Tips without interruption), I'll work on your need.

Thanks for your patience,

Mufasa
(aka Dave of Sandy, Utah, USA)
[I can provide you with low-cost, remote Database Administration services: see our website and contact me via www.dasages.com]

 

[tridith]

ahhh, vacation would be nice, hope you are having a good time.

talk to you soon.

 

[carp]

Based on your original post, I believe this would give you trucks' routes; providing a means for resorting would allow your users to slice/dice this a little differently:

SELECT t.truck_name,
       p.latitude,
       p.longitude,
       p.date_time,
       p.location
FROM cust_trucks t,
     cust_positions p,
     cust_truck_sensors s,
     cust_position_detail d
WHERE t.asset_id = p.asset_id
  AND p.asset_id = s.cust_truck_id
  AND p.oid = d.position_id
  AND d.detail_value_1 = s.oid
  AND p.asset_type_id = 2
  AND d.position_detail_type_id = 1
  AND t.truck_name IN (list_of_selected_trucks)
  AND p.date_time BETWEEN <selected_start_time> AND <selected_end_time>
  AND s.sensor_name = <selected_sensor_name>
ORDER BY t.truck_name, p.date_time;

This might not be exactly what you need, but gives us a starting point, and might get you close enough that you can take it from here by yourself.
Please let us know.

Santa - enjoy San Francisco; wish I was there!

 

[tridith]

Hey Carp, seems to be working, I wish this damn database had some real data in it so I could test my queries.

Thanks for the query, I will be back soon with another one for ya. I think it will be a 4 table query too.

 

[tridith]

Oh, here is the query that I ended up with.

SELECT 
	@Session.cust_database~."cust_trucks"."truck_name",
	"p"."lat" as "Latitude",
	"p"."lon" as "Longitude",
	"p"."date_time",
	trunc("p"."date_time") as THEDAY,
	"p"."location3distance" / 1000 as "location3distance", 
	"p"."location3"
FROM 
	@Session.cust_database~."cust_trucks",
	@Session.cust_database~."cust_positions" "p",
	@Session.cust_database~."cust_trucks_sensors" "s",
	@Session.cust_database~."cust_position_detail" "d"
WHERE 
	@Session.cust_database~."cust_trucks"."OID" = "p"."asset_id"
AND 
	"p"."asset_id" = "s"."cust_truck_id"
AND 
	"p"."OID" = "d"."position_id"
AND 
	"d"."detail_value_1" = "s"."OID"
AND 
	"p"."asset_type_id" = 2
AND 
	"d"."position_detail_type_id" = 1
AND 
	("p"."date_time" between 
to_date('@Request.Mobile.StartDateTime~ @Request.Mobile.StartHour~:@Request.Mobile.StartMinute~@Request.Mobile.StartAMPM~', 'MM/DD/YYYY HH:MIAM') and  
to_date('@Request.Mobile.EndDateTime~ @Request.Mobile.EndHour~:@Request.Mobile.EndMinute~@Request.Mobile.EndAMPM~', 'MM/DD/YYYY HH:MIAM'))

AND 
	"s"."sensor_name" = '@Request.DDLSensors~'
	
	@Session.TruckWhereClause~
ORDER BY 
	@Session.cust_database~."cust_trucks"."truck_name", "p"."date_time"