Not Being Able To Create A View That Requires A Loop
Oct 25, 2007
Hey
i have a table A that contains 3 columns : id, entry ,sessionid
i want to create a view on this table that will contain
- for each sessionid s in A --> select top 5 rows having s as sessionid and ordered by id desc
(s can have 1 or 2 or 5 or 300 entries i want to get only the latest 5 rows that correspond to this session)
I tried many queries and different combinations i could find one yet to do the following.
Can anyone help me plz?
Can we have a loop in a view?is it possible?
View 7 Replies
ADVERTISEMENT
Oct 15, 2015
I've got a report which, when exported to word, comes up with only the body of the report visible, and with the protected view warning present in Word 2013. When I enable editing, the header and footers appear correctly. What is happening here, and how do I correct it, if at all? I don't know that my customer is likely to accept having to enable editing to address this.
View 2 Replies
View Related
Jul 24, 2012
Write a CREATE VIEW statement that defines a view named Invoice Basic that returns three columns: VendorName, InvoiceNumber, and InvoiceTotal. Then, write a SELECT statement that returns all of the columns in the view, sorted by VendorName, where the first letter of the vendor name is N, O, or P.
This is what I have so far,
CREATE VIEW InvoiceBasic AS
SELECT VendorName, InvoiceNumber, InvoiceTotal
From Vendors JOIN Invoices
ON Vendors.VendorID = Invoices.VendorID
[code]...
View 2 Replies
View Related
Apr 4, 2007
Hello,
is it possible to build a loop for the following statement?
CREATE VIEW vwObjects as (
Select 2001 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate >= '20010101' and o.sdate < '20010401'
union
Select 2001 as year, 2 as quarter, id as id
from dbo.objects o
where o.edate >= '20010301' and o.sdate < '20010701'
...
union
Select 2002 as year, 1 as quarter, id as id
from dbo.objects o
where o.edate > '20020101' and o.sdate < '20020401'
...
)
I want a kind of calender for my olap cube, so I can get every active object in a special quarter resp year.
Thank you!
View 5 Replies
View Related
Dec 11, 2007
Hi, i have a Stored Procedure that is calling a view. I need to pass on all the records of this view so i made another view that returns the rows count and then a for loop from 0 to count-1. But then how can i access the rows and columns of the view one by one? Any idea?
And if someone has a better logic that gives the same result please let me know. Thanks...
View 10 Replies
View Related
Aug 4, 2004
Hi All,
I'm trying to create a proc for granting permission for developer, but I tried many times, still couldn't get successful, someone can help me? The original statement is:
Create PROC dbo.GrantPermission
@user1 varchar(50)
as
Grant create table to @user1
go
Grant create view to @user1
go
Grant create Procedure to @user1
Go
Thanks Guys.
View 14 Replies
View Related
Apr 12, 2006
Hi,
I have currently a problem with setting up the permissions for some developers. My configuration looks like this.
DB A is the productive database.
DB B is a kind of "development" database.
Now we have a couple of users call them BOB, DAVID, ...
who are members of the db role db_reader and db_writer for the productive db a but they should be allowed to do nearly everything on db b.
Therefor I added them to the db role db_owner for db b.
For testing purposes I tried to "CREATE" a view TEST as BOB in database B but I received the error message
'Msg 262, Level 14, State 1, Procedure Test, Line 3
CREATE VIEW permission denied in database 'b'.'
I cross checked the permissions on db level and I even granted all available permissions on db level but nevertheless I receive this error message.
What's my mistake?
Of course it worked fine when I give them sysadmin rights but then they have far too much permissions.
Regards,
Stefan
View 8 Replies
View Related
Aug 14, 2000
I had given one of our developers create view permissions, but he wants to also modify views that are not owned by him, they are owned by dbo.
I ran a profiler trace and determined that when he tries to modify a view using query designer in SQLem or right clicks in SQLem on the view and goes to properties, it is performing a ALTER VIEW. It does the same for dbo in a trace (an ALTER View). He gets a call failed and a permission error that he doesn't have create view permissions, object is owned by dbo, using both methods.
If it is doing an alter view how can I set permissions for that and why does it give a create view error when its really doing an alter view? Very confusing.
View 1 Replies
View Related
Jul 9, 2007
Hi,Right, I have this problem, and it;s more through lack of understanding vb.net that well more then an actual problem I will out line what I want to do,basically it all revolves around a page that needs to be built when navigated to so it can be easily updated without anyone having to edit the code.Get all the table names from a databaseLoop through each of the results to build a statementNest a 2nd loop to split the returned data from the correct tableBuild a listbox for each table returnedThis is what I have currently, this works but the problem is, if another course is added, someone will need to manually edit the code on the page to add a new code to get the new course hence why I want to create a loop that gets all the data so all someone needs to do is put in the all table the new course name. Please noteI cut this down to just show 2 result but there is about 30 odd. 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=x;Password=xx")
2 'Dim DBDataAdapter As SqlDataAdapter
3 DBDataAdapter = New SqlDataAdapter("Select AOE,ADC, FROM TBL_Role WHERE Title = @ddlTitle", DBConn)
4 DBDataAdapter.SelectCommand.Parameters.Add("@ddlTitle", SqlDbType.NVarChar)
5 DBDataAdapter.SelectCommand.Parameters("@ddlTitle").Value = TitleDropDown.SelectedValue
6 DBDataAdapter.Fill(DSPageData, "Courses")
7
8 'Loop through each record, split at + and place in ListBoxs
9
10 VarDS = DSPageData.Tables("Courses").Rows(0).Item("AOE")
11 Dim VarArray As String() = VarDS.Split("+")
12 Dim i As Integer
13 For i = 0 To VarArray.Length - 1
14
15 Dim li As New ListItem()
16 li.Text = VarArray(i).ToString()
17 li.Value = VarArray(i).ToString()
18 Me.txtAOE.Items.Add(li)
19 Next i
20
21 VarDS = DSPageData.Tables("Courses").Rows(0).Item("ADC")
22 VarArray = Nothing
23 VarArray = VarDS.Split("+")
24 i = Nothing
25 For i = 0 To VarArray.Length - 1
26
27 Dim li As New ListItem()
28 li.Text = VarArray(i).ToString()
29 li.Value = VarArray(i).ToString()
30 Me.txtADC.Items.Add(li)
31 Next i
Now here is my pseudo code to what I roughly want to do, hope it makes sense to someone and someone can point me in the correct direction. Please note,I know the split bit works, so at the minute I am just trying to get the loop to get all my courses 1 DBConn = New SqlConnection("Server=SD02;Initial Catalog=WhoGetsWhat;Persist Security Info=True;UID=wgw;PWD=wgwsql;")
2 DBSelect.Connection = DBConn
3 DBSelect.Connection.Open()
4 'Get the row number in the database
5 DBSelect.CommandText = "SELECT COUNT(*) FROM TBL_All"
6 DBResult = DBSelect.ExecuteScalar()
7 DBSelect.Connection.Close()
8 Dim Count = DBResult
9 'Get all the Tables and Keys in the Database's
10 DBDataAdapter = New SqlDataAdapter("SELECT * FROM TBL_All", DBConn)
11 DBDataAdapter.Fill(DSPageData, "All")
12 'declare all loop vars
13 Dim X As Integer
14 Dim Y As Integer
15 Dim i As Integer
16 'Loops through all the tables
17 Dim DSArray As String() = DSPageData.Tables("All").Items()
18 For Y = 0 To Count
19 Dim VarDS As String() = DSPageData.Tables("All").Rows(0).Item(DSArray(Y))
20 Dim SplitArray As String() = VarDS.Split("+")
21
22
23 For i = 0 To SplitArray.Length - 1
24 Dim Li As New ListItem()
25 Li.Text = SplitArray(i).ToString()
26 Li.Value = SplitArray(i).ToString()
27 Me.txt & DSArray(Y) &.Items.Add(Li)
28 Next i
29
30 Next Y
View 3 Replies
View Related
Jun 28, 2007
I have a basic while loop but I need to be able to increment the counter from one alpha character to the next:
declare @counter nvarchar(10)
set @counter = 'A'
while @counter < 'Z'
beginprint 'the counter is ' + @counter
set @counter = @counter + @counter
end
In this example I would need to see the following output:
the counter is Athe counter is Bthe counter is Cthe counter is D.....
Is this possible in SQL?
View 2 Replies
View Related
May 29, 2008
I have a stored proc I am running, and I would like to create a list of email addresses from a table and put that list into a variable. I did a basic while loop to work on syntax, but now I don't know how to actually get each address added on. Here's how I started it
declare @start int, @testEmail nvarchar(2000)
set @start = 1
set @testEmail = NULL
while @start <= (Select count(PADM_Email) from PADM_Emails)
Begin
--Print @start
set @testEmail = @testEmail + (Select distinct PADM_Email from PADM_Emails) + ';'
set @start = @start + 1
End
I know that the above is wrong, but I don't know how to get it right. Ideally, I want the @testEmail to look like this:
emailaddress1;emailaddress2;emailaddress3;
View 7 Replies
View Related
Jul 17, 2006
I need to run a script to create a trigger on 18 tables on 3 databases. The code is identical with the exception of the table and trigger names at the beginning. Does anyone know of a way to create them all with a loop instead of manually replacing the table and trigger names and executing over and over? I tried variables but get an 'Incorrect syntax near '@TriggerName' error.
if exists (select * from sysobjects where id =
object_id (N'dbo.tgUsersAudit') and
objectproperty (id, N'IsTrigger') = 1)
drop trigger dbo.tgUsersAudit
go
CREATE TRIGGER tgUsersAudit on tblUsers FOR insert, update, delete
AS
DECLARE @TableName varchar(128)
SET @TableName = tblUsers
..................from here the code is the same for all
View 11 Replies
View Related
Jun 19, 2008
Hi all
I'm new to sql and could do with some help resolving this issue.
My problem is as follows,
I have two tables a BomHeaders table and a BomComponents table which consists of all the components of the boms in the BomHeaders table.
The structure of BOMs means that BOMs reference BOMs within themselves and can potentially go down many levels:
In a simple form it would look like this:
LevelRef: BomA
1component A
1component B
1Bom D
1component C
What i would like to do is potentially create a temporary table which uses the BomReference as a parameter and will loop through the records and bring me back every component from every level
Which would in its simplest form look something like this
LevelRef: BomA
1......component A
1......component B
1......Bom D
2.........Component A
2.........Component C
2.........Bom C
3............Component F
3............Component Z
1......component C
I would like to report against this table on a regular basis for specific BomReferences and although I know some basic SQL this is a little more than at this point in time i'm capable of so any help or advice on the best method of tackling this problem would be greatly appreciated.
also i've created a bit of a diagram just in case my ideas weren't conveyed accurately.
Bill Shankley
View 4 Replies
View Related
Apr 24, 2007
What I'm trying to do is this;
I have a table with Year , Account and Amount as fields. I want to
SELECT Year, Account, sum(Amount) AS Amt
FROM GLTable
WHERE Year <= varYear
varYear being a variable which is each year from a query
SELECT Distinct Year FROM GLTable
My thought was that I would need to pass a variable into a select statement which then would be used as the source in my Data Flow Task.
What I have done is to defined two variables as follows
Name: varYear (this will hold the year)
Scope: Package
Data type: String
Name:vSQL (This will hold a SQL statement using the varYear)
Scope: Package
Data type: String
Value: "SELECT Year, Account, sum(Amount) AS Amount FROM GLTable WHERE Year <=" + @[User::varYear]
I've created a SQL Task as follows
Result set: Full Result Set
Connection Type: OLE DB
SQL Statement: SELECT DISTINCT Year FROM GLTable
Result Name: 0
Variable Name: User::varYear
Next I created a For Each Loop container with the following parameters
Enumerator: Foreach ADO Enumerator
ADO Object source Variable: User::varYear
Enumeration Mode: Rows in First Table
I then created a Data Flow Task in the Foreach Loop Container and as the source used OLE DB Source as follows
Data Access Mode: SQL Command from Variable
Variable Name: User::varYear
However this returns a couple of errors "Statement(s) could not be prepared."
and "Incorrect syntax near '='.".
I'm not sure what is wrong or if this is the right way to accomplish what I am trying to do. I got this from another thread "Passing Variables" started 15 Nov 2005.
Any help would be most appreciated.
Regards,
Bill
View 5 Replies
View Related
Jun 4, 2014
I have a for each loop(ADO Enumerator) container which executes for each Advertiserid which is coming from database. In for each loop I have to create a new excel file with the advertiser name. So if the loop executes 7 times there should be seven excel spreadsheets with seven advertiser names.
How can i create an excel dynamically in the foreach loop container.
View 10 Replies
View Related
Oct 25, 2007
I am looking for the best way in SSIS to do the following. I have an SQL table that for each row in the table I want to take an element from the table do a lookup in a Teredata Table, return information from the teredata source. Use that returned data to do some calculations and create a derived column from my calculations and place the data into the same SQL table that I am parsing through.
Ideas?
View 1 Replies
View Related
Jan 8, 2001
When I use following command it works fine.
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
but if I use following command with condition it gives me an error.
if not exists(select * from sysobjects where name ='ikw_custom' and type='V')
begin
CREATE VIEW dbo.[ikw_custom]
AS
SELECT * FROM rns_keyword.dbo.ikw_custom
end
help will be appriciated.
Thanks in advance
-Balbir
View 1 Replies
View Related
Apr 15, 2006
I am learning how to work with MSSQL
here is what I like to do
I have a table with URLs like this "www.urls.com" I need to create a view that will insert this "http://" before "www.urls.com" can some please let me know what I need to do
Thank you
View 14 Replies
View Related
Sep 25, 2005
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??
View 2 Replies
View Related
Mar 18, 2006
hi,if the below query
select DISTINCT group_module_menu_info.menulink FROM
user_group_role_info,group_role_module_info,group_ module_menu_info
where user_group_role_info.userid=user_table.userid and
((group_module_menu_info.moduleid = user_group_role_info.moduleid)OR
(user_group_role_info.roleid=group_role_module_inf o.roleid and
group_module_menu_info.moduleid = group_role_module_info.moduleid))
if i want to create a view
how ?
thank you!
View 5 Replies
View Related
Jan 26, 2006
Create a view showing every order that was shipped to Spain. Name the destination column "DestinationSpain". Include code that checks if the view already exists. If it does, it should be dropped and re-created.
Use Northwind
Go
DROP VIEW Spain_Orders
Go
CREATE VIEW Spain_Orders
AS
SELECT *
FROM Orders
WHERE ShipCountry = 'Spain'
this is what I get.
server: Msg 3701, Level 11, State 5, Line 1
Cannot drop the view 'Spain_Orders', because it does not exist in the system catalog.
albanie
View 20 Replies
View Related
Nov 29, 2007
I know IIf doesn't exist in SQL. However, I am creating a view and want to check the value of a field and if it is 1 the field returns 'This value' if it is 2 then 'That value' , if 3 then 'Another value'.
How can I do this ?
View 13 Replies
View Related
Jul 23, 2005
Hi Gurus,I'm a beginner. Would you please tell me if it's possible to create a viewhaving a calcuated column based on the condition of the column on the sqltable.create view vwImaging ASselectEmpID, LastName, FirstName, EmpTag = 'Act' ifFROM tblPerPayI have a table EMP:SSN (char 9)A view giving a formatted SSN (XXX-XX-XXXX,) a--Message posted via http://www.sqlmonster.com
View 3 Replies
View Related
Jul 20, 2005
Hi.I created a simple view with the following statements:CREATE VIEW dbo.VIEW1ASSELECT dbo.VIEW_ALL.ID,dbo.VIEW_ALL.Code,Another.dbo.OTHER_VIEW.Label as SpecialCodeFROM dbo.VIEW_ALL LEFT OUTER JOINAnother.dbo.OTHER_VIEW ON(dbo.VIEW_ALL.Code + '_0') = Another.dbo.OTHER_VIEW.LabelWhen I run :select * from VIEW1 where code = 'abcde',I would get all 10 rows that are of the same data where the code is'abcde'. But what I thought it should return is really just one rowsince the rest of the 9 rows are exactly the same! I know that I canget my result if I useselect DISTINCT * from VIEW1 where code = 'abcde'But, is there any way I could change my SQL statements(CREATE VIEW)above so that I automatically get 1 row instead of 10 rows of exactlythe same value without using DISTINCT?Thank you for any help.
View 7 Replies
View Related
Jan 8, 2008
I have a table in my program which going all the invoices,and I want to create a view where I can see the quantity of the products as many times by 1 as the quantity in the invoice:
Invoice:
Product name quantity
woods 3
at the view
woods 1
woods 1
woods 1
Thank you.
View 4 Replies
View Related
Aug 2, 2006
how to create view in sql server 2005 that recognize Identity And Primary key of base table
View 2 Replies
View Related
Aug 14, 2007
Good afternoon,
I have a user that needs read only access to all tables in a particular database - Which is working fine.
He also needs to create views, as well as the above, but nothing else.
Is this possible?
Many thanks.
View 1 Replies
View Related
Mar 29, 2006
CREATE VIEW getUsedColumns AS SELECT * FROM (SELECT DISTINCT attr FROM iba UNION SELECT DISTINCT attr FROM new_iba) WHERE attr != 0;
its working with oracle
how to chenge in t-sql
thanx
View 5 Replies
View Related
Jul 8, 2006
Hey guys, im really new ASP.net. I just upsized my access database to SQL Server Express and I'm trying to mimic some view functionalitty I had in access. I think they call it the expression builder.So bassically Im trying to make a column that does some calculations on other columns. Like:if columnA-columnB > 100 then columnC='YES' else columnC='NO'Does anyone know how to do this? I want to add this column in a view. Can i perhaps just do it in the datagrid? I'm so frusterated at the moment, ive been trying for about 3 hours!Thanks in advance for your help.
View 1 Replies
View Related
Sep 17, 2007
I need to create view from the results of executing dynamic SQL.Query like this have incorrect syntax: CREATE VIEW vModuleCatalogObjectValues AS EXECUTE sp_executesql @sql, @paramDefinition, @moduleId; ENDWhat i need to do to get it work?!
View 1 Replies
View Related
Jul 16, 2001
Hi,
Is there a way you can give a user a permission to "Create View" only but not to create other objects like tables or SP's in a database.
Thanks
View 1 Replies
View Related
Feb 6, 1999
Hi,
I tried to create a view on following 4 tables with two different syntaxes. One with ANSI syntax and other with conventional(using WHERE clause). Both SELECT work fine and return same result sets. Views are created in both cases. SELECT from Conventional_View works fine But SELECT from the ANSI_View returns error message without Error Number as :
'DB-Library Process Dead - Connection Broken'
CREATE TABLE dbo.SILT01 (
SILF01A varchar (8) NOT NULL ,
SILF01B varchar (25) NULL ,
SILF01C smallint NULL ,
CONSTRAINT SILT01_PK PRIMARY KEY CLUSTERED
(SILF01A)
)
GO
CREATE TABLE dbo.SILT19 (
SILF23A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT19_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF01A
)
)
GO
CREATE TABLE dbo.SILT20 (
SILF23A varchar (8) NOT NULL ,
SILF15A varchar (8) NOT NULL ,
SILF01A varchar (8) NOT NULL ,
CONSTRAINT aaaaaSILT20_PK PRIMARY KEY CLUSTERED
(
SILF23A,
SILF15A,
SILF01A
)
)
GO
CREATE TABLE dbo.SILT23 (
SILF23A varchar (8) NOT NULL ,
SILF23B varchar (30) NULL ,
SILF23C varchar (40) NULL ,
SILF23D varchar (8) NULL ,
SILF23E varchar (1) NULL ,
SILF23F text NULL ,
SILF23G varchar (25) NULL ,
SILF23H varchar (8) NULL ,
SILF23I varchar (25) NULL ,
SILF23J varchar (25) NULL ,
SILF23K text NULL ,
SILF23L varchar (25) NULL ,
SILF23M varchar (8) NULL ,
SILF23N varchar (25) NULL ,
SILF23O varchar (25) NULL ,
SILF23P text NULL ,
SILF23Q varchar (25) NULL ,
SILF23R varchar (8) NULL ,
SILF23S varchar (25) NULL ,
SILF23T varchar (25) NULL ,
SILF23U varchar (15) NULL ,
SILF23V varchar (15) NULL ,
SILF23W varchar (15) NULL ,
SILF23X varchar (15) NULL ,
SILF23Y varchar (15) NULL ,
SILF23Z varchar (15) NULL ,
SILF23A1 varchar (15) NULL ,
SILF23A2 varchar (15) NULL ,
SILF23A3 varchar (15) NULL ,
SILF23A4 varchar (15) NULL ,
SILF23A5 varchar (15) NULL ,
SILF23A6 varchar (3) NULL ,
CONSTRAINT aaaaaSILT23_PK PRIMARY KEY CLUSTERED
(
SILF23A
)
)
GO
create view ANSI_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM ((SILT01 INNER JOIN SILT19 ON SILT01.SILF01A = SILT19.SILF01A)
INNER JOIN SILT23 ON SILT19.SILF23A = SILT23.SILF23A)
LEFT JOIN SILT20 ON (SILT19.SILF01A = SILT20.SILF01A) AND
(SILT19.SILF23A = SILT20.SILF23A)
create view Conventional_View as
SELECT DISTINCT SILT01.SILF01A, SILT23.SILF23A,
SILT23.SILF23B, SILT20.SILF15A
FROM SILT01, SILT19,SILT23, SILT20
WHERE ( SILT01.SILF01A = SILT19.SILF01A AND
SILT19.SILF23A = SILT23.SILF23A ) AND
( ( SILT19.SILF01A *= SILT20.SILF01A) AND
(SILT19.SILF23A *= SILT20.SILF23A) )
SELECT * FROM ANSI_VIEW
'DB-Library Process Dead - Connection Broken'
SELECT * FROM Conventional_View (Works fine)
Expecting Help or Suggestions from experts.
Thanx,
Gunvant Patil( gunvantp@mail.usa.com )
View 1 Replies
View Related
Dec 1, 2004
G'day all.
I am trying to create an index on a view that joins two tables.
I get the classic error of course:
'Cannot index the view 'dbname.dbo.HJC_net'. It contains one or more disallowed constructs.'
Thing that gets me is that it all seems pretty normal stuff and I can't see what is stopping it.
Code is below and any help greatly appreciated.
CREATE VIEW dbo.HJC_net WITH SCHEMABINDING AS
SELECTt_number
FROM dbo.ticket_cancellations RIGHT OUTER JOIN
dbo.tickets ON dbo.ticket_cancellations.tc_system_ref = dbo.tickets.t_number
WHERE dbo.tickets.t_cancelled <> - 1 OR
-- Add all cancellation codes that are to be excluded from the NET view below
(dbo.ticket_cancellations.tc_cancellation_code <> 83943
AND dbo.ticket_cancellations.tc_cancellation_code <> 83946)
GO
-- Create a clustered index, it MUST be unique
CREATE UNIQUE CLUSTERED INDEX t_number_unique ON HJC_net(t_number)
View 1 Replies
View Related
