[Sql-ig] LEFT OUTER JOIN problem

Ji, Yue yue.ji at yale.edu
Wed Aug 17 12:49:00 EDT 2011


Hi Tobin,

Lauren is correct.
Besides this error, you need to select the field names inside each sub queries, instead just select *.

Yue

-----Original Message-----
From: sql-ig-bounces at mailman.yale.edu [mailto:sql-ig-bounces at mailman.yale.edu] On Behalf Of Brown, Lauren
Sent: Wednesday, August 17, 2011 10:25 AM
To: Nellhaus, Tobin; SQL Interest Group (sql-ig at mailman.yale.edu)
Subject: Re: [Sql-ig] LEFT OUTER JOIN problem

my eye is drawn to the FROM statements:  ", line_item.bib_id", which is not a table

is this the problem?

Lauren

-----Original Message-----
From: sql-ig-bounces at mailman.yale.edu [mailto:sql-ig-bounces at mailman.yale.edu] On Behalf Of Nellhaus, Tobin
Sent: Wednesday, August 17, 2011 10:15 AM
To: SQL Interest Group (sql-ig at mailman.yale.edu)
Subject: [Sql-ig] LEFT OUTER JOIN problem

Hi all,

SQL Developer says that the syntax for the query below is OK, but when I run it, I get the error that the table or view doesn't exist.  I've played with a bunch of variants but I get the same error every time.  The goal is to get a list of DVDs in the collection at Bass, and some PO information where available (sometimes it isn't, hence the use of an outer join).  Ideas?

Tobin Nellhaus
Librarian for Performing Arts, Media and Philosophy
Coordinator for Humanities Collection Development
tobin.nellhaus at yale.edu
203/432-8212

=======

  SELECT DISTINCT 
  (CASE 
    WHEN instr(DVD_List.title, '/') > '0' THEN substr(DVD_List.title,1, (instr(DVD_List.title, '/')-1))
    ELSE DVD_List.title
  END) AS "Title"
  , DVD_List.imprint AS "Publisher"
  , Purchase_orders.po_status_date AS "PO Update"
  , Purchase_orders.po_status_desc AS "PO Status"
  , TO_CHAR(Purchase_orders.item_status_date,'dd-Mon-yy') AS "Status Date"
  , (CASE
    WHEN Purchase_orders.item_status BETWEEN '12' AND '17' THEN 'Lost/Missing'
    ELSE 'Here'
  END) AS "Status"
  , Purchase_orders.chron AS "Chron"
  , Purchase_orders.copy_number AS "Copy"
  , Purchase_orders.historical_charges AS "Charges"
  , Purchase_orders.historical_browses AS "Browses"
  
  FROM 
  
 ( SELECT * FROM
  bib_master
  , bib_text
  , bib_item
  , item
  , item_status
  , item_status_type
  , mfhd_item
  , line_item.bib_id
  WHERE bib_master.bib_id = bib_text.bib_id
  AND bib_text.bib_id = bib_item.bib_id
  AND bib_item.item_id = item.item_id
  AND item.item_id = item_status.item_id
  AND item.item_id  = mfhd_item.item_id
  and item_status.item_status = item_status_type.item_status_type
  and item.perm_location = '608'
  AND bib_master.suppress_in_opac = 'N'
) DVD_List
  
  left outer join (SELECT *
  FROM bib_item
  , line_item
  , purchase_order
  , po_status
  where bib_item.bib_id = line_item.bib_id
  AND line_item.po_id = purchase_order.po_id
  AND purchase_order.po_status = po_status.po_status
  ) Purchase_orders
  
  on DVD_List.bib_id = Purchase_orders.bib_id
    
  ORDER BY (CASE 
    WHEN instr(DVD_List.title, '/') > '0' THEN substr(DVD_List.title,1, (instr( DVD_List.title, '/')-1))
    ELSE DVD_List.title
  END)
_______________________________________________
Sql-ig mailing list
Sql-ig at mailman.yale.edu
http://mailman.yale.edu/mailman/listinfo/sql-ig
_______________________________________________
Sql-ig mailing list
Sql-ig at mailman.yale.edu
http://mailman.yale.edu/mailman/listinfo/sql-ig


More information about the Sql-ig mailing list