'Having' question
in [Programming SQL]
|
Prev: Upsert Proc using IF logic
Next: For Plamen Ratchev
From: Mark B on 20 Jul 2008 23:37 If I use: HAVING (GroupName LIKE @EnterMasterGroupName + N'%') I get: GroupName: ========= Earth.New Zealand.Auckland Earth.New Zealand.Auckland.Henderson Earth.New Zealand.Auckland.Epsom Earth.New Zealand.Wellington Earth.New Zealand.Wellington.Upper Hutt Earth.New Zealand.Wellington.City Center When I have "Earth.New Zealand" as the @EnterMasterGroupName parameter value. That's good but I also want another stored procedure that just returns: Earth.New Zealand.Auckland Earth.New Zealand.Wellington In other words just a single level rather than all levels down. I am thinking to count the full-stops in the GroupName and filter where CountFullStops(@EnterMasterGroupName)+1=CountFullStops(GroupName) So it would only pick up the next level of GroupNames. Can someone help me with the syntax/strategy for coding this? The field is indexed. Is there a CountTextItem sort of function in SQL? TIA
From: Eric Isaacs on 20 Jul 2008 23:55 > Can someone help me with the syntax/strategy for coding this? The field is > indexed. Is there a CountTextItem sort of function in SQL? The best strategy would be to redesign the table to have each of those levels in a separate field, or possibly in a separate record with a relationship to that other record, depending on the relationships and how deep they go. If you simply split those fields out into separate tables, I imagine you can see that the logic would be pretty simple to do the select you're wanting, The database could also take advantage of indexes on those fields to give you fast results. You can then just parse them back together to display them the way you have them. Not only would the programming be easier if they were in separate fields, but the database would be able to execute it faster too. The format of the data in the database and the way it's displayed to the application users do not need to be the same.
From: Steve Kass on 21 Jul 2008 00:11 Mark, If a redesign is out of the question, and the '.' is only used as a separator, you can limit the number of '.' characters to two in one of these ways: HAVING LEN(REPLACE(GroupName,'.','') = LEN(GroupName) - 2 HAVING GroupName NOT LIKE '%.%.%.%' HAVING PARSENAME(GroupName,4) IS NULL To limit the number of '.'s to one more than there are in @EnterMasterGroupName, you have this simple choice, assuming all GroupName values begin with the master group name immediately followed by '.'. HAVING GroupName NOT LIKE @EnterMasterGroupName + N'.%.%' Steve Kass Drew University http://www.stevekass.com Mark B wrote: > If I use: > > HAVING (GroupName LIKE @EnterMasterGroupName + N'%') > > I get: > > GroupName: > ========= > > Earth.New Zealand.Auckland > Earth.New Zealand.Auckland.Henderson > Earth.New Zealand.Auckland.Epsom > Earth.New Zealand.Wellington > Earth.New Zealand.Wellington.Upper Hutt > Earth.New Zealand.Wellington.City Center > > When I have "Earth.New Zealand" as the @EnterMasterGroupName parameter > value. > > That's good but I also want another stored procedure that just returns: > > Earth.New Zealand.Auckland > Earth.New Zealand.Wellington > > In other words just a single level rather than all levels down. > > I am thinking to count the full-stops in the GroupName and filter > where CountFullStops(@EnterMasterGroupName)+1=CountFullStops(GroupName) > > So it would only pick up the next level of GroupNames. > > Can someone help me with the syntax/strategy for coding this? The > field is indexed. Is there a CountTextItem sort of function in SQL? > > TIA > > > > >
From: Mark B on 21 Jul 2008 00:16 I know but: 1) There needs to be non-pre-defined and unlimited sub-levels. For example, a person might create: Earth.New Zealand.Auckland.Henderson.Teed Street.Building A. Export Sales Department.Team A.Project X 2) The current LIKE operator does take advantage of the index 3) I have 'nearly' finished everything else relating to the existing method :) "Eric Isaacs" <eisaacs(a)gmail.com> wrote in message news:901c11ec-6f89-444d-8818-61749b404782(a)u12g2000prd.googlegroups.com... >> Can someone help me with the syntax/strategy for coding this? The field >> is >> indexed. Is there a CountTextItem sort of function in SQL? > > The best strategy would be to redesign the table to have each of those > levels in a separate field, or possibly in a separate record with a > relationship to that other record, depending on the relationships and > how deep they go. > > If you simply split those fields out into separate tables, I imagine > you can see that the logic would be pretty simple to do the select > you're wanting, The database could also take advantage of indexes on > those fields to give you fast results. You can then just parse them > back together to display them the way you have them. Not only would > the programming be easier if they were in separate fields, but the > database would be able to execute it faster too. > > The format of the data in the database and the way it's displayed to > the application users do not need to be the same.
From: Mark B on 21 Jul 2008 00:36
Thanks. I think the later method would be the one. I am assuming too that NOT LIKE with the wildcards also takes advantage of the index. "Steve Kass" <skass(a)drew.edu> wrote in message news:eleYgfu6IHA.2336(a)TK2MSFTNGP03.phx.gbl... > Mark, > > If a redesign is out of the question, and the '.' is only used > as a separator, you can limit the number of '.' characters > to two in one of these ways: > > HAVING LEN(REPLACE(GroupName,'.','') = LEN(GroupName) - 2 > HAVING GroupName NOT LIKE '%.%.%.%' > HAVING PARSENAME(GroupName,4) IS NULL > > To limit the number of '.'s to one more than there are in > @EnterMasterGroupName, you have this simple choice, > assuming all GroupName values begin with the master group > name immediately followed by '.'. > > HAVING GroupName NOT LIKE @EnterMasterGroupName + N'.%.%' > > Steve Kass > Drew University > http://www.stevekass.com > > Mark B wrote: > >> If I use: >> >> HAVING (GroupName LIKE @EnterMasterGroupName + N'%') >> >> I get: >> >> GroupName: >> ========= >> >> Earth.New Zealand.Auckland >> Earth.New Zealand.Auckland.Henderson >> Earth.New Zealand.Auckland.Epsom >> Earth.New Zealand.Wellington >> Earth.New Zealand.Wellington.Upper Hutt >> Earth.New Zealand.Wellington.City Center >> >> When I have "Earth.New Zealand" as the @EnterMasterGroupName parameter >> value. >> >> That's good but I also want another stored procedure that just returns: >> >> Earth.New Zealand.Auckland >> Earth.New Zealand.Wellington >> >> In other words just a single level rather than all levels down. >> >> I am thinking to count the full-stops in the GroupName and filter where >> CountFullStops(@EnterMasterGroupName)+1=CountFullStops(GroupName) >> >> So it would only pick up the next level of GroupNames. >> >> Can someone help me with the syntax/strategy for coding this? The field >> is indexed. Is there a CountTextItem sort of function in SQL? >> >> TIA >> >> >> >> >> |