Solved

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

Posted on 2011-02-14
5
195 Views
Last Modified: 2012-05-11
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
Comment
Question by:vijay11
  • 3
5 Comments
 
LVL 40

Expert Comment

by:Kyle Abrahams
ID: 34890265
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
 

Author Comment

by:vijay11
ID: 34890424
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
 

Author Comment

by:vijay11
ID: 34890444
and there are 2 update statements . So there can be parent\child\child relation
0
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 34891478
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
 

Author Closing Comment

by:vijay11
ID: 35012927
This was not the accurate answer but It helped me to re write the code my self
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A theme is a collection of property settings that allow you to define the look of pages and controls, and then apply the look consistently across pages in an application. Themes can be made up of a set of elements: skins, style sheets, images, and o…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question