Greetings, Monks:

It's been awhile, and I'm not *as* much of a newbie as I was, but this has me stumped. I hope someone here can enlighten me. I have a Perl script that runs through our library database and picks out all new items we have gotten in the past three months and puts them in a table, which we use to run a new books search/rss feed. It works like a charm, but now we want to add e-books. I modified the SQL query to add e-books, ran it in Aquadata to make sure it works (it does), and plugged it in to the old script. When I run the script, I get the error: "cannot bind to non-existent field 11"

I understand that bind_col works with each column in the SQL select statement, but I don't understand why this particular field ($loca_id) is coming up as nonexistent, when others have not (such as \$requestor).

My questions are, how can I make this work? and Why aren't the other bind_cols throwing errors? Many thanks--code below.

Old SQL (the query that works, plus the bind_cols):

$sth = $dbh->prepare(qq{select  BIB_TEXT.BIB_ID, BIB_TEXT.TITLE_BRIEF,
+TO_CHAR(BIB_ITEM.ADD_DATE, 'YYYYMMDDHHMISS'),
                                MFHD_MASTER.DISPLAY_CALL_NO, ITEM.ITEM
+_ID, BIB_MFHD.MFHD_ID,TO_CHAR(ADD_MONTHS(SysDate, -3),'YYYYMMDDHHMISS
+'), 
                LINE_ITEM.BIB_ID, LINE_ITEM.REQUESTOR, LOCATION.LOCATI
+ON_DISPLAY_NAME, MFHD_MASTER.LOCATION_ID, BIB_TEXT.ISBN 
                        from    BIB_TEXT, BIB_ITEM, MFHD_MASTER, ITEM,
+ BIB_MFHD, DUAL, LINE_ITEM, LOCATION, BIB_MASTER
                        where   BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID
                        and     BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID
                        and     BIB_TEXT.BIB_ID = BIB_ITEM.BIB_ID
            and     BIB_TEXT.BIB_ID = LINE_ITEM.BIB_ID
                        and     BIB_ITEM.ITEM_ID = ITEM.ITEM_ID
            and     BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID
                        and     BIB_MASTER.CREATE_DATE > ADD_MONTHS(Sy
+sDate, -3)
            and     BIB_MASTER.SUPPRESS_IN_OPAC = 'N'
                        and     MFHD_MASTER.LOCATION_ID = LOCATION.LOC
+ATION_ID
            order by BIB_ITEM.ADD_DATE
        
});

# -- 
# --  execute the query
# --

$sth->execute();

$sth->bind_col( 1, \$bib_id );
$sth->bind_col( 2, \$_title_marc );
$sth->bind_col( 3, \$timedate );
$sth->bind_col( 4, \$callno );
$sth->bind_col( 5, \$item_id );
$sth->bind_col( 6, \$mfhd_id );
$sth->bind_col( 7, \$trash );
$sth->bind_col( 8, \$moretrash );
$sth->bind_col( 9, \$requestor );
$sth->bind_col( 10, \$location );
$sth->bind_col( 11, \$loca_id );
$sth->bind_col( 12, \$isbn );

[download]

New SQL (the query that throws the error, with bind_cols):

$sth = $dbh->prepare(qq{select  BIB_TEXT.BIB_ID, 
BIB_TEXT.TITLE_BRIEF,
BIB_MASTER.CREATE_DATE,
TO_CHAR(BIB_MASTER.CREATE_DATE, 'YYYYMMDDHHMISS'),         
MFHD_MASTER.DISPLAY_CALL_NO, 
BIB_MFHD.MFHD_ID,
TO_CHAR(ADD_MONTHS(SysDate, -3),'YYYYMMDDHHMISS'), 
LOCATION.LOCATION_DISPLAY_NAME,
MFHD_MASTER.LOCATION_ID,
BIB_TEXT.ISBN 
from    BIB_TEXT, MFHD_MASTER, BIB_MFHD, DUAL, LOCATION, BIB_MASTER
where   BIB_TEXT.BIB_ID = BIB_MFHD.BIB_ID
and     BIB_MFHD.MFHD_ID = MFHD_MASTER.MFHD_ID
and     BIB_TEXT.BIB_ID = BIB_MASTER.BIB_ID
and     BIB_MASTER.CREATE_DATE > ADD_MONTHS(SysDate, -3)
and     BIB_MASTER.SUPPRESS_IN_OPAC = 'N'
and     MFHD_MASTER.LOCATION_ID = LOCATION.LOCATION_ID
and LOCATION.LOCATION_ID in (
'9',
'22',
'25',
'70',
'95',
'105',
'110',
'112',
'114',
'119',
'120',
'125',
'127',
'130',
'135',
'139',
'151',
'158',
'159',
'160',
'161',
'162',
'163',
'164',
'165',
'170',
'173',
'174',
'176'
)
order by BIB_MASTER.CREATE_DATE desc    
});

# -- 
# --  execute the query
# --

$sth->execute();

$sth->bind_col( 1, \$bib_id );
$sth->bind_col( 2, \$_title_marc );
$sth->bind_col( 3, \$timedate );
$sth->bind_col( 4, \$callno );
$sth->bind_col( 5, \$item_id );
$sth->bind_col( 6, \$mfhd_id );
$sth->bind_col( 7, \$trash );
$sth->bind_col( 8, \$moretrash );
$sth->bind_col( 9, \$requestor );
$sth->bind_col( 10, \$location );
$sth->bind_col( 11, \$loca_id );
$sth->bind_col( 12, \$isbn );

[download]

In reply to bind_col() and the SQL query by Hans Castorp

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.