[Sql-ig] LEFT OUTER JOIN problem

Nellhaus, Tobin tobin.nellhaus at yale.edu
Wed Aug 17 10:15:07 EDT 2011

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


    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"
  , 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
    WHEN instr(DVD_List.title, '/') > '0' THEN substr(DVD_List.title,1, (instr( DVD_List.title, '/')-1))
    ELSE DVD_List.title

More information about the Sql-ig mailing list