Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1500 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
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 to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

688 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