Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » DBISAM Technical Support » Support Forums » DBISAM SQL » View Thread |
Messages 1 to 4 of 4 total |
General SQL question |
Sat, Aug 26 2006 2:31 AM | Permanent Link |
"Ian Branch" | Hello SQL Gurus,
I have a string field which is an account # called acctnumb. I have a string reference field which contains several account #s separated by a ';'. I have the Account #s in a string variable, cAcctNumbs, so I can assign it to a Query parameter. I need the SQL for a Where statement that will test for the presence of acctnumb in cAcctNumbs. I thought I could do it with an 'IN' operator but can't seem to make it work. Any assistance appreciated. Regards & TIA, Ian -- |
Sat, Aug 26 2006 2:54 AM | Permanent Link |
"Ian Branch" | Sorry, should have indicated. The data from the Reference account #s field
looks like the following.. 123456;234567;345678 Let's say I have a number of records with 123456 & 234567 as well as many other account numbers the acctnumb field. I need to get just those that are in the reference accounts. Yes I could hard code them but the reference account #s change from customer to customer. Regards, Ian -- |
Sat, Aug 26 2006 3:05 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ian
IIRC you need POS or POSITION. If your account numbers are fixed length then you'll be ok otherwise you'll end up with false positives The easy case (fixed length) would be something along the lines of WHERE POS(acctnumb IN cAcctNumbs) > 0 The more difficult case WHERE POS(acctnumb IN cAcctNumbs) > 0 AND ( acctnumb =cAcctNumbs OR SUBSTRING(cAcctNumbs FROM LENGTH(acctnumb)+1 FOR 1) = ';' ) I think Roy Lambert |
Mon, Aug 28 2006 6:08 AM | Permanent Link |
Chris Erdal | "Ian Branch" <branch@sitathome.net> wrote in
news:7FDD50A4-6CB1-42F2-B3F7-02D8B497121F@news.elevatesoft.com: > Sorry, should have indicated. The data from the Reference account #s > field looks like the following.. 123456;234567;345678 > > Let's say I have a number of records with 123456 & 234567 as well > as many > other account numbers the acctnumb field. > > I need to get just those that are in the reference accounts. Yes > I could hard > code them but the reference account #s change from customer to > customer. > > Regards, > > Ian > what about using: WHERE ';'+cAcctNumbs+';' LIKE '%;'+acctnumb+';%' -- Chris |
This web page was last updated on Saturday, May 4, 2024 at 09:18 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |