Board index » delphi » using "like" in a SQL statement

using "like" in a SQL statement

Hello,

The following code worked in InterBase 4.1 and doesn't work in InterBase
6.1.
**********
select
a.cert,a.dealer_id,a.plan_type,a.month_sold,a.trans_date,a.reserve,a.adminis
trator,
a.agent1_id, p.plan_months from allsales a, plans_limited p
where
a.plan_type ='AA06A'
and
a.administrator in('GAD','GAO','GDO','GIP','GNA','GND','GNO','GNP')
 and a.administrator = p.program_code and a.plan_type like p.plan_code||'%'
and (trans_date >= ' 7/01/96 00:00:00'
and trans_date <= ' 2/28/01 23:59:59')

***************
the plans_limited table is a view and has a value of  'AA06' in the
p.plan_code field.
What happens now is only 5 rows are returned when there is actually 250 rows

Thank you,
Randy

 

Re:using "like" in a SQL statement


Quote
Rand Monroe wrote:

> The following code worked in InterBase 4.1 and doesn't work in InterBase
> 6.1.

        The syntax

LIKE FIELD_NAME || '%'

        ...works for me on a test table so I don't think your use of this
syntax is the problem.  Without knowing more about what you're doing I
can't say what is the problem, however.  Can you try deleting clauses
from your WHERE to narrow it down?

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Vertex Systems Corporation
Senior Developer           http://www.vertexsoftware.com

Delphi/InterBase weblog:   http://delphi.weblogs.com

Re:using "like" in a SQL statement


Quote
Craig Stuntz (TeamB) wrote:
> Rand Monroe wrote:

>> The following code worked in InterBase 4.1 and doesn't work in InterBase
>> 6.1.

>    The syntax

> LIKE FIELD_NAME || '%'

>    ...works for me on a test table so I don't think your use of this
> syntax is the problem.  Without knowing more about what you're doing I
> can't say what is the problem, however.  Can you try deleting clauses
> from your WHERE to narrow it down?

Another way to verify this: drop the wildcard character and change LIKE
to STARTING WITH.

Michael

Other Threads