Sql – error ORA-06550 ORA-00933

Your ORA-00933 error is due to an incorrectly formatted SELECT statement:

SELECT desp_id_seq.nextval,
       dbms_random.string('U',5),
       TRUNC(dbms_random.value(0000,9999)),
       prod_id from dba_xy.product 
       prod_name from dba_xy.product; 

...when it should be:

SELECT DESP_ID_SEQ.nextval,
       DBMS_RANDOM.string('U',5),
       TRUNC(DBMS_RANDOM.value(0000,9999)),
       t.prod_id,
       t.prod_name 
  FROM dba_xy.product t; 

You were missing the comma to separate the prod_id and prod_name columns, and additionally had a redundant FROM dba_xy.product declaration in the wrong location.

That said, the dba_xy.despatch table should only contain the prod_id. If you need to provide a human readable version of the data, I recommend you construct a view. Example:

CREATE VIEW despatch_vw AS
SELECT t.prod_id,
       p.prod_name
  FROM dba_xy.despatch t
  JOIN dba_xy.product p ON p.prod_id = t.prod_id

SELECT   Orders.OrderNumber, LineItems.Quantity, LineItems.Description
FROM     Orders
JOIN     LineItems
ON       LineItems.LineItemGUID =
         (
         SELECT  TOP 1 LineItemGUID 
         FROM    LineItems
         WHERE   OrderID = Orders.OrderID
         )

In SQL Server 2005 and above, you could just replace INNER JOIN with CROSS APPLY:

SELECT  Orders.OrderNumber, LineItems2.Quantity, LineItems2.Description
FROM    Orders
CROSS APPLY
        (
        SELECT  TOP 1 LineItems.Quantity, LineItems.Description
        FROM    LineItems
        WHERE   LineItems.OrderID = Orders.OrderID
        ) LineItems2

Please note that TOP 1 without ORDER BY is not deterministic: this query you will get you one line item per order, but it is not defined which one will it be.

Multiple invocations of the query can give you different line items for the same order, even if the underlying did not change.

If you want deterministic order, you should add an ORDER BY clause to the innermost query.

Example sqlfiddle