[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