Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 201
  • Last Modified:

Can some one re - write this sql into single select statement. It has to be compatible with sql server 2000 also

Hi ,

I have a query like this
Can 1 insert and 2 update statements can be written in single select statements (case  statement may be )
  create table #t_perm1
(
tab_name varchar(255)           null,
parent_entity varchar(255)      null,
child_entity varchar(255)       null,
parent_tool_id varchar(255)     null,
parent_tool_id2 varchar(255)    null,
menubar_id      int

)

insert into #t_perm1
select distinct 'Menus'  ,c.name , a.name, c.tool_id , c.tool_id ,b.menubar_id
from   country a, state b, country c
where  a.tool_id        = b.tool_id
and    b.parent_tool_id = c.tool_id
and    b.parent_tool_id <> ''
and    b.form           = 'main'
and    b.toolbar        = 'M'  
and    c.tool_id  not in ('cal')

update #t_perm1
set parent_entity =  c.name +'/' + parent_entity,
     parent_tool_id2 = c.tool_id
--select c.name, a.*
from   #t_perm1 a, state b, country c
where  a.parent_tool_id = b.tool_id
and    b.parent_tool_id  = c.tool_id  
and    a.parent_tool_id <> ''
and    b.form           = 'main'
and    b.toolbar        = 'M'  
and    b.tool_id        <> 'Msv'

update #t_perm1
set parent_entity =  c.name +'/' + parent_entity
--select c.name, a.*
from   #t_perm1 a, state b, country c
where  a.parent_tool_id2 = b.tool_id
and    b.parent_tool_id  = c.tool_id  
and    a.parent_tool_id2 <> ''
and    b.form           = 'main'
and    b.toolbar        = 'M'  
and    b.tool_id        <> 'Msv'

The above statement is working fine , but the requirement is to write in one Single statement.
can some one please help me
0
vijay11
Asked:
vijay11
  • 3
1 Solution
 
Kyle AbrahamsSenior .Net DeveloperCommented:
select distinct 'Menus'  , case when b.tool_id is 'Msv' then c.name else c.name +'/' + parent_entity end as parent_entity, a.name, c.tool_id , c.tool_id ,b.menubar_id
from   country a, state b, country c
where  (a.tool_id        = b.tool_id  or a.parent_tool_id2 = b.tool_id)
and    b.parent_tool_id = c.tool_id
and    b.parent_tool_id <> ''
and    b.form           = 'main'
and    b.toolbar        = 'M'  
and    c.tool_id  not in ('cal')

0
 
vijay11Author Commented:
In one of the join column  'a.parent_tool_id2' is from temp table . i am getting the same error when I execute the sql

Thanks
0
 
vijay11Author Commented:
and there are 2 update statements . So there can be parent\child\child relation
0
 
LowfatspreadCommented:
try this....

need to left join  the initial  Insert query with the other two updates
and insert (select) into the temp table...


you need to check both the result and format of the columns involved
and convert/cast as necessary....

you could also put this / the original into a stored procedure and "just" execute 1 statement...
(with a global temp table perhaps?)
Select A.Tab_name
      ,coalesce(C.Name+'/','')+coalesce(B.Name + '/','')+ A.Parent_entity as Parent_entity
      ,A.Child_entity
      ,A.parent_tool_id
      ,coalesce(B.tool_id,A.parent_tool_id2) as Parent_tool_id2
      ,A.Menubar
  Into #t_perm1
  From (
select distinct 'Menus' as Tab_name
      ,c.name as parent_entity
      , a.name as child_entity
      , c.tool_id as parent_tool_id
      , c.tool_id as parent_tool_id2
      ,b.menubar_id  as menubar_id
  from country a
 Inner Join (select * 
               from state
               Where parent_tool_id <> '' 
                 and form    = 'main' 
                 and toolbar = 'M'
            ) as b
    on a.tool_id = b.tool_id 
 Inner Join (Select * 
               from country
              where tool_id  not in ('cal')
            ) as c
    on b.parent_tool_id = c.tool_id
       ) as A

 Left Outer Join 
           (select s.tool_id as Btoolid,c.tool_id,C.Name
               from state as S
              Inner Join country c
                on S.parent_tool_id  = c.tool_id  
              where S.form           = 'main' 
                and S.toolbar        = 'M'  
                and S.tool_id        <> 'Msv'
                and S.Tool_id <> ''
            ) as B
    on A.parent_tool_id = B.btool_id 
 
 Left Outer Join
       (select b.tool_id as Btoolid,c.name
          from state b
         Inner Join country c
            on b.parent_tool_id  = c.tool_id
         Where b.form           = 'main' 
           and b.toolbar        = 'M'  
           and b.tool_id        <> 'Msv'
           and b.tool_id <> ''
       ) as C
ON  a.parent_tool_id2 = C.btool_id

Open in new window

0
 
vijay11Author Commented:
This was not the accurate answer but It helped me to re write the code my self
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

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