Create an SQL View from VBA

Hello All;

I'm new to SQL, and developing my first application using SQL 2000 as the back end for my data.

Can anyone tell me if I can create an SQL View from VBA?

I'm trying to create a data view that access various records from a single table, and it has to distribute that data 2 14 different subforms, representing different days in a 2 week period, which is distingiushed by a field called "Day", that is numbered from 1 to 14.

I also have a summary subform that has the weekly summary (days 1 to 7 and 8 to 14) on seperate subforms. In total I have 16 subforms, which actually source from a single table, just split depending on the day in the period.

As I see it now, creating views iis the best way for me to go, but I also have to be able to change the period id on the fly, so I'm think I have to use VBA to generate these views.

Does any of this make sense, and am I on the right track??You might want to consider a more dynamic solution. Do the 14 forms all hold the same data fields? If so, why not use one form and base the contents on the day of a week. Make the day of the week a field in your table and populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

Fixing your schema now will pay you back many fold in the future.

Avoid creating database objects on the fly in end user applications.

To answer your question, yes this is possible. Is it a good idea? No.|||... populate your form based on a stored procedure that uses the day of the week as a parameter in the query.

Fixing your schema now will pay you back many fold in the future.

Avoid creating database objects on the fly in end user applications.

To answer your question, yes this is possible. Is it a good idea? No.

Agreed with all the points here. You might also consider a user defined function that returns a table. I don't generally use these for multi-column result sets, but it is permissible to do so.

Perhaps you could post some ddl and sample data and improve your chance for getting a useable answer...

Regards,

hmscott

Create A/R distribution using IF...THEN or CASE

I am trying to write a query in Transact-SQL to create a user view in my SQL
database. I am trying to populate columns for each "aging" category (30, 60,
90, etc), so the correct age receives the amount due, but other columns are
zero. However, I can't find the correct CASE or IF...THEN syntax. It needs t
o
do something equal to the following:
If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
CurrentDue = 0.
If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else 30DayDue
= 0...
etc
I would be grateful for help...Thank you.Try using CASE expressions like the snippet below. See the Books Online for
more info.
SELECT
CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END AS
"CurrentDue",
CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END AS
"30DayDue"
etc...
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>I am trying to write a query in Transact-SQL to create a user view in my
>SQL
> database. I am trying to populate columns for each "aging" category (30,
> 60,
> 90, etc), so the correct age receives the amount due, but other columns
> are
> zero. However, I can't find the correct CASE or IF...THEN syntax. It needs
> to
> do something equal to the following:
> If ServiceDateAge BETWEEN 0 AND 29 Then CurrentDue = PatientDue Else
> CurrentDue = 0.
> If ServiceDateAge BETWEEN 30 and 59 Then 30DayDue = PatientDue Else
> 30DayDue
> = 0...
> etc
> I would be grateful for help...Thank you.
>|||Thank you. That should do it. I did not see an example in the on-line books
of using CASE to populate a quantity in a column (thought it may have been
there).
"Dan Guzman" wrote:

> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29 I
want to update CurrentPatientDue to PatientDue but at the same time also
update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
writing another full CASE statement?
"Dan Guzman" wrote:

> Try using CASE expressions like the snippet below. See the Books Online f
or
> more info.
> SELECT
> CASE WHEN ServiceDateAge BETWEEN 0 AND 29 THEN PatientDue ELSE 0 END A
S
> "CurrentDue",
> CASE WHEN ServiceDateAge BETWEEN 30 AND 59 Then PatientDue ELSE 0 END
AS
> "30DayDue"
> etc...
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:30EBD6C4-AB7F-4F70-93EB-8C68AB5646C0@.microsoft.com...
>
>|||The important concept here is that SQL CASE is an expression and not a
statement. It simply returns a single value conditionally so you need to
replicate the CASE expression. If you are using SQL 2000, you can
encapsulate CASE in a user-defined scalar function like the example below.
CREATE FUNCTION dbo.AgeAmount
(
LowAge int,
HighAge int,
Age int,
Value int
)
RETURNS int
AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
GO
SELECT
dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
"CurrentInsuranceDue"
etc..
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
> Dan one more question: Let's say that when ServiceDateAge BETWEEN 0 AND 29
> I
> want to update CurrentPatientDue to PatientDue but at the same time also
> update CurrentInsuranceDue to InsuranceDue. Can I achieve this without
> writing another full CASE statement?
> "Dan Guzman" wrote:
>|||This opens up a whole new area for me to learn and use. However, my first
attempt is producing an error I don't understand. Instead of declaring the
function every time, I thought I would attempt to create a "User Defined
Function". In the properties dialog I believe I am using your text as follow
s:
CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value int)
RETURNS int AS
BEGIN
(RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END)
END
GO
When I try to save this or check the syntax, the error is: "Must declare the
variable @.age". Can you help me with this as well, please?
"Dan Guzman" wrote:

> The important concept here is that SQL CASE is an expression and not a
> statement. It simply returns a single value conditionally so you need to
> replicate the CASE expression. If you are using SQL 2000, you can
> encapsulate CASE in a user-defined scalar function like the example below.
>
> CREATE FUNCTION dbo.AgeAmount
> (
> LowAge int,
> HighAge int,
> Age int,
> Value int
> )
> RETURNS int
> AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> GO
> SELECT
> dbo.AgeAmount(0, 29, ServiceDateAge, PatientDue) AS "CurrentDue",
> dbo.AgeAmount(30, 59, ServiceDateAge, PatientDue) AS "30DayDue",
> dbo.AgeAmount(0, 29, ServiceDateAge, CurrentInsuranceDue) AS
> "CurrentInsuranceDue"
> etc..
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:79FB0AFA-77E7-46CA-93CA-1B815E514B8F@.microsoft.com...
>
>|||Try running the following using Query Analyzer:
CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
int)
RETURNS int AS
BEGIN
RETURN
CASE
WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
ELSE 0
END
END
Hope this helps.
Dan Guzman
SQL Server MVP
"richardb" <richardb@.discussions.microsoft.com> wrote in message
news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
> This opens up a whole new area for me to learn and use. However, my first
> attempt is producing an error I don't understand. Instead of declaring the
> function every time, I thought I would attempt to create a "User Defined
> Function". In the properties dialog I believe I am using your text as
> follows:
> CREATE FUNCTION dbo.AgeAmount (LowAge int, HighAge int, Age int, Value
> int)
> RETURNS int AS
> BEGIN
> (RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END)
> END
> GO
> When I try to save this or check the syntax, the error is: "Must declare
> the
> variable @.age". Can you help me with this as well, please?
> "Dan Guzman" wrote:
>|||Hello Dan,
OK I'm in business. However, I ran the script in Query Analyzer. It ran
successfully, but I could not find my function, even though I'm sure I was
accessing the correct database. So, I opened a new user defined function,
deleted the standard text and inserted your text. This saved with no errors
and I am using the function now. Thank you for introducing me to this
interesting area of SQL 2000.
"Dan Guzman" wrote:

> Try running the following using Query Analyzer:
> CREATE FUNCTION dbo.AgeAmount (@.LowAge int, @.HighAge int, @.Age int, @.Value
> int)
> RETURNS int AS
> BEGIN
> RETURN
> CASE
> WHEN @.Age BETWEEN @.LowAge AND @.HighAge THEN @.Value
> ELSE 0
> END
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "richardb" <richardb@.discussions.microsoft.com> wrote in message
> news:6FB45184-BC27-4FC4-8D7F-81208CA173BF@.microsoft.com...
>
>

Create a view, failed

