Table variables creation problem

Hi, what i would like todo is put a multiple values result into a Table Variable and reuse it few times in the code (in the select, where, etc..)

My problems are :
- Can't use the XMLPATH method into the Insert into VAR Table
- Where i can call my insert to the var table in the code,  cause i need to know the field RequestId, witch is too soon in the following code.....

Here's my code :




DECLARE @RequestApplications TABLE
(
         Descriptions varchar(4000)
)

INSERT INTO @RequestApplications (Descriptions) (      SELECT ApplicationName + ' '
                        FROM    ApplicationsAccessRequest AAR
                                                       WHERE  AAR.RequestID = RequestID
                                       FOR XML PATH('')
                                                                                   )
-- Result will be "VPN, HS-VPN, WORD, VISIO, "


SELECT R.RequestId, (SELECT TOP 1 Descriptions FROM RequestApplications ) as Apps
FROM       Request      
WHERE            (SELECT TOP 1 Descriptions FROM RequestApplications ) LIKE '%VPN%'                                                              

Anyone know how to resolve this ?

Thanks a lot for you help and excuse my english....
dany651Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
ava23Connect With a Mentor Commented:
just insert the data from the inner query into a temp table and then use that table for querying
SO,

select RequestId, Apps =
          (select distinct [ApplicationName] + ', ' from ApplicationAccessRequest  T2
           where T1.RequestId = T2.RequestId for xml path(''))
INTO #TEMPTABLE
from ApplicationAccessRequest   T1
group by RequestId

SELECT * FROM #TEMPTABLE WHERE Apps LIKE '%MS WORD%'

DROP TABLE #TEMPTABLE
0
 
indianguru2Commented:
From what i understand from the Question you are better off with a temp table rather than a table variable.
0
 
Anthony PerkinsCommented:
Try it this way:
DECLARE @RequestApplications TABLE 
(
         Descriptions varchar(4000) 
)

INSERT @RequestApplications (Descriptions) 
SELECT Descriptions
FROM	(
	SELECT ApplicationName + ' ' Descriptions
	FROM    ApplicationsAccessRequest AAR
	WHERE  AAR.RequestID = RequestID          -- This does not make a lot of sense
	FOR XML PATH('') 
	) a

-- Result will be "VPN, HS-VPN, WORD, VISIO, "

SELECT R.RequestId, (SELECT TOP 1 Descriptions FROM RequestApplications ) as Apps
FROM       Request      
WHERE            (SELECT TOP 1 Descriptions FROM RequestApplications ) LIKE '%VPN%'  

Open in new window

0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

 
dany651Author Commented:
Hi acperkins,  i dont understant your answer,  you write "This does not make a lot of sense" but you didnt specify where i shoud call the insert in my code, i want to create the temp var for each record....
0
 
Anthony PerkinsCommented:
>>i dont understant your answer,  you write "This does not make a lot of sense"<<
I was specifically referring to the condition:
WHERE  AAR.RequestID = RequestID

That is like saying WHERE 1 = 1 so it is not necessary.

Did you try the query as is? If so, di it give you the right results?  If not what were they and what was the expected results?
0
 
ava23Commented:
I dont understand your second question, so if you could elaborate more, that would be nice. Here is the solution of your first question:


DECLARE @RequestApplications TABLE
(
         Descriptions varchar(4000)
)

DECLARE @DESC VARCHAR(4000)
SET @DESC = (select ObjectLongName + ', ' from WAcc_BusinessObject for xml path(''))

 insert into @RequestApplications
select @DESC

your insert did not work because of some limitations on usage of FOR XML:
http://msdn.microsoft.com/en-us/library/aa226520(SQL.80).aspx

I hope that helps.
0
 
dany651Author Commented:
ava23 : Everything is fine with my first problem (VS XMLPATH)  thanks a lot

Second problem :
In my var table construction i want to specify  the RequestId value of my select statement.... so i think my var table construction code should not be before my select statement cause my var table will not know my "RequestId" value.......

In my var table i only want info about one Request,  relationship is 1 to many from "Request" to "ApplicationAccessRequest"

Exemple :

Request Table                 ApplicationAccessRequest Table
RequestId                         RequestId             ApplicationName
2244                                 2244                     SAP
2245                                 2244                     Visual Studio
2246                                 2244                      MS Word
                                         2245                      Lotus Notes
                                         2246                      IE 8
                                         2246                      SAP

my select statement will return :  
RequestId    Apps
2244            SAP, Visual Studio, MS Word,
2245            Lotus Notes,
2246            IE 8, SAP,


Let me know if it's not  clear.....


Thanks a lot for your help everyone
0
 
dany651Author Commented:
Don't forget that i want to reuse my result (SAP, visual Studio, ..)  in my where clause not only in my select , it's the reason why i don't call my SUB SELECT in my main select directly....
0
 
ava23Commented:
I could only understand the tables that you have shown, so here is what i can think of to get the third table from table 1 and table 2:

select RequestId, Apps =
          (select distinct [ApplicationName] + ', ' from ApplicationAccessRequest  T2
           where T1.RequestId = T2.RequestId for xml path(''))
from ApplicationAccessRequest   T1
group by RequestId

I hope that helps, if thats not what u were looking for, please explain using tables, would be much better... thanks
0
 
dany651Author Commented:
Your solution is in the good way but what i need is reuse the result (Apps) in my where clause also to apply filter.... and i dont wanna have to recall the SELECT applicationName... 2 times
0
 
ava23Commented:
could you please explain with an example? Thanks
0
 
dany651Author Commented:
yes sure,  it's similar to your exemple except  i dont wanna call the select  2 times (one in the select for apps field and one in the where)...


select RequestId, Apps =
          (select distinct [ApplicationName] + ', ' from ApplicationAccessRequest  T2
           where T1.RequestId = T2.RequestId for xml path(''))
from ApplicationAccessRequest   T1
WHERE  (select distinct [ApplicationName] + ', ' from ApplicationAccessRequest  T2
           where T1.RequestId = T2.RequestId for xml path(''))      LIKE '%MS WORD%'


Are you following me ?

Thanks

dany
0
 
ava23Commented:
yeah, i got that, but what do you want then? what are you trying to achieve? please explain
0
 
ava23Commented:
just try an outer query:

SELECT * FROM
(select RequestId, Apps =
          (select distinct [ApplicationName] + ', ' from ApplicationAccessRequest  T2
           where T1.RequestId = T2.RequestId for xml path(''))
from ApplicationAccessRequest   T1
group by RequestId)TMP
WHERE TMP.Apps LIKE '%MS WORD%'
0
 
dany651Author Commented:
What i would like todo is :

Get my applications in my select stratement and also apply a filter (where clause) to this result

ex :

Result will be the following result cause i select the apps in my statement and i have also apply a filter to only get the record that contains the "MS WORD" applications:
RequestId         Applications
2456                   SAP, VSS, SAP, MS WORD,
3652                   VISIO, MS WORD

Will not be part of my result :
1125                   Lotus Notes,
3345                   VISIO, IE7
0
 
dany651Author Commented:
Your second result sounds perfect except its remove reference to my Request table witch i need it to get additionnal fields from this table..
0
 
dany651Author Commented:
i means last exemple (not second result)
0
 
dany651Author Commented:
This it's what i finally do,  put everything in a temp tables and then after doing select on that temps table using a where clause

Thanks a lot
0
All Courses

From novice to tech pro — start learning today.