Re: [Koha] SQL help please

[Ian Bays]

I think you will find the data required in items.itemnotes_nonpublic so 
your WHERE clause might be:


WHERE items.itemnotes_nonpublic LIKE '%Added to LA%'

Use the LIKE comparison in case there is anything else in the notes.


On 03/06/2020 05:53, Ketan Kulkarni wrote:

You will have to use the function ExtractValue() for extracting the public
note. Once you do that, just download the CSV and sort that column in a
spreadsheet application. Should do the trick.

On Wed, Jun 3, 2020 at 10:18 AM Kerrie Stevens 
wrote:


Hi All,

I want to be able to run a report that pulls out bib number, title, author
& barcode for resources that have "Added to LA" in the 'x-non-public note'
field of the item record. That last bit is the bit I'm not sure how to
do...?

Thank you for any assistance you may provide...


Kerrie Stevens
Alphacrucis College
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha


___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha




--
Ian Bays
Director, PTFS Europe Limited
Content Management and Library Solutions
+44 (0) 7774 995297 (mobile)
skype: ian.bays
email: ian.b...@ptfs-europe.com

___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Ketan Kulkarni]

You will have to use the function ExtractValue() for extracting the public
note. Once you do that, just download the CSV and sort that column in a
spreadsheet application. Should do the trick.

On Wed, Jun 3, 2020 at 10:18 AM Kerrie Stevens 
wrote:

> Hi All,
>
> I want to be able to run a report that pulls out bib number, title, author
> & barcode for resources that have "Added to LA" in the 'x-non-public note'
> field of the item record. That last bit is the bit I'm not sure how to
> do...?
>
> Thank you for any assistance you may provide...
>
>
> Kerrie Stevens
> Alphacrucis College
> ___
>
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha
>
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] SQL help please

[Kerrie Stevens]

Hi All,

I want to be able to run a report that pulls out bib number, title, author & 
barcode for resources that have "Added to LA" in the 'x-non-public note' field 
of the item record. That last bit is the bit I'm not sure how to do...?

Thank you for any assistance you may provide...


Kerrie Stevens
Alphacrucis College
___

Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
Unsubscribe: https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Bob Birchall]

Hi Kerrie,
You haven't joined the Items table. So:
JOIN items ON (biblio_metadata.biblionumber = items.biblionumber)

HTH,
Bob
Calyx

On 12/06/18 08:43, Kerrie Stevens wrote:

I have the following report which almost does what I need but I can't quite get 
it there.

I want to be able get a list of titles and the Dewey from 082$a but I also want 
to be able to select for which collection I want the results - that is where 
I'm getting stuck.
I can select the collection when I run the report, but I get the following 
error so there is something else missing...
Error: The following error was encountered: The database returned the following 
error:  Unknown column 'items.ccode' in 'where clause'

Here is my report so far:

SELECT biblio.biblionumber, biblio.title, biblio.author, concat (ExtractValue( metadata, 
'//datafield[@tag="082"]/subfield[@code="a"]' ), " ", ExtractValue( metadata, 
'//datafield[@tag="082"]/subfield[@code="b"]' )) as Dewey
FROM biblio_metadata
JOIN biblio ON ( biblio_metadata.biblionumber = biblio.biblionumber )
WHERE items.ccode=<>
ORDER BY biblio.title ASC

It works if I remove the WHERE line, but then I can't select which collection.

Thanks for any help you may provide!


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] SQL help please

[Kerrie Stevens]

I have the following report which almost does what I need but I can't quite get 
it there.

I want to be able get a list of titles and the Dewey from 082$a but I also want 
to be able to select for which collection I want the results - that is where 
I'm getting stuck.
I can select the collection when I run the report, but I get the following 
error so there is something else missing...
Error: The following error was encountered: The database returned the following 
error:  Unknown column 'items.ccode' in 'where clause'

Here is my report so far:

SELECT biblio.biblionumber, biblio.title, biblio.author, concat (ExtractValue( 
metadata, '//datafield[@tag="082"]/subfield[@code="a"]' ), " ", ExtractValue( 
metadata, '//datafield[@tag="082"]/subfield[@code="b"]' )) as Dewey
FROM biblio_metadata
JOIN biblio ON ( biblio_metadata.biblionumber = biblio.biblionumber )
WHERE items.ccode=<>
ORDER BY biblio.title ASC

