Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 6 of 6 total |
Coalesce statement |
Fri, Sep 13 2013 1:22 AM | Permanent Link |
Ben Sprei CustomEDP | Im trying to find records where the search criteria will match one of 3
fields named Group1, Group2 and Group3. Im trying to use the following statement: Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%''' This is returning an error Any suggestions |
Fri, Sep 13 2013 4:25 AM | Permanent Link |
Uli Becker | Ben,
> Im trying to find records where the search criteria will match one of 3 > fields named > Group1, Group2 and Group3. Im trying to use the following statement: > > Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%''' > > This is returning an error As it should. The correct syntax is Coalesce(Group1,'') or (Coalesce(Group2,'') or Coalesce(Group3,'') http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=coalesce Uli |
Fri, Sep 13 2013 4:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Ben
>Im trying to find records where the search criteria will match one of 3 >fields named >Group1, Group2 and Group3. Im trying to use the following statement: > >Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%''' > >This is returning an error Without seeing the error (hint hint) I can only guess. The guess is you have instances where Group1, Group2 and Group3 are all null so alter your COALESE to Coalesce(Group1, Group2, Group3,'*') like ''%' + Edit1.Text + '%''' Where '*' should be some text string that will NEVER occur in Group1, Group2 or Group3 - I generally use something like '¬!£$%^&*()¬' - I have yet to encounter that in any of mt databases Roy Lambert [Team Elevate] |
Fri, Sep 13 2013 4:36 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | Uli
Good point, I was just fixing it as was Roy Lambert |
Fri, Sep 13 2013 10:58 AM | Permanent Link |
Ben Sprei CustomEDP | The correct syntax is as posted:
where coalesce(group1, group2, group3) = 101 The problem was that these fields are integer fields and the goal result I tried was VarChar. Sorry for this mistake Thanx to All "Roy Lambert" <roy@lybster.me.uk> wrote in message news:33BCDDC8-0B47-4C28-A7C3-18D9CD00E57E@news.elevatesoft.com... > Ben > >>Im trying to find records where the search criteria will match one of 3 >>fields named >>Group1, Group2 and Group3. Im trying to use the following statement: >> >>Where Coalesce(Group1, Group2, Group3) like ''%' + Edit1.Text + '%''' >> >>This is returning an error > > Without seeing the error (hint hint) I can only guess. The guess is you > have instances where Group1, Group2 and Group3 are all null so alter your > COALESE to > > > Coalesce(Group1, Group2, Group3,'*') like ''%' + Edit1.Text + '%''' > > Where '*' should be some text string that will NEVER occur in Group1, > Group2 or Group3 - I generally use something like '¬!£$%^&*()¬' - I have > yet to encounter that in any of mt databases > > Roy Lambert [Team Elevate] |
Fri, Sep 13 2013 11:45 AM | Permanent Link |
Uli Becker | Ben,
> The problem was that these fields are integer fields and the goal result I > tried was VarChar. OK, then I completely misunderstood that. Uli |
This web page was last updated on Tuesday, May 14, 2024 at 07:14 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |