Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2010
    Location
    Montreal, QC
    Posts
    4

    Unanswered: Embedded query name/reference

    The Vehicle table has unchangeable features, including the weight bearing capability, and the ParcelDelivery include all the delivery data so if a vehicle hasn't delivered yet at a date, it won't have an entry in the later ; drivers can't drive for more than 8 hours a day and vehicles can't drive themselves. I translated the table/column names to English.

    I'm using Oracle Database 10g Express Edition Release 10.2.0.1.0

    I can list the sum of the elapsed delivery times of the vehicles with the minimal weigth bearing capabilities at a specific time:
    Code:
    SELECT Vehicle.RegistrationNumber, sum(ParcelDelivery.DeliveryElapsed)
    FROM Vehicle NATURAL JOIN ParcelDelivery
    WHERE Vehicle.Weight >= 2000 AND ParcelDelivery.DeliveryDate='11-FEB-2010'
    GROUP BY Vehicle.RegistrationNumber;

    Although there's also vehicles that meet those requirements which hasn't made a delivery that day so we need to make an external join to also fetch those. I first tried with a natural join:
    Code:
    SELECT *
    FROM 
    	(SELECT * FROM Vehicle WHERE Vehicle.Weight >= 2000)
     NATURAL JOIN 
    	(SELECT * FROM ParcelDelivery WHERE ParcelDelivery.DeliveryDate='11-FEB-2010');
    But I can't do an external left join as my console log show:
    Code:
    SQL> SELECT *
      2  FROM
      3     (SELECT * FROM Vehicle WHERE Vehicle.Weight >= 2000)
      4   LEFT JOIN
      5     (SELECT * FROM ParcelDelivery WHERE ParcelDelivery.DeliveryDate='11-FEB-2010')
      6  ON Vehicle.RegistrationNumber=ParcelDelivery.RegistrationNumber;
    ON Vehicle.RegistrationNumber=ParcelDelivery.Registra tionNumber
    *
    ERROR at line 6:
    ORA-00904: "ParcelDelivery"."RegistrationNumber": invalid identifier

    Please don't hesitate to ask any question and thank you kindly for your help.
    Last edited by DynV; 04-12-10 at 03:20. Reason: 1.forgot a couple colum translation 2.source tables

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 2009-12-31, not a date'
    When a DATE datatype is desired, then use TO_DATE() function.

    "ParcelDelivery"."RegistrationNumber" is out of scope
    Last edited by anacedent; 04-12-10 at 00:36.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2007
    Posts
    623
    Just curious why you did not change the join type in the original query:
    Code:
    SELECT Vehicule.RegistrationNumber, sum(<source_table>.DeliveryElapsed)
    FROM Vehicule LEFT JOIN ParcelDelivery
      ON Vehicule.RegistrationNumber=ParcelDelivery.RegistrationNumber
    WHERE <source_table>.poids >= 2000
      AND <source_table>.DeliveryDate=TO_DATE( '11-FEB-2010', 'DD-MON-YYYY' )
    GROUP BY Vehicule.RegistrationNumber;
    Also it would be nice to use aliases for all columns in the query (I added them as <source_table> where I was not sure from which table they come). If table names are too long, use table name aliases (which is the reason of the new query failure - the subqueries can be accessed only with them and they did not have any).

    Finally, you might find this article on AskTom about reasons why (not) using natural join interesting: http://asktom.oracle.com/pls/apex/f?...:8764523461767

  4. #4
    Join Date
    Apr 2010
    Location
    Montreal, QC
    Posts
    4
    Quote Originally Posted by anacedent View Post
    With Oracle characters between single quote marks are STRINGS!
    'This is a string, 2009-12-31, not a date'
    When a DATE datatype is desired, then use TO_DATE() function.e
    Thank you! I was in a hurry when did those queries. But I tested the query at that time and it did filter out other dates so I guess it function in a non recommended way.

    Quote Originally Posted by anacedent View Post
    "ParcelDelivery"."RegistrationNumber" is out of scope
    Could you explain further? Thanks

    Quote Originally Posted by flyboy View Post
    Just curious why you did not change the join type in the original query:
    Code:
    SELECT Vehicule.RegistrationNumber, sum(<source_table>.DeliveryElapsed)
    FROM Vehicule LEFT JOIN ParcelDelivery
      ON Vehicule.RegistrationNumber=ParcelDelivery.RegistrationNumber
    WHERE <source_table>.poids >= 2000
      AND <source_table>.DeliveryDate=TO_DATE( '11-FEB-2010', 'DD-MON-YYYY' )
    GROUP BY Vehicule.RegistrationNumber;
    Also it would be nice to use aliases for all columns in the query (I added them as <source_table> where I was not sure from which table they come). If table names are too long, use table name aliases (which is the reason of the new query failure - the subqueries can be accessed only with them and they did not have any).
    I'm sorry I edited my first post so my queries are clearer (translations & source tables). So let me just try your table alias theory for the subqueries...
    Code:
    SQL> SELECT *
      2  FROM
      3     (SELECT * FROM Vehicle v WHERE v.Weight >= 2000)
      4   LEFT JOIN
      5     (SELECT * FROM ParcelDelivery p WHERE p.DeliveryDate=select *
    from 
    	(select * from vehicule v where v.poids >= 2000)
     left join 
    	(select * from livraisoncolis l where l.DateLivraison=TO_DATE( '11-FEB-2010', 'DD-MON-YYYY' ))
    on v.Immatriculation=l.Immatriculation;)
      6  ON v.RegistrationNumber=p.RegistrationNumber;
    ON p.RegistrationNumber=p.RegistrationNumber
    *
    ERROR at line 6:
    ORA-00904: "p"."RegistrationNumber": invalid identifier

    The schema of ParcelDelivery.RegistrationNumber finishes with "REFERENCES Vehicle" so if I understood correctly this guarantee me it's the same name or it would've been "REFERENCES Vehicle.OtherColumnName".

    About your suggestion, I really don't remember what went through my head and your solution look like it could very likely work but this problem now has my curiosity and it would be pretty nice to be able to have subqueries join if you can help me.

    Quote Originally Posted by flyboy View Post
    Finally, you might find this article on AskTom about reasons why (not) using natural join interesting: http://asktom.oracle.com/pls/apex/f?...:8764523461767
    Awesome thanks! I never used it IRL, just for this paper, but it's an appropriate deterrent in case I would've picked up on it.
    Last edited by DynV; 04-12-10 at 03:54. Reason: comment split

  5. #5
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by DynV View Post
    I'm sorry I edited my first post so my queries are clearer (translations & source tables). So let me just try your table alias theory for the subqueries...
    Code:
    SQL> SELECT *
      2  FROM
      3     (SELECT * FROM Vehicle v WHERE v.Weight >= 2000) <subquery_alias>
      4   LEFT JOIN
      5     (SELECT * FROM ParcelDelivery p WHERE p.DeliveryDate=select *
    from 
    	(select * from vehicule v where v.poids >= 2000)
     left join 
    	(select * from livraisoncolis l where l.DateLivraison=TO_DATE( '11-FEB-2010', 'DD-MON-YYYY' ))
    on v.Immatriculation=l.Immatriculation;)
      6  ON v.RegistrationNumber=p.RegistrationNumber;
    The same way you aliased the table VEHICLE (alias V), you might alias the whole subquery in the place of <subquery_alias> (line 3). As the query syntax from line 5 does not seem correct, you should consult SQL Reference book for the correct one. It is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

  6. #6
    Join Date
    Apr 2010
    Location
    Montreal, QC
    Posts
    4
    Quote Originally Posted by flyboy View Post
    The same way you aliased the table VEHICLE (alias V), you might alias the whole subquery in the place of <subquery_alias> (line 3). As the query syntax from line 5 does not seem correct, you should consult SQL Reference book for the correct one. It is available with other Oracle documentation e.g. online on Search and Download Oracle Database, Application Server, and Collaboration Suite Documentation
    First I apologize for the wrongly formulated query which had 2 from and 2 join, it was most likely due to copy-pasting a translated term over the original language. About the translation, I was tired of it so I made a PHP script that you may find on my homepage on the page Term translator which I used for the following queries.

    I finally achieved something really near what I'm looking for. The only problem left is the nulls resulting from the left join. I'd need those entries to be sorted the last by group of weight ; maybe if there would be a way to replace nulls by 0s that it would be sorted the last (by group of weight).

    Code:
    SQL> SELECT SV.Registratiation, SV.Weight, SL.DeliveryElapsedSum
      2  FROM
      3     (SELECT V.Registratiation, V.Weight
      4             FROM Vehicle V
      5             WHERE V.Weight >= 2000
      6     ) SV
      7   LEFT JOIN
      8     (SELECT L.Registratiation, SUM(DeliveryElapsed) AS DeliveryElapsedSum
      9             FROM ParcelDelivery L
     10             WHERE L.DeliveryDate='15-FEB-2010'
     11             GROUP BY L.Registratiation
     12     ) SL
     13  ON SV.Registratiation=SL.Registratiation
     14  ORDER BY SV.Weight, SL.DeliveryElapsedSum DESC;
    
    Registrati      Weight DeliveryElapsedSum
    ---------- ---------- -------------------
    VRH229           2000
    VRH227           2000                   3
    VRH224           2000                   1
    VRH225           3000                   6
    Although you may the column is a date from it's creation snippet:
    Code:
    CREATE TABLE ParcelDelivery (
    	...
    	DeliveryDate			DATE NOT NULL,
    	...
    );
    I don't know what's the issue but TO_DATE() doesn't seem to function on my client.

    Code:
    SQL> SELECT L.Registratiation
      2             FROM ParcelDelivery L
      3             WHERE L.DeliveryDate=TO_DATE('15-FEB-2010','DD-MON-YYYY');
    
    no rows selected
    
    SQL> SELECT L.Registratiation
      2             FROM ParcelDelivery L
      3             WHERE L.DeliveryDate='15-FEB-2010';
    
    Registrati
    ----------
    VRH225
    VRH226
    VRH224
    VRH227
    VRH222

  7. #7
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by DynV View Post
    The only problem left is the nulls resulting from the left join. I'd need those entries to be sorted the last by group of weight ; maybe if there would be a way to replace nulls by 0s that it would be sorted the last (by group of weight). :confused:
    Have a look at NULLS FIRST/NULLS LAST option of ORDER BY clause. The second one is probably what you need. It is further described in SQL Reference book, available e.g.online on http://tahiti.oracle.com/.
    Quote Originally Posted by DynV View Post
    Although you may the column is a date from it's creation snippet:
    Code:
    CREATE TABLE ParcelDelivery (
    	...
    	DeliveryDate			DATE NOT NULL,
    	...
    );
    I don't know what's the issue but TO_DATE() doesn't seem to function on my client.
    Normally, DATE data type contains time part. So, the most obvious reason is none of DELIVERYDATE values are from midnight. If using string literal returned something, then the DELIVERYDATE column was probably converted to VARCHAR2 using current NLS_DATE_FORMAT parameter value. Which may (after its change) fail. Also it cannot use index on DELIVERYDATE column (if exists). Commonly, it is not good to depend on any kind of implicit data type conversion - it may lead to unpredictable results in future.

    The correct condition on DELIVERYDATE could be for example (note the strictness/non-strictness of inequalities):
    Code:
    WHERE L.DeliveryDate>=TO_DATE('15-FEB-2010','DD-MON-YYYY')
      AND L.DeliveryDate<TO_DATE('16-FEB-2010','DD-MON-YYYY')

  8. #8
    Join Date
    Apr 2010
    Location
    Montreal, QC
    Posts
    4
    The date problem was that my assignment documents didn't consider the dates format so I had this:

    Code:
    SQL> SELECT L.DeliveryDate
      2             FROM ParcelDelivery L ;
    
    DeliveryDate
    ----------------
    20/02/0015 10:00
    20/02/0015 10:00
    20/02/0015 10:00
    20/03/0015 10:00
    20/03/0015 10:00
    20/01/0029 10:00
    20/02/0016 10:00
    20/02/0012 10:00
    20/02/0015 10:00
    20/02/0011 10:00
    20/02/0015 10:00
    
    11 rows selected.
    I set the NLS_DATE_FORMAT properly then reran the query files and it functioned as below. The insertions should be modify to use TO_DATE but I need to run the files that I was given exactly as they are...

    Code:
    SQL> SELECT SV.Registratiation, SV.Weight, SL.DeliveryElapsedSum
      2  FROM
      3     (SELECT V.Registratiation, V.Weight
      4             FROM Vehicle V
      5             WHERE V.Weight >= 2000
      6     ) SV
      7   LEFT JOIN
      8     (SELECT L.Registratiation, SUM(DeliveryElapsed) AS DeliveryElapsedSum
      9             FROM ParcelDelivery L
     10             WHERE L.DeliveryDate>=TO_DATE('15-FEB-2010','DD-MON-YYYY')
     11                     AND L.DeliveryDate<TO_DATE('16-FEB-2010','DD-MON-YYYY')
     12             GROUP BY L.Registratiation
     13     ) SL
     14  ON SV.Registratiation=SL.Registratiation
     15  ORDER BY SV.Weight, SL.DeliveryElapsedSum DESC NULLS LAST;
    
    Registrati      Weight DeliveryElapsedSum
    ---------- ---------- -------------------
    VRH227           2000                   3
    VRH224           2000                   1
    VRH229           2000
    VRH225           3000                   6
    All your help is appreciated flyboy
    Last edited by DynV; 04-14-10 at 07:14. Reason: forgot quote translation

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •