• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

Sybase SQL

Hi experts,
would appreciate some help here.

I have a table Groups and a stored proc as below:
CREATE PROCEDURE dbo.getGroups(
   @group_name CHAR(50) = NULL)
AS
   if (@group_name = NULL )
           select * from Groups order by group_name, group_description
     else
        select * from Groups where upper(group_name) like upper(@group_name) order by group_name, group_description

This sp needs to  be enhanced to return an additional value by name (is_in_use) including all the above cols, so that users know if group is in use or not.

the way to determine is_in_use is based on sql:
select * from Groups g, groupprofile gp
where gp.prod_group = g.group_name
this should run for each group and if it returns a row (1 or more than 1) - is_in_use should be true, else false..

could you kindly help me with this -- how can I return is_in_use flag for each group from my sp..
thanks
0
irodov
Asked:
irodov
  • 6
  • 3
  • 3
2 Solutions
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi irodov,

A simple join will do.  :)  And you've already done that, though I suggest an outer join.  The inner join that you've selected will filter out the groups where is_in_use would be false.

select g.*, gp.is_in_use
from Groups g
left outer join
(
  select group_name, count(*) as is_in_use
  from groupprofile
) gp
where gp.prod_group = g.group_name

Note that is_in_use will be null for false and non-zero for true, but you can modify that in the query.



Good Luck,
Kent
0
 
grant300Commented:
Try this....

SELECT G.*, CASE WHEN COUNT(GP.prod_group) > 0 THEN 'Y' ELSE 'N' END AS is_in_use
   FROM Groups G
    LEFT OUTER JOIN GroupProfile GP
       ON GP.prod_group = G.group_name
 GROUP BY < all the items in the select list implied by '*'>

When you compile a stored procedure having an '*' in the select list, the compiler will immediately expand it to the explicit list of columns.

Regards,
Bill
0
 
irodovAuthor Commented:
Thanks,

I tried with Bill's idea because is_in_use should never be null.
It seems to be working fine.

But Bill -
in my Groups table -- group_name is unique.. so this table cannot have 2 TEST group.

so I should just group_by group_name right?
or are you saying --- I should include all the columns in Groups table as well as is_in_use in group by
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
irodovAuthor Commented:
also, one more on this -
I am doing in my sp as below:
select * from Groups where upper(group_name) like upper(@group_name) order by group_name, group_description

I want to do it by using @group_name%
so if "A" is passed, it shold return all rows
which start with A or a.

Can you help me with  that.
thanks,

0
 
grant300Commented:
Note the change in the ON clause.

SELECT G.*, CASE WHEN COUNT(GP.prod_group) > 0 THEN 'Y' ELSE 'N' END AS is_in_use
   FROM Groups G
    LEFT OUTER JOIN GroupProfile GP
       ON GP.prod_group LIKE G.group_name + '%'
 GROUP BY < all the items in the select list implied by '*'>

BTW, you should make certain that your data is organized in such a way that the wildcarding does not return a bunch of stuff you don't really want.

Regards,
Bill
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi iridov,

Sorry.  I'd assume that you needed a boost and not the entire query.

Here's mine solution again, with the flag set appropriately.  Depending on how much data you're dealing with and how it's indexed, you may find either Bill's solution or mine runs quite a bit faster than the other.

select g.*, case when gp.is_in_use is null then 0 else 1 end as is_in_use
from Groups g
left outer join
(
  select group_name, count(*) as is_in_use
  from groupprofile
) gp
where gp.prod_group = g.group_name


Good Luck,
Kent
0
 
irodovAuthor Commented:
oK,

I changed to below:
CREATE PROCEDURE dbo.getGroups(
   @group_name CHAR(50) = NULL)
AS
   if (@group_name = NULL )
            SELECT pg.*, CASE WHEN COUNT(sc.prod_group) > 0 THEN 'Y' ELSE 'N' END AS is_in_use
           FROM Groups pg
           LEFT OUTER JOIN groupprofile sc
            ON sc.prod_group = pg.group_name
            GROUP BY group_name
     else
        SELECT pg.*, CASE WHEN COUNT(sc.prod_group) > 0 THEN 'Y' ELSE 'N' END AS is_in_use
           FROM Groups pg
           LEFT OUTER JOIN groupprofilesc
            ON sc.prod_group = pg.group_name
            where upper(pg.group_name) like upper(@group_name)+'%'
            GROUP BY group_name
        RETURN 0

it runs fine as
exec getGroups
exec getGroups 'TEST'
but doesn't bring results if I do the below

exec getGroups 't'

what AM i missing?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi irodov,

Do you have a group named 't'?


Good Luck,
Kent
0
 
irodovAuthor Commented:
well,
I have a group_name TEST.

so it should bring TEST group.
I am doing upper and adding % to it.
0
 
irodovAuthor Commented:
exec getGroups 'TEST'
is bringing TEST group without issues.

but exec getGroups 't'  doesn't bring TEST group
0
 
irodovAuthor Commented:
any help please
0
 
grant300Commented:
Rule one of debugging: change only one thing at a time.

Try

exec getGroups 'T'

This will tell you if the problem is with capitalization or with the wildcarding.

Regards,
Bill
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now