It works if I remove the WHERE line, but then I can't select which collection.

Thanks for any help you may provide!


Kerrie Stevens  AALIA(CP), MAppSci(LibMgt), BBus(Info)
Melbourne Campus - Librarian

kerrie.stev...@ac.edu.au | ac.edu.au
P +61 3 8799 1155 | F +61 3 8799 1199
1 Keith Campbell Court, Scoresby VIC 3179 Australia

This email and any attachments may contain privileged and confidential 
information and are intended for the named addressee only. If you have received 
this email in error, please notify the sender and delete this email 
immediately. Any confidentiality, privilege or copyright is not waived or lost 
because this email has been sent to you in error. It is your responsibility to 
check this email and any attachments for viruses.



Scanned by the Alphacrucis Netbox from 
http://cyberhound.com/;>CyberHound

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Pedro Amorim]

This is awesome, Nick.
I'm bookmarking this for future reference, thanks a lot!

On 13 December 2017 at 15:27, Nick Clemens 
wrote:

> This is a quite unfinished stub, but it aims to allow for letting you input
> a list into a report:
> https://github.com/bywatersolutions/koha-reports-plus
>
> On Mon, Dec 11, 2017 at 8:20 PM DHD.KOHA  wrote:
>
> > what version of KOHA ?
> >
> >
> >
> > On 11/12/2017 02:18 πμ, Kerrie Stevens wrote:
> > > Is there a way to write an SQL report where you can input a number of
> > item barcodes (as you do when using batch modify or delete) that results
> in
> > a table showing title, author, call number etc...?
> > >
> > > Thanks for any help you may provide!
> > >
> > >
> > > Kerrie Stevens
> > > AALIA(CP), MAppSc(Lib)(CSU), BBus(InfoMgt)(RMIT)
> > >
> > > College Librarian
> > > First Aid Officer
> > > College Archivist
> > > Copyright Compliance Officer
> > > Journal Manager – Journal of Contemporary Ministry
> > >
> > > Harvest Bible College
> > >
> > > OFFICE: +61 3 8799  <+61%203%208799%20>
> > > DIRECT LINE: +61 3 8799 1155 <+61%203%208799%201155>
> > > EMAIL: kstev...@harvest.edu.au
> > > STREET ADDRESS: 1 Keith Campbell Court, Scoresby VIC 3179 AUSTRALIA
> > > POSTAL ADDRESS: PO BOX 9183, Scoresby VIC 3179 AUSTRALIA
> > > WORKING HOURS: Mon - Thurs 8.30am – 4.30pm, Fri 8.30am – 3pm
> > >
> > > harvest.edu.au
> > > CRICOS CODE: 01035C (VIC) Harvest Bible College Ltd.
> > > [RTO: 0067]
> > >
> > > DISCLAIMER: The information contained in this e-mail is confidential
> and
> > may also be privileged. Opinions expressed are those of the sender and
> not
> > necessarily the opinions of Harvest Bible College. If you are not the
> > addressee, any use of this communication is strictly prohibited. If you
> > have received this message in error, please contact i...@harvest.edu.au
> > While Harvest Bible College is using the latest antiviral tools, we do
> not
> > except responsibility for problems caused by virus or other destructive
> > mechanisms which may be attached to electronic communication. Please scan
> > this email and other attachment for viruses.
> > >
> > > ___
> > > Koha mailing list  http://koha-community.org
> > > Koha@lists.katipo.co.nz
> > > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Nick Clemens]

This is a quite unfinished stub, but it aims to allow for letting you input
a list into a report:
https://github.com/bywatersolutions/koha-reports-plus

On Mon, Dec 11, 2017 at 8:20 PM DHD.KOHA  wrote:

> what version of KOHA ?
>
>
>
> On 11/12/2017 02:18 πμ, Kerrie Stevens wrote:
> > Is there a way to write an SQL report where you can input a number of
> item barcodes (as you do when using batch modify or delete) that results in
> a table showing title, author, call number etc...?
> >
> > Thanks for any help you may provide!
> >
> >
> > Kerrie Stevens
> > AALIA(CP), MAppSc(Lib)(CSU), BBus(InfoMgt)(RMIT)
> >
> > College Librarian
> > First Aid Officer
> > College Archivist
> > Copyright Compliance Officer
> > Journal Manager – Journal of Contemporary Ministry
> >
> > Harvest Bible College
> >
> > OFFICE: +61 3 8799  <+61%203%208799%20>
> > DIRECT LINE: +61 3 8799 1155 <+61%203%208799%201155>
> > EMAIL: kstev...@harvest.edu.au
> > STREET ADDRESS: 1 Keith Campbell Court, Scoresby VIC 3179 AUSTRALIA
> > POSTAL ADDRESS: PO BOX 9183, Scoresby VIC 3179 AUSTRALIA
> > WORKING HOURS: Mon - Thurs 8.30am – 4.30pm, Fri 8.30am – 3pm
> >
> > harvest.edu.au
> > CRICOS CODE: 01035C (VIC) Harvest Bible College Ltd.
> > [RTO: 0067]
> >
> > DISCLAIMER: The information contained in this e-mail is confidential and
> may also be privileged. Opinions expressed are those of the sender and not
> necessarily the opinions of Harvest Bible College. If you are not the
> addressee, any use of this communication is strictly prohibited. If you
> have received this message in error, please contact i...@harvest.edu.au
> While Harvest Bible College is using the latest antiviral tools, we do not
> except responsibility for problems caused by virus or other destructive
> mechanisms which may be attached to electronic communication. Please scan
> this email and other attachment for viruses.
> >
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[DHD.KOHA]

what version of KOHA ?



On 11/12/2017 02:18 πμ, Kerrie Stevens wrote:

Is there a way to write an SQL report where you can input a number of item 
barcodes (as you do when using batch modify or delete) that results in a table 
showing title, author, call number etc...?

Thanks for any help you may provide!


Kerrie Stevens
AALIA(CP), MAppSc(Lib)(CSU), BBus(InfoMgt)(RMIT)

College Librarian
First Aid Officer
College Archivist
Copyright Compliance Officer
Journal Manager – Journal of Contemporary Ministry

Harvest Bible College

OFFICE: +61 3 8799 
DIRECT LINE: +61 3 8799 1155
EMAIL: kstev...@harvest.edu.au
STREET ADDRESS: 1 Keith Campbell Court, Scoresby VIC 3179 AUSTRALIA
POSTAL ADDRESS: PO BOX 9183, Scoresby VIC 3179 AUSTRALIA
WORKING HOURS: Mon - Thurs 8.30am – 4.30pm, Fri 8.30am – 3pm

harvest.edu.au
CRICOS CODE: 01035C (VIC) Harvest Bible College Ltd.
[RTO: 0067]

DISCLAIMER: The information contained in this e-mail is confidential and may 
also be privileged. Opinions expressed are those of the sender and not 
necessarily the opinions of Harvest Bible College. If you are not the 
addressee, any use of this communication is strictly prohibited. If you have 
received this message in error, please contact i...@harvest.edu.au While 
Harvest Bible College is using the latest antiviral tools, we do not except 
responsibility for problems caused by virus or other destructive mechanisms 
which may be attached to electronic communication. Please scan this email and 
other attachment for viruses.

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha


___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Mark Alexander]

Excerpts from Pedro Amorim's message of 2017-12-11 14:18:08 -0100:
> This should work:
> 
> Select title, author, i.itemcallnumber from biblio b
> Left join biblioitems bi on b.biblionumber=bi.biblionumber
> Left join items i on bi.biblionumber=i.biblionumber
> where i.barcode IN ("BARCODE1", "BARCODE2", "BARCODE3");

This can also be made into an SQL report using multiple entry fields
for the barcodes.  It's a bit of a kludge but seems to work because
the blank fields are treated either as '' or NULL in the generated SQL.

Select title, author, i.itemcallnumber from biblio b
Left join biblioitems bi on b.biblionumber=bi.biblionumber
Left join items i on bi.biblionumber=i.biblionumber
where i.barcode = <>
or i.barcode = <>
or i.barcode = <>
or i.barcode = <>
or i.barcode = <>
or i.barcode = <>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Pedro Amorim]

Hello Kerrie,

This should work:

Select title, author, i.itemcallnumber from biblio b
Left join biblioitems bi on b.biblionumber=bi.biblionumber
Left join items i on bi.biblionumber=i.biblionumber
where i.barcode IN ("BARCODE1", "BARCODE2", "BARCODE3");

GL,

Pedro Amorim

On 11 December 2017 at 04:39, SATISH  wrote:

> And Barcodes can be searched with wild cards '%' or '_'
>
> eg: 45% results in
> 45, 451,452,453..459, 4500
> this would help you to some extent.
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[SATISH]

And Barcodes can be searched with wild cards '%' or '_'

eg: 45% results in
45, 451,452,453..459, 4500
this would help you to some extent.
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[SATISH]

Hi Kerrie,

I can only find single barcode search that you could find it in SQL
Library..here..

https://wiki.koha-community.org/wiki/SQL_Reports_Library#Barcode_Search_Report

And somebody could help you in enhancing this sql for multiple barcode
search.


Regards
Satish
Librarian
Govt. Engineering College, Hassan
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] SQL help please

[Kerrie Stevens]

Is there a way to write an SQL report where you can input a number of item 
barcodes (as you do when using batch modify or delete) that results in a table 
showing title, author, call number etc...?

Thanks for any help you may provide!


Kerrie Stevens
AALIA(CP), MAppSc(Lib)(CSU), BBus(InfoMgt)(RMIT)

College Librarian
First Aid Officer
College Archivist
Copyright Compliance Officer
Journal Manager – Journal of Contemporary Ministry

Harvest Bible College 

OFFICE: +61 3 8799 
DIRECT LINE: +61 3 8799 1155
EMAIL: kstev...@harvest.edu.au 
STREET ADDRESS: 1 Keith Campbell Court, Scoresby VIC 3179 AUSTRALIA
POSTAL ADDRESS: PO BOX 9183, Scoresby VIC 3179 AUSTRALIA
WORKING HOURS: Mon - Thurs 8.30am – 4.30pm, Fri 8.30am – 3pm

harvest.edu.au
CRICOS CODE: 01035C (VIC) Harvest Bible College Ltd.
[RTO: 0067]

DISCLAIMER: The information contained in this e-mail is confidential and may 
also be privileged. Opinions expressed are those of the sender and not 
necessarily the opinions of Harvest Bible College. If you are not the 
addressee, any use of this communication is strictly prohibited. If you have 
received this message in error, please contact i...@harvest.edu.au While 
Harvest Bible College is using the latest antiviral tools, we do not except 
responsibility for problems caused by virus or other destructive mechanisms 
which may be attached to electronic communication. Please scan this email and 
other attachment for viruses. 

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Katrin]

Hi Kerry,

you are right, I think this one should be better:

SELECT
i.itemnumber,
i.biblionumber,
b.title,
b.author,
i.itemcallnumber,
i.barcode,
i.homebranch
FROM
items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE homebranch = "QLD"
AND i.biblionumber not in (select distinct i2.biblionumber from items i2 
where homebranch != "QLD")

ORDER BY i.itemcallnumber

If you want holdingbranch instead of holdingbranch you can just replace it.

Hope this works,

Katrin


On 12.04.2017 23:52, Kerrie Stevens wrote:


SELECT
i.itemnumber,
b.title,
b.author,
i.itemcallnumber,
i.barcode,
i.homebranch
FROM
items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE homebranch = "QLD"
AND i.biblionumber not in (SELECT biblionumber FROM items WHERE 
i.homebranch != "QLD")

ORDER BY i.itemcallnumber



___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Kerrie Stevens]

Hi Katrin,

Again, this is giving me everything at QLD, not items ONLY held at QLD…

I’m sure there must be a way to do it…

Thanks,

Kerrie Stevens

From: Katrin Fischer [mailto:katrin.fischer...@web.de]
Sent: Wednesday, April 12, 2017 5:27 PM
To: Kerrie Stevens <kstev...@harvest.edu.au>
Cc: koha@lists.katipo.co.nz
Subject: Aw: [Koha] SQL help please

Hi Kerrie,

I think DISTINCT is not quite what you want here, but this seems to work for 
one of our libraries with multiple branches:

SELECT
i.itemnumber,
b.title,
b.author,
i.itemcallnumber,
i.barcode,
i.homebranch
FROM
items i
LEFT JOIN biblio b ON (i.biblionumber=b.biblionumber)
WHERE homebranch = "QLD"
AND i.biblionumber not in (SELECT biblionumber FROM items WHERE i.homebranch != 
"QLD")
ORDER BY i.itemcallnumber

Hope this helps,

Katrin


Gesendet: Mittwoch, 12. April 2017 um 07:20 Uhr
Von: "Kerrie Stevens" <kstev...@harvest.edu.au<mailto:kstev...@harvest.edu.au>>
An: "koha@lists.katipo.co.nz<mailto:koha@lists.katipo.co.nz>" 
<koha@lists.katipo.co.nz<mailto:koha@lists.katipo.co.nz>>
Betreff: [Koha] SQL help please
I have this report that lists all the items in our QLD library but I want to 
restrict it to items ONLY held in our QLD library and not another branch... I 
don't know where to put the DISTINCT tag? Please advise:

SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, 
items.barcode
FROM items
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE items.homebranch='QLD'
ORDER BY items.itemcallnumber

Thanks for your help,

Kerrie Stevens
Harevst Bible College

___
Koha mailing list http://koha-community.org
Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz>
https://lists.katipo.co.nz/mailman/listinfo/koha
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Kerrie Stevens]

That’s right – I want items only held at QLD, bot Qld and another campus.

When I run the report you’ve provided, I get the following error:
You have an error in your SQL syntax; check the manual that corresponds to your 
MySQL server version for the right syntax to use near 'SELECT * FROM items 
WHERE items.homebranch != 'QLD' ) ORDER BY items.itemcallnu' at line 10


Kerrie Stevens

From: Barton Chittenden [mailto:bar...@bywatersolutions.com]
Sent: Wednesday, April 12, 2017 4:59 PM
To: Kerrie Stevens <kstev...@harvest.edu.au>
Cc: koha@lists.katipo.co.nz
Subject: Re: [Koha] SQL help please

Kerrie,

Just to be sure that I'm understanding you here ... you wan to list the item if 
it's held at 'QLD', but *not* if it's held at 'QLD' but also held at another 
branch?

I think this will work:

SELECT
items.itemnumber,
biblio.title,
biblio.author,
items.itemcallnumber,
items.barcode
FROM
items
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE NOT CONTAINS ( SELECT * FROM items WHERE items.homebranch != 'QLD' )
ORDER BY items.itemcallnumber


On Wed, Apr 12, 2017 at 1:20 AM, Kerrie Stevens 
<kstev...@harvest.edu.au<mailto:kstev...@harvest.edu.au>> wrote:
I have this report that lists all the items in our QLD library but I want to 
restrict it to items ONLY held in our QLD library and not another branch... I 
don't know where to put the DISTINCT tag? Please advise:

SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, 
items.barcode
FROM items
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE items.homebranch='QLD'
ORDER BY items.itemcallnumber

Thanks for your help,

Kerrie Stevens
Harevst Bible College

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz<mailto:Koha@lists.katipo.co.nz>
https://lists.katipo.co.nz/mailman/listinfo/koha

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Barton Chittenden]

Kerrie,

Just to be sure that I'm understanding you here ... you wan to list the
item if it's held at 'QLD', but *not* if it's held at 'QLD' but also held
at another branch?

I think this will work:

SELECT
items.itemnumber,
biblio.title,
biblio.author,
items.itemcallnumber,
items.barcode
FROM
items
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE NOT CONTAINS ( SELECT * FROM items WHERE items.homebranch != 'QLD' )
ORDER BY items.itemcallnumber


On Wed, Apr 12, 2017 at 1:20 AM, Kerrie Stevens 
wrote:

> I have this report that lists all the items in our QLD library but I want
> to restrict it to items ONLY held in our QLD library and not another
> branch... I don't know where to put the DISTINCT tag? Please advise:
>
> SELECT items.itemnumber, biblio.title, biblio.author,
> items.itemcallnumber, items.barcode
> FROM items
> LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
> WHERE items.homebranch='QLD'
> ORDER BY items.itemcallnumber
>
> Thanks for your help,
>
> Kerrie Stevens
> Harevst Bible College
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] SQL help please

[Kerrie Stevens]

I have this report that lists all the items in our QLD library but I want to 
restrict it to items ONLY held in our QLD library and not another branch... I 
don't know where to put the DISTINCT tag? Please advise:

SELECT items.itemnumber, biblio.title, biblio.author, items.itemcallnumber, 
items.barcode
FROM items
LEFT JOIN biblio ON (items.biblionumber=biblio.biblionumber)
WHERE items.homebranch='QLD'
ORDER BY items.itemcallnumber

Thanks for your help,

Kerrie Stevens
Harevst Bible College

___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Jane Cothron]

Hi--
   I believe that Nicole Engard wrote the following report for me.  It
seems to work.

SELECT biblio.biblionumber, biblio.title, biblio.author
FROM biblioitems
JOIN biblio ON ( biblioitems.biblionumber = biblio.biblionumber )
WHERE ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]'
) = ""

On Mon, Aug 8, 2016 at 11:36 PM, Kerrie Stevens 
wrote:

> Hi Everyone,
>
> I'm trying to get a report to identify which bib records in my Koha do not
> have anything in the 942 $c Koha Item Type marc field.
>
> I tried to tweak a report from the report library, but it doesn't appear
> to work correctly as some of the results do have things in the 942$c field.
> Can anyone help me work out what I'm doing wrong?
>
>
> SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml,
> LOCATE('',
>
>biblioitems.marcxml, LOCATE(' biblioitems.marcxml)+19),
>
>LOCATE('', biblioitems.marcxml, LOCATE(' code="c">',
>
>biblioitems.marcxml, LOCATE('
>biblioitems.marcxml)+19)) - LOCATE('',
> biblioitems.marcxml,
>
>LOCATE(' itemtype
>
> FROM biblioitems, biblio
>
> WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT NULL
>
> I'm specifically wanting a list of bib records that have nothing in the
> 942$c marc field.
>
> Any help very much appreciated. I have almost no SQL experience/knowledge!
>
> Thank you
>
> Kerrie Stevens
>
> Harvest Bible College
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>



-- 
Jane Cothron
Cataloger
Lincoln County Library District
P.O. Box 2027
Newport, Oregon 97365
541-265-3066 (phone & fax)
jcoth...@lincolncolibrarydist.org
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Tomas Cohen Arazi]

:-P

El mié., 10 ago. 2016 a las 10:56, Jonathan Druart (<
jonathan.dru...@bugs.koha-community.org>) escribió:

> Erk indeed! Thanks Barton.
>
> I have the habit of looking at 995 for items ;)
>
> 2016-08-10 14:32 GMT+01:00 Barton Chittenden  >:
> > Jonathan's use of ExtractValue is correct, but item data isn't stored in
> > biblioitems.marcxml -- as a matter of fact, it's not stored in marc at
> > all... it's *always* generated on the fly.
> >
> > You can query against items.itype instead.
> >
> > --Barton
> >
> > On Tue, Aug 9, 2016 at 4:18 AM, Jonathan Druart
> >  wrote:
> >>
> >> Hi Kerrie,
> >> I'd say that the following query returns what you want:
> >>
> >> SELECT biblioitems.biblionumber, marcxml from biblioitems where
> >> ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' )
> >> = "";
> >>
> >> Regards,
> >> Jonathan
> >>
> >> 2016-08-09 7:36 GMT+01:00 Kerrie Stevens :
> >> > Hi Everyone,
> >> >
> >> > I'm trying to get a report to identify which bib records in my Koha do
> >> > not have anything in the 942 $c Koha Item Type marc field.
> >> >
> >> > I tried to tweak a report from the report library, but it doesn't
> appear
> >> > to work correctly as some of the results do have things in the 942$c
> field.
> >> > Can anyone help me work out what I'm doing wrong?
> >> >
> >> >
> >> > SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml,
> >> > LOCATE('',
> >> >
> >> >biblioitems.marcxml, LOCATE(' >> > biblioitems.marcxml)+19),
> >> >
> >> >LOCATE('', biblioitems.marcxml, LOCATE(' >> > code="c">',
> >> >
> >> >biblioitems.marcxml, LOCATE(' >> >
> >> >biblioitems.marcxml)+19)) - LOCATE('',
> >> > biblioitems.marcxml,
> >> >
> >> >LOCATE(' >> > itemtype
> >> >
> >> > FROM biblioitems, biblio
> >> >
> >> > WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT
> NULL
> >> >
> >> > I'm specifically wanting a list of bib records that have nothing in
> the
> >> > 942$c marc field.
> >> >
> >> > Any help very much appreciated. I have almost no SQL
> >> > experience/knowledge!
> >> >
> >> > Thank you
> >> >
> >> > Kerrie Stevens
> >> >
> >> > Harvest Bible College
> >> >
> >> > ___
> >> > Koha mailing list  http://koha-community.org
> >> > Koha@lists.katipo.co.nz
> >> > https://lists.katipo.co.nz/mailman/listinfo/koha
> >> ___
> >> Koha mailing list  http://koha-community.org
> >> Koha@lists.katipo.co.nz
> >> https://lists.katipo.co.nz/mailman/listinfo/koha
> >
> >
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
-- 
Tomás Cohen Arazi
Theke Solutions (https://theke.io )
✆ +54 9351 3513384
GPG: B2F3C15F
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Jonathan Druart]

Erk indeed! Thanks Barton.

I have the habit of looking at 995 for items ;)

2016-08-10 14:32 GMT+01:00 Barton Chittenden :
> Jonathan's use of ExtractValue is correct, but item data isn't stored in
> biblioitems.marcxml -- as a matter of fact, it's not stored in marc at
> all... it's *always* generated on the fly.
>
> You can query against items.itype instead.
>
> --Barton
>
> On Tue, Aug 9, 2016 at 4:18 AM, Jonathan Druart
>  wrote:
>>
>> Hi Kerrie,
>> I'd say that the following query returns what you want:
>>
>> SELECT biblioitems.biblionumber, marcxml from biblioitems where
>> ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' )
>> = "";
>>
>> Regards,
>> Jonathan
>>
>> 2016-08-09 7:36 GMT+01:00 Kerrie Stevens :
>> > Hi Everyone,
>> >
>> > I'm trying to get a report to identify which bib records in my Koha do
>> > not have anything in the 942 $c Koha Item Type marc field.
>> >
>> > I tried to tweak a report from the report library, but it doesn't appear
>> > to work correctly as some of the results do have things in the 942$c field.
>> > Can anyone help me work out what I'm doing wrong?
>> >
>> >
>> > SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml,
>> > LOCATE('',
>> >
>> >biblioitems.marcxml, LOCATE('> > biblioitems.marcxml)+19),
>> >
>> >LOCATE('', biblioitems.marcxml, LOCATE('> > code="c">',
>> >
>> >biblioitems.marcxml, LOCATE('> >
>> >biblioitems.marcxml)+19)) - LOCATE('',
>> > biblioitems.marcxml,
>> >
>> >LOCATE('> > itemtype
>> >
>> > FROM biblioitems, biblio
>> >
>> > WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT NULL
>> >
>> > I'm specifically wanting a list of bib records that have nothing in the
>> > 942$c marc field.
>> >
>> > Any help very much appreciated. I have almost no SQL
>> > experience/knowledge!
>> >
>> > Thank you
>> >
>> > Kerrie Stevens
>> >
>> > Harvest Bible College
>> >
>> > ___
>> > Koha mailing list  http://koha-community.org
>> > Koha@lists.katipo.co.nz
>> > https://lists.katipo.co.nz/mailman/listinfo/koha
>> ___
>> Koha mailing list  http://koha-community.org
>> Koha@lists.katipo.co.nz
>> https://lists.katipo.co.nz/mailman/listinfo/koha
>
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Barton Chittenden]

Jonathan's use of ExtractValue is correct, but item data isn't stored in
biblioitems.marcxml -- as a matter of fact, it's not stored in marc at
all... it's *always* generated on the fly.

You can query against items.itype instead.

--Barton

On Tue, Aug 9, 2016 at 4:18 AM, Jonathan Druart <
jonathan.dru...@bugs.koha-community.org> wrote:

> Hi Kerrie,
> I'd say that the following query returns what you want:
>
> SELECT biblioitems.biblionumber, marcxml from biblioitems where
> ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' )
> = "";
>
> Regards,
> Jonathan
>
> 2016-08-09 7:36 GMT+01:00 Kerrie Stevens :
> > Hi Everyone,
> >
> > I'm trying to get a report to identify which bib records in my Koha do
> not have anything in the 942 $c Koha Item Type marc field.
> >
> > I tried to tweak a report from the report library, but it doesn't appear
> to work correctly as some of the results do have things in the 942$c field.
> > Can anyone help me work out what I'm doing wrong?
> >
> >
> > SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml,
> LOCATE('',
> >
> >biblioitems.marcxml, LOCATE(' biblioitems.marcxml)+19),
> >
> >LOCATE('', biblioitems.marcxml, LOCATE(' code="c">',
> >
> >biblioitems.marcxml, LOCATE(' >
> >biblioitems.marcxml)+19)) - LOCATE('',
> biblioitems.marcxml,
> >
> >LOCATE(' itemtype
> >
> > FROM biblioitems, biblio
> >
> > WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT NULL
> >
> > I'm specifically wanting a list of bib records that have nothing in the
> 942$c marc field.
> >
> > Any help very much appreciated. I have almost no SQL
> experience/knowledge!
> >
> > Thank you
> >
> > Kerrie Stevens
> >
> > Harvest Bible College
> >
> > ___
> > Koha mailing list  http://koha-community.org
> > Koha@lists.katipo.co.nz
> > https://lists.katipo.co.nz/mailman/listinfo/koha
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
>
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

Re: [Koha] SQL help please

[Jonathan Druart]

Hi Kerrie,
I'd say that the following query returns what you want:

SELECT biblioitems.biblionumber, marcxml from biblioitems where
ExtractValue( marcxml, '//datafield[@tag="942"]/subfield[@code="c"]' )
= "";

Regards,
Jonathan

2016-08-09 7:36 GMT+01:00 Kerrie Stevens :
> Hi Everyone,
>
> I'm trying to get a report to identify which bib records in my Koha do not 
> have anything in the 942 $c Koha Item Type marc field.
>
> I tried to tweak a report from the report library, but it doesn't appear to 
> work correctly as some of the results do have things in the 942$c field.
> Can anyone help me work out what I'm doing wrong?
>
>
> SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml, LOCATE(' code="c">',
>
>biblioitems.marcxml, LOCATE(' biblioitems.marcxml)+19),
>
>LOCATE('', biblioitems.marcxml, LOCATE(' code="c">',
>
>biblioitems.marcxml, LOCATE('
>biblioitems.marcxml)+19)) - LOCATE('', 
> biblioitems.marcxml,
>
>LOCATE('
> FROM biblioitems, biblio
>
> WHERE biblioitems.biblionumber = biblio.biblionumber AND url IS NOT NULL
>
> I'm specifically wanting a list of bib records that have nothing in the 942$c 
> marc field.
>
> Any help very much appreciated. I have almost no SQL experience/knowledge!
>
> Thank you
>
> Kerrie Stevens
>
> Harvest Bible College
>
> ___
> Koha mailing list  http://koha-community.org
> Koha@lists.katipo.co.nz
> https://lists.katipo.co.nz/mailman/listinfo/koha
___
Koha mailing list  http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha

[Koha] SQL help please

[Kerrie Stevens]

Hi Everyone,

I'm trying to get a report to identify which bib records in my Koha do not have 
anything in the 942 $c Koha Item Type marc field.

I tried to tweak a report from the report library, but it doesn't appear to 
work correctly as some of the results do have things in the 942$c field.
Can anyone help me work out what I'm doing wrong?


SELECT biblio.biblionumber, SUBSTRING(biblioitems.marcxml, LOCATE('',

   biblioitems.marcxml, LOCATE('', biblioitems.marcxml, LOCATE('',

   biblioitems.marcxml, LOCATE('', 
biblioitems.marcxml,

   LOCATE('http://koha-community.org
Koha@lists.katipo.co.nz
https://lists.katipo.co.nz/mailman/listinfo/koha