[Sql-ig] LEFT OUTER JOIN problem

Wed Aug 17 15:03:08 EDT 2011

Whoops, that was a slip!

I haven't tried a JOIN in the real world before; I'm getting adventurous.  I have a new, working version of the query (below).

However, I encountered something interesting (to me, anyway, as an adventurous newbie).  After correcting line_item.bib_id, the query still gave me trouble.  I finally found the reason but it surprises me.  In the two subqueries, the SELECT statements included things like 'bib_text.bib_id AS "bib_id"' and 'bib_item.bib_id AS "bib_id".'  I did this thinking I'd avoid getting field names like "DVD_Titles.bib_text.bib_id." It turns out that even though I renamed the fields in exactly the same way, Oracle didn't like it.  Apparently it behaves this way on any field that needs to be acted on (JOIN, ORDER BY, etc.).  When I either removed renaming or just removed the quotation marks, the script zipped through, and included the expected nulls.  On the other hand, if you don't need to sort or do anything else, Oracle doesn't care about the quotation marks.  You can still rename with quotation marks in the outermost SELECT.

Now I have to figure out if I got correct results.  But at least the query works.

Thanks,

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

======

SELECT DISTINCT DVD_Title as "Title", Publisher, Charges, Browses, Last_Update as "Last Update", Chron, Copy, PO_Status as "PO Status", PO_Date AS "PO Date"
FROM

(SELECT DISTINCT  
 bib_text.bib_id
, (CASE  
	WHEN instr(title, '/') > '0' THEN substr(title,1, (instr( title, '/')-1)) 
	ELSE title 
END) AS DVD_Title
, imprint AS Publisher
, item.historical_charges AS Charges 
, item.historical_browses AS Browses
, (CASE 
	WHEN item_status.item_status BETWEEN '12' AND '17' THEN 'Lost/Missing' 
	ELSE 'Here' 
END) AS Status 
, to_char(item_status.item_status_date,'dd-Mon-yy') AS Last_Update
, mfhd_item.chron AS Chron 
, item.copy_number AS Copy

FROM bib_master 
, bib_text 
, bib_item 
, item 
, item_status 
, item_status_type 
, mfhd_item 

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_Titles

LEFT OUTER JOIN

(SELECT bib_item.bib_id, po_status.po_status_desc as PO_Status, to_char(purchase_order.po_status_date,'dd-Mon-yy') as PO_Date
  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
) DVD_POs

on DVD_Titles.bib_id = DVD_POs.bib_id

order by DVD_Title

> -----Original Message-----
> From: Brown, Lauren
> Sent: Wednesday, August 17, 2011 10:25 AM
> To: Nellhaus, Tobin; SQL Interest Group (sql-ig at mailman.yale.edu)
> Subject: RE: 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