Correction :- I mean the last line in the original sql should be ... and item.item_id = handle.resource_id
Allen Lam wrote: > Hi Barrett, > > I noticed a potential err in your given sql. Should the last line be > ... and item.item_id = handle.item_id ? > > OK, back to your question. The bitstream submit date is written in > metadatavalue. > > Try this one: > > SELECT bitstream.bitstream_id, bitstream.name, bitstream.source, > handle.handle, metadatavalue.text_value > FROM bitstream, handle, bundle, item2bundle, item, metadatavalue > WHERE bitstream.bitstream_id = bundle.primary_bitstream_id > and bundle.bundle_id =item2bundle.bundle_id > and item2bundle.item_id = item.item_id > and item.item_id = handle.resource_id > and item.item_id = metadatavalue.item_id > and metadatavalue.metadata_field_id=XXX; > > The XXX is an id you can find out from your metadatafieldregistry > table where dc.description.provenance belongs. > > Add more conditions to the sql to filter out unwanted rows. > > Cheers, > Allen Lam. > HKU Hub Administrator, http://hub.hku.hk > > > > Vanessa Barrett wrote: >> >> Can anyone help with a fairly basic SQL statement to report on the >> date bitstreams were added to the database? >> >> The following is the statement I am using which works fine and gives >> me all bitstreams in the database >> >> SELECT bitstream.bitstream_id, bitstream.name, bitstream.source, >> handle.handle >> >> FROM bitstream, handle, bundle, item2bundle, item >> >> WHERE bitstream.bitstream_id = bundle.primary_bitstream_id >> >> and bundle.bundle_id =item2bundle.bundle_id >> >> and item2bundle.item_id = item.item_id >> >> and item.item_id = handle.handle_id >> >> However I can’t seem to find anything in the db_schema that would >> appear to be the date a bitstream was added to the database. I did try >> >> AND itemsbydateaccessioned.date_accessioned > ? >> >> but this table seems to no longer exist and anyway it would be the >> date the item was added not the date the bitstream was added. >> >> I am using DSpace 1.5.2. >> >> Cheers, >> >> Vanessa Barrett >> Digital Services Librarian >> The University of Adelaide, AUSTRALIA 5005 >> Ph : +61 8 8303 4625 >> e-mail: [email protected] >> >> CRICOS Provider Number 00123M >> ----------------------------------------------------------- >> IMPORTANT: This message may contain confidential or legally >> privileged information. If you think it was sent to you by mistake, >> please delete all copies and advise the sender. For the purposes of >> the SPAM Act 2003, this email is authorised by The University of >> Adelaide. >> >> Think green: read on the screen. >> >> ------------------------------------------------------------------------ >> >> ------------------------------------------------------------------------------ >> >> >> Download Intel® Parallel Studio Eval >> Try the new software tools for yourself. Speed compiling, find bugs >> proactively, and fine-tune applications for parallel performance. >> See why Intel Parallel Studio got high marks during beta. >> http://p.sf.net/sfu/intel-sw-dev >> ------------------------------------------------------------------------ >> >> _______________________________________________ >> Dspace-general mailing list >> [email protected] >> https://lists.sourceforge.net/lists/listinfo/dspace-general >> > ------------------------------------------------------------------------------ Download Intel® Parallel Studio Eval Try the new software tools for yourself. Speed compiling, find bugs proactively, and fine-tune applications for parallel performance. See why Intel Parallel Studio got high marks during beta. http://p.sf.net/sfu/intel-sw-dev _______________________________________________ Dspace-general mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/dspace-general
