Roger
Maybe if you use LIKE, you can avoid the whole problem.
Eg.
select u.property_no, u.road_name, u.postal_code
from usr_prop_info u, road_info r
where u.postal_code like r.postal_code + '%'
andr.district_no = '#form.hdb_estate#'
You might need to change the + and/or the % to something else
depending on what DBMS you're using.
Nick
-Original Message-
From: Roger Lim [mailto:[EMAIL PROTECTED]]
Sent: Monday, October 30, 2000 4:03 AM
To: CF-Talk
Subject: SUBSTR : SQL queries
Hi,
I encountered problem in executing the below SQL statement :
select property_no, road_name, postal_code
from usr_prop_info
where substr(postal_code,1,length(a.postal_code)) in (select a.postal_code
from road_info a where district_no = '#form.hdb_estate#')
Apparently, my ROAD_INFO.POSTAL_CODE is either in 2-digit or 3-digit number
but my USR_PROP_INFO.POSTAL_CODE is in 6-digit number.
I'm trying to do a substr() but its length is determined by the length of
the postal_code in ROAD_INFO.
How do I able to achieve that ?
Thanks.
Roger
**
Information in this email is confidential and may be privileged.
It is intended for the addressee only. If you have received it in error,
please notify the sender immediately and delete it from your system.
You should not otherwise copy it, retransmit it or use or disclose its
contents to anyone.
Thank you for your co-operation.
**
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