I tried to create a view with the next select, but the Enterprise Manager
send a message, "El Dise?ador de consultas no admite la interpretación SQL
de
CASE."
Not accept a CASE sql. I need your help to try other way. Thanks.
SELECT
'Raz_01'=
CASE
WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
(TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
TOTAL_ATUAL_MES_01*100
WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
(200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
ELSE 100
--Repeat this with other month 2,3,4-12--
END, CD_PAIS,COD_IND_LOC
From dbo.Datos_reales_2005
ORDER BY CD_PAIS, COD_IND_LOCUse SQL Query Analyzer instead.
AMB
"espinfire" wrote:

> I tried to create a view with the next select, but the Enterprise Manager
> send a message, "El Dise?ador de consultas no admite la interpretación S
QL de
> CASE."
> Not accept a CASE sql. I need your help to try other way. Thanks.
> SELECT
> 'Raz_01'=
> CASE
> WHEN TOTAL_ATUAL_MES_01 >= 0 and Bud_Atual_MES_01 > 0 THEN
> (TOTAL_ATUAL_MES_01/Bud_Atual_MES_01)*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 > 0 THEN
> ((ABS(TOTAL_ATUAL_MES_01)+ Bud_Atual_MES_01)/Bud_Atual_MES_01*100*(-1))-10
0
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 = 0 THEN
> TOTAL_ATUAL_MES_01*100
> WHEN TOTAL_ATUAL_MES_01 > 0 and Bud_Atual_MES_01 < 0 THEN
> ((TOTAL_ATUAL_MES_01+ABS(Bud_Atual_MES_0
1))/ABS(Bud_Atual_MES_01))*100
> WHEN TOTAL_ATUAL_MES_01 = 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> WHEN TOTAL_ATUAL_MES_01 < 0 and Bud_Atual_MES_01 < 0 THEN
> (200-TOTAL_ATUAL_MES_01/Bud_Atual_MES_01*100)
> ELSE 100
> --Repeat this with other month 2,3,4-12--
> END, CD_PAIS,COD_IND_LOC
> From dbo.Datos_reales_2005
> ORDER BY CD_PAIS, COD_IND_LOCsql

Create a view to get latest status for each application

I would like some help creating a view that will display the latest status for each application. The lastest status should be based on CreateDt.

For example:

Table Structure:

============

Application: ApplicationID, Name, Address, City, State, Zip, etc..

ApplicationAction: ApplicationActionID, ApplicationID, Status (Ex:new, reviewed, approved, closed), CreateDt

View should display:

==============

ApplicantID, ApplicantActionID, Status, CreateDt

Example:

==========

ApplicantID=4, Name=Bob Smith, etc...

ApplicantActionID=1, ApplicantID=4, Status=New, CreatDt=1/3/20071:00

ApplicantActionID=2, ApplicantID=4, Status=Reviewed, CreatDt=1/3/2007 2:00

ApplicantActionID=3, ApplicantID=4, Status=Approved, CreatDt=1/4/2007 1:00

... etc...

View should return:

Applicant=4, ApplicantActionID=3, Status=Approved, CreatDt=1/4/2007 1:00

etc...

Hint: Use MAX(CreatDt) to get the information you need.|||

well I got that far, I need help getting beyond that:( Any ideas?

|||Can you post what you have so far?|||

Oh well..

Declare @.ApTable (ApplicationIDint ,Name varchar(50), Addressvarchar(50), Cityvarchar(50), Statevarchar(50), Zipvarchar(50))Declare @.AATable (ApplicationActionIDint, ApplicationIDint, Statusvarchar(50), CreateDtdatetime)Insert into @.ApSELECt 4,'Bob Smith','123 street','SomeCity','SS','12345'UNIONALLSELECT 5,'New Smith','sss','ss','a','4455'Insert into @.AASELECT 1,4,'New','1/3/2007 1:00'UNIONALLSELECT 2,4,'Reviewed','1/3/2007 2:00'UNIONALLSELECT 3,4,'Approved','1/4/2007 1:00'UNIONALLSELECT 1,5,'New','5/24/2006 1:00'Select *from @.ApSelect *from @.AASELECT aa.*FROM (select applicationid,max(Createdt)as MxCreateDtfrom @.AAgroup by applicationid) Xjoin @.AA AAon aa.createdt = X.MXcreatedt

create a view that return data and its count of description

I have a view that has a link to 3 tables.
I need to dispaly only the data in one table and list how
many that data is defined in one table.
here is the code for the first view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
All I need is the oilName and its COUNT of cautions.
let say the oil name is "Basil" and it has three counts.
All I need is Basil in one coumn and in the other coulmn
I need "3"
Thank youWithout DDL, I'm taking some guesses, but try:
SELECT o.oilName, count(*) AS CautionCount
FROM Oils AS o
join OilCautions AS oc
on o.OILID = oc.OILID
group by o.oilName
HTH
Vern
>--Original Message--
>I have a view that has a link to 3 tables.
>I need to dispaly only the data in one table and list how
>many that data is defined in one table.
>here is the code for the first view.
>USE Aromatherapy
>GO
>if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
>= 'V')
> DROP VIEW
> Oils_Cautions_View
>go
>CREATE VIEW Oils_Cautions_View AS
>SELECT o.oilID, oilName,
>Description FROM Oils AS o, Cautions as c, OilCautions as
>oc
>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>All I need is the oilName and its COUNT of cautions.
>let say the oil name is "Basil" and it has three counts.
>All I need is Basil in one coumn and in the other coulmn
>I need "3"
>Thank you
>.
>|||Hi Vern,
Thank you very much for your time
Cristian
>--Original Message--
>Without DDL, I'm taking some guesses, but try:
>SELECT o.oilName, count(*) AS CautionCount
>FROM Oils AS o
>join OilCautions AS oc
> on o.OILID = oc.OILID
>group by o.oilName
>HTH
>Vern
>>--Original Message--
>>I have a view that has a link to 3 tables.
>>I need to dispaly only the data in one table and list
how
>>many that data is defined in one table.
>>here is the code for the first view.
>>USE Aromatherapy
>>GO
>>if exists (select name from sysobjects
>> where name = 'Oils_Cautions_View' and
type
>>= 'V')
>> DROP VIEW
>> Oils_Cautions_View
>>go
>>CREATE VIEW Oils_Cautions_View AS
>>SELECT o.oilID, oilName,
>>Description FROM Oils AS o, Cautions as c, OilCautions
as
>>oc
>>Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
>>All I need is the oilName and its COUNT of cautions.
>>let say the oil name is "Basil" and it has three counts.
>>All I need is Basil in one coumn and in the other
coulmn
>>I need "3"
>>Thank you
>>.
>.
>

Create a view on another DB

Hello !

I have a "SQLServer Express" database andI would like to create a view in it, pointing to data from another database (Progress)

Is this possible ? How ?

Thanks,

Ok I succeed using the linked servers.

However I have read that we could use the syntax

select * from linked_server_name..schema.table

but this gives me an error. It's ok when I use :

select * from OPENQUERY(linked_server_name, 'select * from schema.table')

I cannot use the first syntax ?

create a view of or select from a table on another DB

Does anyone know how you could create a view in one db of a table in another db on the same sql server? or how to perform a cross db join in a query?
thanks!Eg;
create view temp as
select a.* from
master..syslogins a, msdb..sysjobs

Use the books online feature that comes with SQL server. Most useful!
Meera

Create a view in VBA

Hi,

Is it possible to create some SQL in VBA, 'run it' then view as a datasheet
the results?

I'm trying to throw together a fairly large search form, and VBA seems the
best way to parse the parameters.

Cheers,
Chris"Not Me" <Not.Me@.faker.fake.fa.kee> wrote in message
news:c6rag7$kt$1@.ucsnew1.ncl.ac.uk...
> Hi,
> Is it possible to create some SQL in VBA, 'run it' then view as a
datasheet
> the results?
> I'm trying to throw together a fairly large search form, and VBA seems the
> best way to parse the parameters.
> Cheers,
> Chris

Yes - you could use the ADO COM objects, then return results to your client
and format/present them there. If you need a more precise answer, then it
would help to have some more detail about what you're trying to do and what
tools you're using (eg. Access, Excel etc.).

Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:40913ac0$1_1@.news.bluewin.ch...
> "Not Me" <Not.Me@.faker.fake.fa.kee> wrote in message
> news:c6rag7$kt$1@.ucsnew1.ncl.ac.uk...
> > Hi,
> > Is it possible to create some SQL in VBA, 'run it' then view as a
> datasheet
> > the results?
> > I'm trying to throw together a fairly large search form, and VBA seems
the
> > best way to parse the parameters.
> Yes - you could use the ADO COM objects, then return results to your
client
> and format/present them there. If you need a more precise answer, then it
> would help to have some more detail about what you're trying to do and
what
> tools you're using (eg. Access, Excel etc.).

Thanks for the reply.
In the past, I have been able to pre-write a query in Access, (i.e. have it
saved in the queries list) and use docmd.openquery in VBA to have it
displayed on screen.
I have also been able to create queries 'on the fly' in VBA, and be able to
iterate through them/work on them in the same program.
What I'd like to do is sort of combine the above, so create a query in VBA
and have it displayed in a datasheet view. So the user in an ADE would
select from a few comboboxes, press go and up would pop the resulting query.

Cheers,
Chrissql

Create a View in MSSQL 2000

I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!I have created a view in MS SQL2000 as followed:

Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip,
EMAILaddress
FROM orders;

my question is: If the email exist in the EMAILaddress column then I need to have a Y show in another column called EMAILflag, if the EMAILaddress does not exist then I would need the EMAILflag to be a N.

Any HELP would be GREAT.
Thank You!!

Check out the CASE Statement in Books on Line.

Regards,

hmscott|||Select order_NO, shiptoname, Shiptoaddress, Shiptocity,shiptostate, shiptozip, EMAILaddress
FROM orders;

Semi-Colon(";")

Is this for DB2 or Oracle 8i?

SQL Server would be

SELECT order_NO
, shiptoname
, Shiptoaddress
, Shiptocity
, shiptostate
, shiptozip
, EMAILaddress
, CASE WHEN EMAILaddress IS NULL THEN 'N' ELSE 'Y' END AS EMAILflag
FROM orders
GO|||Thank You!

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SR
Not sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a View Command in Query Analyzer

Hey...a simple question that I forgot...
May I know how to create a view under Query Analyzer...below is what i have
type, but command succesfully, but view not created...wonder why ?
CREATE VIEW dbo.VIEW1
AS
SELECT * FROM Table1If the command was executed successfull the view was created unless you
didnt turn on something like SET Parseonly etc. Did youdirectly Select from
the row after it was created ? "Select * from View1"
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>|||but I tried to go to Enterprise Manager, SQL Server, mydatabase, then under
the VIEWS there...
i click...the newly created View1 is not shown there, but I am able to
Select * from View1...
May I know how to make my View1 to be able to see from the enterprise
manager.
thank you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||The gui not always refreshs the right way, did you right click on the View
node and selected Refresh ? That should do it if you placed it in the right
database. Perhaps you issued the command in the wrong database ? Just proof
It via Select @.@.servername,DB_NAME().
If the view was created it should appear in the INFORMATION_SCHEMA:
Select * from INFORMATION_SCHEMA.Tables
Where TABLE_NAME LIKE 'View1'
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:OAKfHkPXFHA.2540@.tk2msftngp13.phx.gbl...
> but I tried to go to Enterprise Manager, SQL Server, mydatabase, then
> under the VIEWS there...
> i click...the newly created View1 is not shown there, but I am able to
> Select * from View1...
> May I know how to make my View1 to be able to see from the enterprise
> manager.
> thank you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||YES, Jens...I am done with that...
Just wonder how could I delete a view then ?
thanks you
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
> If the command was executed successfull the view was created unless you
> didnt turn on something like SET Parseonly etc. Did youdirectly Select
> from the row after it was created ? "Select * from View1"
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> " A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
> news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
>|||Hi,
I have a suggestion here.
Do you have dbo right to view all objects?
Since you have created it using dbo, I am not sure whether you are using dbo
right to view the VIEW1.
another thing is, you can check BOL on how to drop a view.
Syntax
DROP VIEW { view } [ ,...n ]
thanks.
Leo Leong|||Drop View [Viewname]
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
" A_PK" <pk999@.hotmail.com> schrieb im Newsbeitrag
news:%23PT391PXFHA.3572@.TK2MSFTNGP12.phx.gbl...
> YES, Jens...I am done with that...
> Just wonder how could I delete a view then ?
> thanks you
> "Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote
> in message news:OfrB4bPXFHA.3176@.TK2MSFTNGP12.phx.gbl...
>|||I think you were connected to a different database in Query Analyzer than
you think you were (the one you were looking in in Enterprise Manager). You
can use the Object Search (F4) in Query Analyzer to search for a view called
View1 in all your databases.
Jacco Schalkwijk
SQL Server MVP
" A_PK" <pk999@.hotmail.com> wrote in message
news:uayvjYPXFHA.2740@.TK2MSFTNGP14.phx.gbl...
> Hey...a simple question that I forgot...
> May I know how to create a view under Query Analyzer...below is what i
> have type, but command succesfully, but view not created...wonder why ?
> CREATE VIEW dbo.VIEW1
> AS
> SELECT * FROM Table1
>sql

Create a view based on variables?

Hi All,

I would like to create a view based on a variable (a date).

CREATE VIEW testView (@.myDate)
AS

select * from testTable
WHERE testVar = @.myDate

--
This is far from what the final view would look like, but if anyone knows if something similiar to this can be done, I would greatly appreciate it.

Thanks.I would say "No, it can't." Why can't you just create the view without the where clause and select from it where the date = @.myDate when you need it? You could create a function that returns a table and takes the date as an argument.

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a View

I have a table in a database that has very old and not very relational and I want to create a quick view to show the information in a better way. Let's say that the table has 4 fields : id , child1, child2, child3. I want to create a view from this table that will show two fields: id and child. So, my table currently looks like this:

id child1 child2 child3

1 sam bob chris

and i would like the view to display the information like this.....

id child

1 sam

1 bob

1 chris



Can anybody help me? Thanks in advance,

BobIf your DBMS permits a union view, try:

create veiw view1
as
Select id, child1 child
from table1
union
Select id, child2 child
from table1
union
Select id, child3 child
from table1;

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

create a script to display all the rows of the view

I have a view that has a link to 3 tables.
I need to display all the rows of the view.
here is the code for the view.
USE Aromatherapy
GO
if exists (select name from sysobjects
where name = 'Oils_Cautions_View' and type
= 'V')
DROP VIEW
Oils_Cautions_View
go
CREATE VIEW Oils_Cautions_View AS
SELECT o.oilID, oilName,
Description FROM Oils AS o, Cautions as c, OilCautions as
oc
Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
I like to thank you ahead for the help
Thank you
Cristianselect * from Oils_Cautions_View
"Cristian" <vitanc@.hotmail.com> wrote in message
news:0c9901c392a4$2903c2b0$a001280a@.phx.gbl...
> I have a view that has a link to 3 tables.
> I need to display all the rows of the view.
> here is the code for the view.
> USE Aromatherapy
> GO
> if exists (select name from sysobjects
> where name = 'Oils_Cautions_View' and type
> = 'V')
> DROP VIEW
> Oils_Cautions_View
> go
> CREATE VIEW Oils_Cautions_View AS
> SELECT o.oilID, oilName,
> Description FROM Oils AS o, Cautions as c, OilCautions as
> oc
> Where o.OILID = oc.OilID AND c.CautionID = oc.cautionID
> I like to thank you ahead for the help
> Thank you
> Cristian
>|||1. Create DB Links OLE DB SQL Links for all the 3 servers.
say:
DBLINK1 points to Server1.employee
DBLINK2 points to Server2.employee
DBLINK3 points to Server3.employee
create view v_employee as
select * from dblink1...employee
union
select * from dblink2...employee
union
select * from dblink3...employee
go
select * from v_employee
There is no need for 'Hardcoding' the db servername.
If server needs to changed, you change it at the Link creation time.

Create a cached instance of a view from a linked server?

Hi
How do i manage to create a cache of a view from a linked server?
I have this view on a linked server (takes app. 15 secs) and i would like to
have a "local" cache of this view.
What would be the best way to handle this - to ensure both performance and
consistency?
Hope somebody can give me an answer to this.. :-)Hi
Persist the data to a local table, and have a job that refreshes the table
on a regular basis.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> Hi
> How do i manage to create a cache of a view from a linked server?
> I have this view on a linked server (takes app. 15 secs) and i would like
> to
> have a "local" cache of this view.
> What would be the best way to handle this - to ensure both performance and
> consistency?
> Hope somebody can give me an answer to this.. :-)|||Ok, thanks...
Thought that would be the best way - but didn't know if there were any
built-in features that allowed me to do this easier.
But thanks - i'll take this road then... :-)
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> Persist the data to a local table, and have a job that refreshes the table
> on a regular basis.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pnp" <pnp@.discussions.microsoft.com> wrote in message
> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
>
>|||Replication is an option. But I wouldn't do it for just one table. Keep it s
imple.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:4D5977DE-FB7A-4C19-8236-6E3BC3B70C8A@.microsoft.com...[vbcol=seagreen]
> Ok, thanks...
> Thought that would be the best way - but didn't know if there were any
> built-in features that allowed me to do this easier.
> But thanks - i'll take this road then... :-)
> "Mike Epprecht (SQL MVP)" wrote:
>

