March 10, 2008 at 2:17 am
I hope I am in the right forum.
I am running the stored proc below that creates a view.
Is the code I`m using valid??
CODE:
set @w_sql = 'CREATE VIEW ' + @DstShmNme + 'v' + @DstTblNme + '
AS
DELETE *
FROM ( SELECT * FROM ' + @DstShmNme + 'v' + @DstTblNme + '
WHERE ACRMVTTYP_CDE IN ' + @q + 'CL' + @q + ',' + @q + 'CF' + @q + ')
WHERE GRPBNF_CDE= ' + @q + 'QF' + @q + 'AND ptMEMBNFSTT_CDE<> ' + @q + 'AA' + @q + ' '
execute (@w_sql)
Before I did this I actually created a table and then executed the two queries and then created the view. With the above I wanna get rid of the table.
Any help would be appreciated as this is my first attempt at a stored proc.
March 10, 2008 at 3:08 am
rrhode (3/10/2008)
I hope I am in the right forum.I am running the stored proc below that creates a view.
Is the code I`m using valid??
CODE:
set @w_sql = 'CREATE VIEW ' + @DstShmNme + 'v' + @DstTblNme + '
AS
DELETE *
FROM ( SELECT * FROM ' + @DstShmNme + 'v' + @DstTblNme + '
WHERE ACRMVTTYP_CDE IN ' + @q + 'CL' + @q + ',' + @q + 'CF' + @q + ')
WHERE GRPBNF_CDE= ' + @q + 'QF' + @q + 'AND ptMEMBNFSTT_CDE<> ' + @q + 'AA' + @q + ' '
execute (@w_sql)
Before I did this I actually created a table and then executed the two queries and then created the view. With the above I wanna get rid of the table.
Any help would be appreciated as this is my first attempt at a stored proc.
The code is rather interesting, and I'm having problems understanding it (probably so does SQL Server :)). It creates a view and the statement within is: DELETE * FROM (some select statement).
I'd suggest that instead of using "execute (@w_sql)" you first do a "print @w_sql" and see if the generated statement is the one you would like to be created. When you say you want to get rid of the table, do you mean delete that data from it or to drop the table itself?
Regards,
Andras
March 10, 2008 at 3:49 am
Thanks very much for the speedy reply. This the result I got from print (@w_sql)
CREATE VIEW GI.vGI_CL_200712_EBVG_RJ_Test
AS
DELETE *
FROM ( SELECT * FROM GI.vGI_CL_200712_EBVG_RJ_Test
WHERE ACRMVTTYP_CDE IN 'CL','CF')
WHERE GRPBNF_CDE= 'QF'AND ptMEMBNFSTT_CDE<> 'AA'
A bit of background to what I am trying to achieve, I am populating 2 tables MV table and a CL table. The CL table gets created by running 2 queries from the MV table. The two queries is the Delete and the Select(see above). The end user then links to the table to get the results using MS Access. I have decided to rather create view for CL rather than a table. What I want to do somehow create a query that will do the work of both queries and then create a view from it. i have sneakin suspicion I am going about this the wrong way:) Maybe you could suggest another route. I am not even sure if the above query is valid or not.
Thanks in advance for your help.
March 10, 2008 at 3:59 am
rrhode (3/10/2008)
Thanks very much for the speedy reply. This the result I got from print (@w_sql)CREATE VIEW GI.vGI_CL_200712_EBVG_RJ_Test
AS
DELETE *
FROM ( SELECT * FROM GI.vGI_CL_200712_EBVG_RJ_Test
WHERE ACRMVTTYP_CDE IN 'CL','CF')
WHERE GRPBNF_CDE= 'QF'AND ptMEMBNFSTT_CDE<> 'AA'
A bit of background to what I am trying to achieve, I am populating 2 tables MV table and a CL table. The CL table gets created by running 2 queries from the MV table. The two queries is the Delete and the Select(see above). The end user then links to the table to get the results using MS Access. I have decided to rather create view for CL rather than a table. What I want to do somehow create a query that will do the work of both queries and then create a view from it. i have sneakin suspicion I am going about this the wrong way:) Maybe you could suggest another route. I am not even sure if the above query is valid or not.
Thanks in advance for your help.
Well, you are right, the above query is not will not work. Views are there to return data (they are basically a select statement with a name). You cannot use delete inside. Why are you trying to use a delete statement above? Anyway, probably step one would be to write a select statement that returns the data you wish to have (make sure it works by executing it). You could encapsulate that into a view. If you need to modify the data in the underlying tables you may be able to do it via the view, but there are limitations (see updateable views on: http://msdn2.microsoft.com/en-us/library/ms187956.aspx)
Regards,
Andras
March 10, 2008 at 4:41 am
Thanks I got it right. Created a select statement that yielded the records I wanted and then created a view from them.
Thanks again for the assistance. Enjoy the rest of your day.:D
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply