using the add_message script
Help please
I have been asked to look at adding a message to borrowers who have an email address but no analysis code equal to ELEC.
I am trying to write some sql to select these borrowers to insert in the add_messages script
see my attempt below
there are 2 problems reallly that I hope someone can help with.
1.
I think that this sql will only select borrowers who have an analysis code i.e. a line in the BORROWER_GROUPING_LINK table and I really need to include borrowers who have no analysis code at all. But I was thinking that at least this is a start and would try to work that bit out later later.
2.
The real problem at the moment is that the sql works fine when I run it on it's own from an isql prompt but once it is embedded in the add_message.pl script it produces an error message
EXTRACT FROM ADD_MESSAGE.PL with my edited sql to select borrowers
# Retrieve the borrower/items required
(@ids) = &sql($d,"
select distinct B.BORROWER_ID
from BORROWER B,CONTACT C, BORROWER_GROUPING_LINK G
where B.BORROWER_ID between 190000 and 190500
and B.BORROWER_ID = C.TARGET_ID
and B.BORROWER_ID in
(select C.TARGET_ID from CONTACT C)
and B.BORROWER_ID = G.BORROWER_ID
and G.GROUPING_ID <> "ELEC"
and B.BORROWER_ID in
(select G.BORROWER_ID from BORROWER_GROUPING_LINK G)
");
# Process each borrower retrieved
ERROR MESSAGE WHEN RUNNING ADD_MESSAGE.pl
Bareword found where operator expected at ./add_message.pl line 98, near "and G.GROUPING_ID <> "ELEC"
(Might be a runaway multi-line "" string starting on line 90)
(Do you need to predeclare and?)
String found where operator expected at ./add_message.pl line 101, near """
(Might be a runaway multi-line "" string starting on line 98)
(Missing semicolon on previous line?)
syntax error at ./add_message.pl line 98, near "and G.GROUPING_ID <> "ELEC"
Execution of ./add_message.pl aborted due to compilation errors.
OUTPUT WHEN RUN FROM ISQL (just to prove it works!)
1> select distinct B.BORROWER_ID from BORROWER B, CONTACT C, BORROWER_GROUPING_LINK G
2> where B.BORROWER_ID between 190000 and 190500
3> and B.BORROWER_ID = C.TARGET_ID
4> and B.BORROWER_ID in
5> (select C.TARGET_ID from CONTACT C)
6> and B.BORROWER_ID = G.BORROWER_ID
7> and G.GROUPING_ID <> "ELEC"
8> and B.BORROWER_ID in
9> (select G.BORROWER_ID from BORROWER_GROUPING_LINK G)
10> go
BORROWER_ID
-----------
190466
190467
190217
190475
190477
adding messages
In terms of the error in the perl script the issue is with the double quotes around the GROUPING_ID i.e. "ELEC".
In the Perl script the who SQL statement is bounded by double quotes in order to indicate the start and the end of the statement. With double quotes surround text within the SQL the Perl script ends the SQL statement earlier than it should be and you get the errors.
While double quotes are permissible in SQL within a isql session in a Perl script use single quotes i.e. and G.GROUPING_ID <> 'ELEC'.
Alternatively you can use \" to escape each double quote in the QL query so the perl script ignores it but to me that makes reading the code harder.
In terms of the SQL I think it might be more complicated. You might have to look at breaking down the approach and checking borrowers one at a time in a loop.
Brian Crampton
Developer, Talis
adding messages
Hi Brian
thanks for your reply, the script runs ok now but the sql is rubbish I'm afraid.
I can't write a loop myself but also I think there is a problme with selecting borrower who don't have the ELEC code anyway. Because Bradford use the analysis code for gender, ethnic origin and other reasons just using analysis code <> ELEC is not enough to exclude borrowers who have ELEC plus other analysis codes.
If this is getting beyond the remit of TDN support please say so and put me out of my misery
thanks
Lin
adding messages
The following code calls a sub-routine, CheckBorrowerCode, by passing in a borrower id and a analysis code. If that code exists for the borrower then the value 1 is returned else the value returned is 0.
The result is held in a variable $test_result and then this can be used to include or exclude a borrower from processing. There still needs to be code around this to process the borrowers and add the message.
$test_result = &CheckBorrowerCode($id,$code);
print "TEST:$id $code : $test_result\n";
sub CheckBorrowerCode
{
my($bor_id,$code) = @_;
my(@temp1,$row,$result,$row_value);
$result = 0;
(@temp1) =&sql($d,"select GROUPING_ID
from BORROWER_GROUPING_LINK
where BORROWER_ID = $bor_id
and SUB_TYPE = 1
");
foreach $row(@temp1)
{
($row_value) = &despace($row);
if ($row_value eq $code) { $result = 1; last; }
}
return($result);
}
Thanks
Brian Crampton
Developer, Talis