Create a cached instance of a view from a linked server?

Hi
How do i manage to create a cache of a view from a linked server?
I have this view on a linked server (takes app. 15 secs) and i would like to
have a "local" cache of this view.
What would be the best way to handle this - to ensure both performance and
consistency?
Hope somebody can give me an answer to this.. :-)Hi
Persist the data to a local table, and have a job that refreshes the table
on a regular basis.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> Hi
> How do i manage to create a cache of a view from a linked server?
> I have this view on a linked server (takes app. 15 secs) and i would like
> to
> have a "local" cache of this view.
> What would be the best way to handle this - to ensure both performance and
> consistency?
> Hope somebody can give me an answer to this.. :-)|||Ok, thanks...
Thought that would be the best way - but didn't know if there were any
built-in features that allowed me to do this easier.
But thanks - i'll take this road then... :-)
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> Persist the data to a local table, and have a job that refreshes the table
> on a regular basis.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "pnp" <pnp@.discussions.microsoft.com> wrote in message
> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
> > Hi
> >
> > How do i manage to create a cache of a view from a linked server?
> >
> > I have this view on a linked server (takes app. 15 secs) and i would like
> > to
> > have a "local" cache of this view.
> >
> > What would be the best way to handle this - to ensure both performance and
> > consistency?
> >
> > Hope somebody can give me an answer to this.. :-)
>
>|||Replication is an option. But I wouldn't do it for just one table. Keep it simple.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"pnp" <pnp@.discussions.microsoft.com> wrote in message
news:4D5977DE-FB7A-4C19-8236-6E3BC3B70C8A@.microsoft.com...
> Ok, thanks...
> Thought that would be the best way - but didn't know if there were any
> built-in features that allowed me to do this easier.
> But thanks - i'll take this road then... :-)
> "Mike Epprecht (SQL MVP)" wrote:
>> Hi
>> Persist the data to a local table, and have a job that refreshes the table
>> on a regular basis.
>> Regards
>> --
>> Mike Epprecht, Microsoft SQL Server MVP
>> Zurich, Switzerland
>> IM: mike@.epprecht.net
>> MVP Program: http://www.microsoft.com/mvp
>> Blog: http://www.msmvps.com/epprecht/
>> "pnp" <pnp@.discussions.microsoft.com> wrote in message
>> news:ADA26A03-95C5-4737-9176-B4C114E15EA6@.microsoft.com...
>> > Hi
>> >
>> > How do i manage to create a cache of a view from a linked server?
>> >
>> > I have this view on a linked server (takes app. 15 secs) and i would like
>> > to
>> > have a "local" cache of this view.
>> >
>> > What would be the best way to handle this - to ensure both performance and
>> > consistency?
>> >
>> > Hope somebody can give me an answer to this.. :-)
>>