mysql - query to extract report from book register-CodePudding

I have the below query in mysql, when I run the query, it gives me the complete report and "where clause does not work"

    SELECT oo.dateaccessioned AS 'Date',
    oo.barcode AS 'Acc. No.',
    ooo.title AS 'Title',
    ooo.author AS 'Author/Editor',
    concat_ws(' , ', o.editionstatement, oo.enumchron) AS 'Ed./Vol.',
    concat_ws(' ', o.place, o.publishercode) AS 'Place & Publisher',
    ooo.copyrightdate AS 'Year', o.pages AS 'Page(s)',
    ooooooo.name AS 'Source',
    oo.itemcallnumber AS 'Class No./Book No.',
    concat_ws(', ₹', concat(' ', ooooo.symbol, oooo.listprice), oooo.rrp_tax_included) AS 'Cost',
    concat_ws(' , ', oooooo.invoicenumber, oooooo.shipmentdate) AS 'Bill No. & Date',
    '' AS 'Withdrawn Date',
    '' AS 'Remarks'
    FROM biblioitems o
    LEFT JOIN items oo ON oo.biblioitemnumber=o.biblioitemnumber
    LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber
    LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber
    LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency
    LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid
    LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid
    WHERE cast(oo.barcode AS UNSIGNED) BETWEEN <<Accession Number>> AND <<To Accession Number>>
    GROUP BY oo.barcode
    ORDER BY oo.barcode ASC

Can you please help me to generate a report based on above query - oo.barcode (it is a varchar). I am a Library team member than a database administrator. My oo.barcode begins with HYD and then numercs. I know if it(oo.barcode) is a number only field the above query works without any issue.

I search about how cast works but not able to understand as i am not into database administration.

CodePudding user response：

Try this :

...

    WHERE cast(SUBSTRING_INDEX(oo.barcode,'HYD',-1) AS UNSIGNED INTEGER) BETWEEN <<Accession Number>> AND <<To Accession Number>>
...

SUBSTRING_INDEX(oo.barcode,'HYD',-1) will transform HYD132453741 to 132453741

demo here

CodePudding user response：

If the barcode column is VARCHAR and begins with "HYD", CAST AS UNSIGNED will cause a value of HYD123 to result in 0.

The non-numeric characters of the string would need to be removed prior to casting the value as an integer.

This can be achieved by trimming the leading text "HYD" from the barcode.

CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED)

Otherwise, if the prefix is always 3 characters, the substring position of barcode can be used.

CAST(SUBSTR(barcode, 4) AS UNSIGNED)

If any other non-numeric characters are contained within the string, such as HYD-123-456-789, HYD123-456-789PT, HYD123-456.789, etc, they will also needed to be removed, as the type conversion will treat them in unexpected ways. In addition, any leading 0's of the resulting numeric string value will be truncated from the resulting integer, causing 0123 to become 123.
For more details on how CAST functions see: 12.3 Type Conversion in Expression Evaluation

Examples db<>fiddle

CREATE TABLE tester (
    barcode varchar(255)
);

INSERT INTO tester(barcode) 
VALUES ('HYD123'), ('HYD0123'), ('HYD4231');

Results

SELECT cast(barcode AS UNSIGNED)
FROM tester;

cast(barcode AS UNSIGNED)
0
0
0

SELECT CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED)
FROM tester;

CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED)
123
123
4231

SELECT barcode
FROM tester
WHERE CAST(TRIM(LEADING 'HYD' FROM barcode) AS UNSIGNED) BETWEEN 120 AND 4232;

barcode
HYD123
HYD0123
HYD4231

SELECT CAST(SUBSTR(barcode, 4) AS UNSIGNED)
FROM tester;

CAST(SUBSTR(barcode, 4) AS UNSIGNED)
123
123
4231

SELECT barcode
FROM tester
WHERE CAST(SUBSTR(barcode, 4) AS UNSIGNED) BETWEEN 120 AND 4232;

barcode
HYD123
HYD0123
HYD4231

JOIN optimization

To obtain the expected results, you most likely want an INNER JOIN of the items table with an ON criteria matching the desired barcode range condition. Since INNER JOIN is the equivalent of using WHERE oo.barcode IS NOT NULL, as is the case with your current criteria - NULL matches within the items table are already being excluded.

INNER JOIN items AS oo 
ON oo.biblioitemnumber = o.biblioitemnumber
AND CAST(SUBSTR(oo.barcode, 4) AS UNSIGNED) BETWEEN ? AND ?

Full-Table Scanning

It is important to understand that transforming the column value to suit a criteria will cause a full-table scan that does not benefit from indexing, which will run very slowly.
Instead it is best to store the integer only version of the value in the database to see the benefits of indexing.
This can be accomplished in many ways, such as generated columns.

GROUP BY without an aggregate

Lastly, you should avoid using GROUP BY without an aggregate function. You most likely are expecting DISTINCT or similar form of limiting the record set. Please see MySQL select one column DISTINCT, with corresponding other columns on ways to accomplish this.

To ensure MySQL is not selecting "any value from each group" at random (leading to differing results between query executions), limit the subset data to the distinct biblioitemnumber column values from the available barcode matches. One approach to accomplish the limited subset is as follows.

/* ... */
FROM biblioitems o
INNER JOIN (
    SELECT biblioitemnumber, barcode, booksellerid, enumchron, itemcallnumber 
    FROM items WHERE biblioitemnumber IN(
        SELECT MIN(biblioitemnumber)
        FROM items
        WHERE CAST(SUBSTR(barcode, 4) AS UNSIGNED) BETWEEN ? AND ?
        GROUP BY barcode
    )
) AS oo
ON oo.biblioitemnumber = o.biblioitemnumber
LEFT JOIN biblio ooo ON ooo.biblionumber=o.biblionumber
LEFT JOIN aqorders oooo ON oooo.biblionumber=o.biblionumber
LEFT JOIN currency ooooo ON ooooo.currency=oooo.currency
LEFT JOIN aqinvoices oooooo ON oooooo.booksellerid=oo.booksellerid
LEFT JOIN aqbooksellers ooooooo ON ooooooo.id=oo.booksellerid
ORDER BY oo.barcode ASC