Subject problems with the 'like' predicate
Author randmonroe
Hello,

We have converted our database from InterBase 4.1 to Firebird and
things are working fine. A few problems in the conversion but we are
working through them. We have the following problem. This code works
in the InterBase 4.1 code but works differently in Firebird. The
Firebird database returns one record for each plan_type (like it was
a distinct statement) while 4.1 returns the totals for each. It seems
to be the like statement. Plans_limited is a view with the first 4
characters of plan_code being the same as the first 4 characters of
the allsales table.

Select
A.Plan_Type PlanType,
P.Plan_Months PlanGroup,
Sum(A.Reserve) Reserve,
Sum(A.Insurance) Insurance,
Sum(A.Admfee) Admfee,
Sum(A.Agent1_Commission) Agent1,
Sum(A.Agent2_Commission) Agent2,
Sum(A.Agent3_Commission) Agent3,
Sum(A.AUL) AUL,
Sum(A.Total) Total,
Sum(A.suspense) Suspense,
Sum(A.dealer_paid) Dealerpaid,
Sum(A.voucher_amt) dbcr,
Count(A.Cert) CertCount
from Plans_Limited P, allsales A
where A.Plan_Type like P.Plan_Code ||'%'
and P.Program_Code = A.Administrator
and a.batch_no = 62002008
and a.administrator = "DAA"
group by A.Plan_Type, P.Plan_Months
order by A.Plan_Type, P.Plan_Months

Thank you,
Randy