I was thinking about Luis' post about a similar question here. After thinking about this, I was left with this particular question:
What is the difference between an inline TVF that takes no arguments and a view? Do / should these optimize the same?
I'm searching the Books Online but can't (as yet) discover the difference between SQL_Latin1_General_CP1_CI_AS and Latin1_General_CI_AS. CP1 is code page 1252, but what's the 'default' if I don't specify CP1?
Thanks,
Al
You are right, I did not include the exact query since it has a wholeof joins and many where clauses in it.I did not want to make the post very hard to read, hence I simplifiedit.In the Stored proc I am using a String variable @SQLStringvarchar(2000) to hold the entire select statement, and then executingthat SQL using EXEC (@SQLString).Thus for debugging, I used Query Analyzer, and within the Analyzer I amusing the Select statement.So in my test I do not use any stored proc.so one select statement says:Select * from ( Select Top 600 * from( Select Top 2000 * from( Select Top 2000 PermitNumber, HouseNumber, OnStreetName,FromStreetName, ToStreetName, WorkStartDate as "WorkStart",tbl_Permittee.Permittee_name as PermitteeName, PermitteeNumber,PermitType_ID as "Type",InspectionDistrict,PermitStatus,IssueDate
Quote:
Hiya folks,
Having a problem with a view.
I'm using the syntax
Right('000' & Myfield,3) to 'pad' out the results.
ie if MyField contained 45 the answer should be : 045
but i'm getting the results of : 0
Stuck!! Any offers kind people??
SELECT *
FROM dbo.Objects
WHERE (UserRights & dbo.GetUserRights()) > 0)
The purpose of this view is to implement row level security. The Field, UserRights is a BigInt and holds up to 64 bits corresponding to the user or group that has a right to view this record.
The Function GetUserRights() is a function which returns an Int corresponding to the user rights of the person who is currently logged in. In a bitwise fashion, it then "ands" them together to see if the value is greater than 0. If so, the user has rights to the record.
If I hardcode a value to replace the function, the Select statement is very fast. However, if I use the function it is extremely slow. I believe this is due to the function getting run once for each row of the table, instead of just once.
Is there a way to rewrite this so that it only runs once? I could do this in a stored procedure, but I need it in a view so that I can use replace "Objects" (the table) with "vwObjects" the view.
Thanks,
Greg
Hi, Is there anyway to create a view within a Function? The code is as below. I execute the code between "BEGIN" and "END". SQL Analyzer report error that said
'CREATE VIEW' must be the first statement in a query batch.
I could make the variable constant in SELECT statement, but I'm wondering if there is a way to make CREATE VIEW as part of code piece.
CREATE Function GetCommonFailurs()
AS
BEGIN
IF OBJECT_ID(N'CommonFailures') IS NOT NULL
DROP VIEW CommonFailures
DECLARE @Run1Result as char(4), @Run2Result as char(4);
SET @Run1Result='Fail';
SET @Run2Result='Fail';
CREATE VIEW CommonFailures
AS
SELECT Run1Failures.RunID as Run1ID,
Run2Failures.RunID as Run2ID,
@Run1Result as 'Run1Result',
@Run2Result as 'Run2Result',
Run1Failures.SmartyDOTXMLFilePath as Run1SmartyFilePath,
Run2Failures.SmartyDOTXMLFilePath as Run2SmartyFilePath,
Run1Failures.SDET as SDET,
Run1Failures.CommandLine as CommandLine,
Run1Failures.OutputFilePath as OutputFilePath
FROM Run1Failures
INNER JOIN Run2Failures
ON Run1Failures.TestID = Run2Failures.TestID
END
I have the following data in a table:
Item Qty
1 1
1 -1
2 3
2 -1
2 -1
Using a function, how can I code it so that I get the following:
Item Qty
1 0
2 1
Also, if a Qty does equal 0 (as in item 1), can I use a >0 in the Criteria field of the resultant expression to remove it from the Function results?
what is more efficient between a table-value function or a View
View 3 Replies View RelatedIs there a way in 2005 to script out, directly, a funciton/SP/View or whatever and not have it binary? I used to be able to script something out in 2000 and it would be text and then I could check it into sourcesafe NP and as you know text is much easier to use the compair feature against than binary. Is there a setting in SQL 2005 to handle this? Is it something in Sourcesafe that I need to set?
Thanks,
Phil
Hello. I'm a real newbie - using Access 2003 front end and connecting to SQL Server 2005 ODBC.
I'm having trouble accessing functions through access. I've built the following function:
CREATE FUNCTION fnSTR_LEASESTATUS(@TRS nvarchar(12))
RETURNS TABLE
AS
RETURN
(
SELECT dbo.tblTRACT.STR, dbo.tblTRACT.[TRACT_#], dbo.tblMIN_OWNERS.Min_Owner_Name AS [OWNER OF RECORD], dbo.tblLEASE_TRACTS.LOC_ID, dbo.tblLOCATION.LPR_No, dbo.tblLOCATION.Lease_ID, dbo.tblLEASE_LOG.Date_Mailed, dbo.tblLEASE_LOG.Scan_Lease_Received, dbo.tblLEASE_LOG.Orig_Lease_Recd, dbo.tblLPR_INVOICES.Invoice_No, dbo.tblLPR_PAY.CHECK_DRAFT_No, dbo.tblLESSORS.Name AS [Lease Name]
FROM dbo.tblTRACT LEFT JOIN ((dbo.tblMIN_OWNERS RIGHT JOIN dbo.tblTRACT_OWNER ON dbo.tblMIN_OWNERS.Min_Owner_ID = dbo.tblTRACT_OWNER.Owner_Lease) LEFT JOIN ((((((dbo.tblLPR RIGHT JOIN dbo.tblLOCATION ON dbo.tblLPR.LPR_No = dbo.tblLOCATION.LPR_No) LEFT JOIN dbo.tblLESSORS ON dbo.tblLPR.Lessor_Number = dbo.tblLESSORS.Lessor_Number) RIGHT JOIN dbo.tblLEASE_TRACTS ON dbo.tblLOCATION.LOC_ID = dbo.tblLEASE_TRACTS.LOC_ID) LEFT JOIN dbo.tblLEASE_LOG ON dbo.tblLPR.LPR_No = dbo.tblLEASE_LOG.LPR_No) LEFT JOIN dbo.tblLPR_INVOICES ON dbo.tblLPR.LPR_No = dbo.tblLPR_INVOICES.LPR_No) LEFT JOIN dbo.tblLPR_PAY ON dbo.tblLPR.LPR_No = dbo.tblLPR_PAY.LPR_No) ON dbo.tblTRACT_OWNER.TRACT__Owner_ID = dbo.tblLEASE_TRACTS.Tract_Owner_Id) ON (dbo.tblTRACT.[TRACT_#] = dbo.tblTRACT_OWNER.[TRACT_#]) AND (dbo.tblTRACT.STR = dbo.tblTRACT_OWNER.STR)
WHERE (((dbo.tblTRACT.STR)=@TRS))
)
GO
I understand now I can create a view of the function Simply by using the function name in my FROM statement. However I get an error that arguments provided do not match parameters required. However, I'm not getting the prompt to enter my criterion. Is my error in my function statement? I can't save the view. I also understand I could use a pass-through query. Is there some sort of guidance or tutorial on that to which you could point me?
Thanks for your time.
I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
I'm very new to SQL Server. Please help. I need to create a FUNCTION that creates a view. Then call this function in a SQL which is passed as a parameter to BCP. In Oracle, it would be something like:
create function CREATEVIEW
return number as
begin
create view SampleView as SELECT a,b,c from Mytable;
return 1;
when others then return 0; -- for exception handling
end:
create function DROPVIEW
return number as
begin
Drop view SampleView;
return 1;
when others then return 0; -- for exception handling
end:
Then my BCP will have something like:
BCP "select CREATEVIEW from dual"... QUERYOUT ..
then
BCP "select * from SampleView"... QUERYOUT ..
then drop the view again:
BCP "select DROPVIEW from dual"... QUERYOUT ..
I know there is no DUAL table in SQL SERVER. I just want to know how to code this in SQL Server.
The origin of my problem is that my SQL statement is too long to fit as BCP parameter, hence, am creating a view and reading there and dropping it again. If you can provide me with a better workaround, that would be great.
Thanks in advance.
I have created the following view:
Create view vwOrderItemTotal2
AS
SELECT ItemName, fkMenuItemID, Sum(Quantity) as [SumOfMenuITems] FROM OrderItems GROUP BY fkMenuItemId, ItemName
When I present my data in a GridView, it works fine. For example, several orders for milk are returned as a summary quantity of 26 gallons in a single row of the GridView like this:
26 Milk
Now I need to filter my data by OrderDate and Zipcode. I created this new view:
Create view vwOrderItemTotal5
AS
SELECT Orders.Zipcode, Orders.OrderDate, OrderItems.ItemName, OrderItems.fkMenuItemID, Sum(Quantity) as [SumOfMenuITems]
FROM Orders INNER JOIN OrderItems
ON Orders.OrderID = OrderItems.fkOrderID
GROUP BY fkMenuItemId, ItemName, Zipcode, OrderDate
When I present my data in a Gridview using the new view I get a GridView with multiple rows for milk where each order has its own row like this:
1 Milk
5 Milk
6 Milk
6 Milk
3 Milk
1 Milk
4 Milk
But I want the data presentation in one row for each ItemName (e.g. Milk) as with my first view. Can I adjust my new view to achieve this, or should I stick with my first view (vwOrderItemTotal2) and adjust the Select Command in my SqlDataSource (hasn’t worked yet).
I think that what I want is for the returned data to be grouped by fkMenuItemId only, but the sql server admin won’t let me create a view without including the other fields in the Group By clause. Thanks for any help provided in solving this.
I'm a developer, not a DB admin. I'm writing a .NET app that usescrystal reports.The table I need to output is built inside a stored procedure. Nochoice, it makes use of some temporary tables. (Believe me I've triedto get around that.)Crystal reports seems to only know about tables and views. It lookslike a view cannot call procedures. It can call functions, but in turnthey also can't call procedures. I am hosed, what now?Performance is not a factor here, small data sets, I just gotta get thething working.
View 5 Replies View RelatedI have an Access 2000 MDB file with a SQL 7 back end. I have a main tablewith 50,000 records; and I have a selections table with 50,000 records foreach machine that uses the database (about 25-50). This allows each user tohave their own set of selections.The selections table has three fields: ID (int), Sel (bit), MachName(varchar). ID and MachName comprise the primary key.I have a view that combines the main table and the entries for theselections table for the current machine (SQL below). The view works finewhen opened in EM and QA. And if I create a pass-through query from myAccess MDB file, the results are displayed fine.However, if I link the view to the Access MDB file, I get "#Deleted" inevery field of every record (which seems to indicate that the records werethere and then they were gone). However, if I hard-code the machine nameinto the same view instead of using HOST_NAME and then relink the view tothe MDB file, the linked view opens fine. Only when I use HOST_NAME as aparameter in the view is there a problem with it.Anyone have any idea what's going on here, or have heard of any issues withHOST_NAME and ODBC linked objects? SQL for the view is below.Thanks!NeilSELECT INVTRY.*, InvtrySelections.Sel, InvtrySelections.MachNameFROM dbo.INVTRY INNER JOINdbo.InvtrySelections ONdbo.INVTRY.ID = dbo.InvtrySelections.IDWHERE (dbo.InvtrySelections.MachName = HOST_NAME())
View 20 Replies View RelatedI have some issue when replicating a view (transactional replication).I have following objects on publisher:
Table: [Metadataschema].[Entity]
View: [dbo].[EntityLogicalView]
the view definition is:
create view [dbo].[EntityLogicalView] as (SELECT * FROM [Entity] WHERE OverwriteTime = 0)
this is what I get when I run "Script View as Create". The same query is used by the replication.Unfortunately this query fails on the subscriber "Unable to replicate a view or function because the referenced objects or columns are not present on the Subscriber". I cannot even runt his query manually on the subscriber, it only works when I change it like this:create view [dbo].[EntityLogicalView] as (SELECT * FROM [Metadataschema].[Entity] WHERE OverwriteTime = 0) Is there any way to get this working? Can I change the creation scripts used by the replication or force publisher to include schema names in all objects?
I have a function that accespts a string and a delimeter returns the results in a temp table. I am using the funtion for one of the columns in my view that needs be to split and display the column into different columns. The view takes for ever to run and finally it doesn't split and doesn't display in the column.
Function:
-----------------------------------
ALTER FUNCTION [dbo].[func_Split]
(
@DelimitedString varchar(8000),
[Code].....
Not sure what I am missing in the above view why it doesn't split the string.
I have a problem (who not?) with a function which i'm using in a view.This function is a function which calculates a integer value of adate. For example: '12/31/2004 00:00:00" becomes 20041231. This isvery handy in a datawarehouse and performes superfast. But here is myproblem.My calendar table is limited by a couple of years. What happens isthat sometimes a value is loaded which is not in the range of theCalendardate. What we want to do is when a date is loaded is that thisfunction insert a minimum date when date < minimum date and a maximumdate when date > maximum date.Yes i know you're thinking : This is datamanipulation and yes this istrue. But now we loose information in our cubes and reports by innerjoining. So if we can use a minimum and a maximum than a user wouldsay: "This is strange, a lot of values on 1980/1/1!" instead of "Ithink that i have not all the data!"GreetzHennie
View 2 Replies View RelatedHey,First, sorry if this post appear twice, because, I can not find my postrecently send, trying to post it once again.I'm out of ideas, so, I thought, will search help here again :(I'm trying to prepare a view for ext. app. This is in normal cases veryeasy, but what if the view structure should be dynamic?!Here is my point (I will siplify the examples).I have a table:create table t_data (id bigint identity (1,1) not null,valvarchar(10) not null,data varchar(100) not nullconstraint [PK_t_data] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_data (val, data) values('1111111111','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('2222222222','1234567890abcdefghijklmnoprstuvwxyz 1234567890abcdefghijklmnoprstuvwxyz12345abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('3333333333','12345abcdefghijklmnoprstuvwxyz12345 67890abcdefghijklmnoprstuvwxyz1234567890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('4444444444','67890abcdefghijklmnoprstuvwxyz12345 67890abcdefg12345hijklmnoprstuvwxyz67890abcdefghij klmnoprstuvwxyz')insert into t_data (val, data) values('5555555555','1230abcdefghijklmnoprst12345uvwxyz1 234567890abcdefghijklmnoprstuvwxyz67890abcdefghijk lmnoprstuvwxyz')gocreate table t_dataVal (id bigint identity (1,1) not null,valvarchar(10) not null,fill varchar(4) not nullconstraint [PK_t_dataVal] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_dataVal (val, fill) values ('1111111111','AAAA')insert into t_dataVal (val, fill) values ('2222222222','KKKK')insert into t_dataVal (val, fill) values ('3333333333','DDDD')insert into t_dataVal (val, fill) values ('4444444444','ZZZZ')insert into t_dataVal (val, fill) values ('5555555555','CCCC')gocreate table t_conf (id bigint identity (1,1) not null,start int not null,length int not null,description varchar(20) not null,constraint [PK_t_conf] primary key clustered(id) with fillfactor = 90 on [PRIMARY] )goinsert into t_conf (start, length, description) values (1,10,'value_1')insert into t_conf (start, length, description) values (11,3,'value_2')insert into t_conf (start, length, description) values(55,15,'value_3')insert into t_conf (start, length, description) values (33,2,'value_4')insert into t_conf (start, length, description) values (88,1,'value_5')insert into t_conf (start, length, description) values (56,7,'value_6')goNow here is the issue:table t_conf contain data, which can be modified by user. The user isseting the appropriate values.Now, there should be a view, which returns:- as headers (collumn names) this what is defined in description columnof t_conf (for example: value_1, value_2 ... value_6)- as values, substrings of all data from t_data, cutted with start andlength values for appropriate decription from t_conf.- first two columns of view, should be column val and fill of t_dataValtableSo the effect should be like this:valfillvalue_1value_2value_3value_4value_5value61111111111AAAA1234567890abc....2222222222KKKK1234567890abc....3333333333DDDD12345abcdefgh....4444444444ZZZZ67890abcdefgh....5555555555CCCC1230abcdefghi....of course, for all other value_x should be the appropriate substringsshown.Sounds simple, hm?Well, I'm trying to do this, since yesterday evening, and can not :(In real life, the call of view/function might happend a lot.The table t_data might have around 4000 records, but the data string islonger (around 3000 characters).Application, might acess a udf, which returns table, and I was focusingin that.Was trying, to create local temp table in function, to insert values,using cursor over t_conf.Unfortunately, everything what I get, is just a vertical representationof the data, and I need it horizontal :(The other problem in function is, that I can not use exec() (wll known)so I can not even create a table,dynamicly, using as column names description value from table t_conf,and as size of field length from this table.Sorry, that the description is maybe not exactly for my problem, butthis is because I'm not even sure, which way to use :(any help will be appreciated!Thank You - Matik
View 1 Replies View Relatedhelp with solution for use order BY inside function and VIEW on the sql server
i explain the problem
i am a web developer in asp page
i BUY an DATA grid object for my web site
the problem in this object i can use only ONE field to use ORDER BY
can i store in the sql server function and VIEW that change the ORDER of the result inside the sql server ?
so whan i do
SELECT *
FROM dbo.tb_pivot_big
the sql server ORDER for ME the result and run this (inside the sql server )
Code Snippet
SELECT *
FROM dbo.tb_pivot_big
ORDER BY new_unit, Fname ASC, val_orginal desc
TNX for help
Because of an error in google or underlying site i can reply on my ownissue. Therefore i copied the former entered message in this message.-------------------------------------REPY----------------------------------Hi Maybe i wasn't clear. I want to dynamically check whether what thelowest date and the highest date is in the calendar table. Thepresented solutions has fixed dates and i don't want that.If i could store a global variable in SQL server (dynamic properties?)then it would be great. Fill this once and call it multiple times inmy intensively used function. Is this possible?GreetzHennie----------------------------Previously enteredissue-----------------------I have a problem (who not?) with a function which i'm using in a view.This function is a function which calculates a integer value of adate. For example: '12/31/2004 00:00:00" becomes 20041231. This isvery handy in a datawarehouse and performes superfast. But here is myproblem.My calendar table is limited by a couple of years. What happens isthat sometimes a value is loaded which is not in the range of theCalendardate. What we want to do is when a date is loaded is that thisfunction insert a minimum date when date < minimum date and a maximumdate when date > maximum date.Yes i know you're thinking : This is datamanipulation and yes this istrue. But now we loose information in our cubes and reports by innerjoining. So if we can use a minimum and a maximum than a user wouldsay: "This is strange, a lot of values on 1980/1/1!" instead of "Ithink that i have not all the data!"GreetzHenniePlaats een reactie op dit berichtBericht 2 van deze discussieVan:John Bell (jbellnewsposts@hotmail.com)Onderwerp:Re: Intensively used function in view needs a minimum andmaximum from a tableView this article onlyDiscussies:comp.databases.ms-sqlserverDatum:2004-12-30 03:56:25 PSTHiIf you LEFT or RIGHT JOIN to the calendar table you will get a NULLvaluefor the column, you can then is CASE to determine the valueCREATE FUNCTION ConvertDate (@datevalue datetime)RETURNS INTASBEGINDECLARE @dateint INTSELECT @dateint = CAST( CASE WHEN A.Date < '20030101' THEN '19800101'WHEN A.Date > '20051231' THEN '99991231'ELSE CONVERT(CHAR(4),C.[Year]) + RIGHT('0'+CONVERT(VARCHAR(2),C.[Month]),2) + RIGHT('0'+CONVERT(VARCHAR(2),C.[Day]),2)END AS INT )FROM ( SELECT @datevalue AS [Date] ) ALEFT JOIN CALENDAR C ON C.[Date] = A.[Date]RETURN @dateintENDGOJohn"Hennie de Nooijer" <hdenooijer@hotmail.com> wrote in messagenews:191115aa.0412300238.7dee0f85@posting.google.c om...[color=blue]>I have a problem (who not?) with a function which i'm using in a[/color]view.[color=blue]> This function is a function which calculates a integer value of a> date. For example: '12/31/2004 00:00:00" becomes 20041231. This is> very handy in a datawarehouse and performes superfast. But here is my> problem.>> My calendar table is limited by a couple of years. What happens is> that sometimes a value is loaded which is not in the range of the> Calendardate. What we want to do is when a date is loaded is that this> function insert a minimum date when date < minimum date and a maximum> date when date > maximum date.>> Yes i know you're thinking : This is datamanipulation and yes this is> true. But now we loose information in our cubes and reports by inner> joining. So if we can use a minimum and a maximum than a user would> say: "This is strange, a lot of values on 1980/1/1!" instead of "I> think that i have not all the data!">> Greetz>> Hennie[/color]Plaats een reactie op dit berichtBericht 3 van deze discussieVan:Hugo Kornelis (hugo@pe_NO_rFact.in_SPAM_fo)Onderwerp:Re: Intensively used function in view needs a minimum andmaximum from a tableView this article onlyDiscussies:comp.databases.ms-sqlserverDatum:2004-12-30 15:32:06 PSTOn 30 Dec 2004 02:38:51 -0800, Hennie de Nooijer wrote:[color=blue]>I have a problem (who not?) with a function which i'm using in a[/color]view.[color=blue]>This function is a function which calculates a integer value of a>date. For example: '12/31/2004 00:00:00" becomes 20041231. This is>very handy in a datawarehouse and performes superfast. But here is my>problem.[/color](snip)Hi Hennie,Is this conversion all that your function does? If so, you might wanttotry the following alternative (using CURRENT_TIMESTAMP as example;replaceit with your date column / parameter):SELECT CAST(CONVERT(varchar, CURRENT_TIMESTAMP, 112) AS int)You could put this in the UDF (probably at least as fast as yourcurrentCalenmdar-table based function), or use it inline as a replacement tothefunction call (probably even faster).It should work for all dates from Jan 1st 1753 through Dec 31st 9999.Best, Hugo--(Remove _NO_ and _SPAM_ to get my e-mail address)
View 2 Replies View RelatedHow Do I fix View(below) or Multi-Table select(below) to use this Function to return distinct rows via qcParent_ID?
Following Function populates a field (with concat list of related titles) with other required fields:
Create Function [dbo].openItemsIntoList(@Delimeter varchar(15),@qcparent_ID varchar(1000))
Returns Varchar(8000) as
Begin
Declare @Lists as varchar(8000);
Select @Lists = '';
Select @Lists = @Lists + itemTitle + @Delimeter From z_QClocate_openAll_Qualifier
Where @qcParent_ID = qcParent_ID;
Return Substring(@Lists,1,len(@Lists)-len(@Delimeter));
End
works perfect against single table select (returning 54 distinct rows by qcParent_ID):
Select a.qcParent_ID, a.Facility, a.Modality, openItemListToFix
From dbo.a2_qcEntryForm a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
But data is needed from 3 tables...
- Created a VIEW that returns all (82) rows (negating distinct of the function on qcParent_ID)
- Failed Miserably Integrating Function call into a multi-table select (inexperienced with complex joins)
This VIEW returns ALL (82) rows in table:
CREATE VIEW z_QClocate_openAll AS
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
Calling like this returns ALL 82 rows (negating the functions distinct):
Select a.qcParent_ID, a.qcStatus, a.qcAlert, a.itemComplete, a.Facility, a.Modality, openItemListToFix
From z_QClocate_openAll a
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
AND THEN THERES...
Failing miserably on Integrating the Function call into This SELECT ON MULTI-TABLES:
How to integrate the Function call:
JOIN (Select DISTINCT qcParent_ID, dbo.openItemsIntoList(' / AND ',qcParent_ID) as openItemListToFix FROM dbo.a3_qcItems2Fix) i
on a.qcParent_ID = i.qcParent_ID
into the multi-table Select relationships (while maintaining Where & Order By):
SELECT dbo.a1_qcParent.qcStatus, dbo.a1_qcParent.qcAlert, dbo.a3_qcItems2Fix.qcParent_ID, dbo.a3_qcItems2Fix.qcEntryForm_ID,
dbo.a3_qcItems2Fix.itemComplete, dbo.a3_qcItems2Fix.itemTitle, dbo.a2_qcEntryForm.Facility, dbo.a2_qcEntryForm.Modality
FROM dbo.a1_qcParent INNER JOIN
dbo.a2_qcEntryForm ON dbo.a1_qcParent.qcParent_ID = dbo.a2_qcEntryForm.qcParent_ID INNER JOIN
dbo.a3_qcItems2Fix ON dbo.a2_qcEntryForm.qcEntryForm_ID = dbo.a3_qcItems2Fix.qcEntryForm_ID AND
dbo.a1_qcParent.qcParent_ID = dbo.a3_qcItems2Fix.qcParent_ID
WHERE (dbo.a1_qcParent.qcStatus = 'Awaiting Attn') AND (dbo.a3_qcItems2Fix.itemComplete = 0) OR
(dbo.a1_qcParent.qcStatus = 'In Process') OR
(dbo.a1_qcParent.qcStatus = 'Re-Opened')
i created a query and when i run it like this i get data but when i add a value in the 2ed case for '2%' i get error.
Select a.email, case when a.reportnumber like '1%' then (select b.Reportnumber from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Reportnumber, case when a.Reportnumber like '1%' then (select b.stonebreakdown from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as Measurement, case when a.Reportnumber like '1%' then (select b.reportcarddate from ijasSummaryNo b where a.Reportnumber = b.Reportnumber) end as ijasDate,
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo
from t_RegisterInfoTemp a
Query works fine like this but when i add this (the one marked bold i get error)
case when a.reportnumber like '2%' then (select c.Reportnumber from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as imacsRepNo,case when a.reportnumber like '2%' then (select c.Measurement from appraisalsummaryblue c where a.reportnumber = c.reportnumber) end as Measurement2
This is the error.
Server: Msg 4414, Level 16, State 1, Line 1Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.
I have read that I need to add an instead of trigger. I have no idea how to do this. I am basicaly trying to update at the moment but will also need to insert later.
From the code in VWD2008 I only get the error when i include this info here: When i press update and these below are included in the update statement then i get the error.
[S_DATE] = @S_DATE, [END[IS_CONFIRMED] = @IS_CONFIRMED, [IS_PAID] = @IS_PAID, [S_Descript] = @S_DESCRIPT[COMPANY] = @COMPANY[MONTH] = @MONTH[ACCOUNT] = @ACCOUNT
Here is view from SQL 2000.
CREATE VIEW dbo.VIEW_TRAINING
AS
SELECT dbo.ADDRESS.EMAIL, dbo.ADDRESS.FIRST_NAME AS [first name], dbo.ADDRESS.LAST_NAME AS [last name], dbo.ADDRESS.STATE,
dbo.ADDRESS.TEL1 AS phone, dbo.CUST.NAME AS Company, dbo.ITEMS.DESCRIPT AS S_Descript, dbo.ADDRESS.JOB_TITLE AS Job_Title,
dbo.TRAINING_SCHEDULE.[MONTH], dbo.TRAINING_SCHEDULE.S_DATE, dbo.TRAINING_SCHEDULE.END_DATE,
dbo.TRAINING_SCHEDULE.IS_CONFIRMED, dbo.TRAINING_SCHEDULE.IS_PAID, dbo.TRAINING_SCHEDULE.CUST_CODE AS Account,
dbo.TRAINING_SCHEDULE.SCHEDULE_ID
FROM dbo.TRAINING_SCHEDULE INNER JOIN
dbo.CUST ON dbo.TRAINING_SCHEDULE.CUST_CODE = dbo.CUST.CUST_CODE RIGHT OUTER JOIN
dbo.X_INVOIC RIGHT OUTER JOIN
dbo.INVOICES ON dbo.X_INVOIC.ORDER_NO = dbo.INVOICES.DOC_NO LEFT OUTER JOIN
dbo.ADDRESS ON dbo.INVOICES.CUST_CODE = dbo.ADDRESS.CUST_CODE LEFT OUTER JOIN
dbo.ITEMS ON dbo.ITEMS.ITEMNO = dbo.X_INVOIC.ITEM_CODE ON dbo.CUST.CUST_CODE = dbo.ADDRESS.CUST_CODE
WHERE (dbo.X_INVOIC.ITEM_CODE LIKE 'FOT-%') AND (dbo.X_INVOIC.STATUS = 7) AND (dbo.ADDRESS.TYPE IN (4, 5, 6)) AND (dbo.ADDRESS.EMAIL <> '') AND
(dbo.ADDRESS.COUNTRY = 'UNITED STATES') AND (dbo.ITEMS.CATEGORY = 'TRAININGCLASSES') AND
(dbo.TRAINING_SCHEDULE.CUST_CODE = 'steve')
Here is the forms code I am working with: used VWD to generate the forms...I added update...<%@ Page Language="C#" AutoEventWireup="true" CodeFile="training.aspx.cs" Inherits="training" TRACE = TRUE%>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
</div>
<asp:FormView ID="FormView1" runat="server" DataSourceID="dstraining"
DefaultMode="Edit" AllowPaging="True">
<EditItemTemplate>
EMAIL:
<asp:TextBox ID="EMAILTextBox" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
<br />
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel1" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
<br />
<asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True"
CommandName="Update" Text="Update" />
<asp:LinkButton ID="UpdateCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</EditItemTemplate>
<InsertItemTemplate>
EMAIL:
<asp:TextBox ID="EMAILTextBox" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:TextBox ID="first_nameTextBox" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:TextBox ID="last_nameTextBox" runat="server"
Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:TextBox ID="STATETextBox" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:TextBox ID="phoneTextBox" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:TextBox ID="CompanyTextBox" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:TextBox ID="S_DescriptTextBox" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:TextBox ID="Job_TitleTextBox" runat="server"
Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:TextBox ID="MONTHTextBox" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:TextBox ID="S_DATETextBox" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:TextBox ID="END_DATETextBox" runat="server"
Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:TextBox ID="IS_CONFIRMEDTextBox" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:TextBox ID="IS_PAIDTextBox" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:TextBox ID="AccountTextBox" runat="server" Text='<%# Bind("Account") %>' />
<br />
<asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True"
CommandName="Insert" Text="Insert" />
<asp:LinkButton ID="InsertCancelButton" runat="server"
CausesValidation="False" CommandName="Cancel" Text="Cancel" />
</InsertItemTemplate>
<ItemTemplate>
EMAIL:
<asp:Label ID="EMAILLabel" runat="server" Text='<%# Bind("EMAIL") %>' />
<br />
first_name:
<asp:Label ID="first_nameLabel" runat="server"
Text='<%# Bind("first_name") %>' />
<br />
last_name:
<asp:Label ID="last_nameLabel" runat="server" Text='<%# Bind("last_name") %>' />
<br />
STATE:
<asp:Label ID="STATELabel" runat="server" Text='<%# Bind("STATE") %>' />
<br />
phone:
<asp:Label ID="phoneLabel" runat="server" Text='<%# Bind("phone") %>' />
<br />
Company:
<asp:Label ID="CompanyLabel" runat="server" Text='<%# Bind("Company") %>' />
<br />
S_Descript:
<asp:Label ID="S_DescriptLabel" runat="server"
Text='<%# Bind("S_Descript") %>' />
<br />
Job_Title:
<asp:Label ID="Job_TitleLabel" runat="server" Text='<%# Bind("Job_Title") %>' />
<br />
MONTH:
<asp:Label ID="MONTHLabel" runat="server" Text='<%# Bind("MONTH") %>' />
<br />
S_DATE:
<asp:Label ID="S_DATELabel" runat="server" Text='<%# Bind("S_DATE") %>' />
<br />
END_DATE:
<asp:Label ID="END_DATELabel" runat="server" Text='<%# Bind("END_DATE") %>' />
<br />
IS_CONFIRMED:
<asp:Label ID="IS_CONFIRMEDLabel" runat="server"
Text='<%# Bind("IS_CONFIRMED") %>' />
<br />
IS_PAID:
<asp:Label ID="IS_PAIDLabel" runat="server" Text='<%# Bind("IS_PAID") %>' />
<br />
Account:
<asp:Label ID="AccountLabel" runat="server" Text='<%# Bind("Account") %>' />
<br />
SCHEDULE_ID:
<asp:Label ID="SCHEDULE_IDLabel" runat="server"
Text='<%# Eval("SCHEDULE_ID") %>' />
<br />
</ItemTemplate>
</asp:FormView>
<asp:SqlDataSource ID="dstraining" runat="server"
ConnectionString="<%$ ConnectionStrings:EVEREST_FIBEROPTConnectionString %>"
SelectCommand="SELECT , [first name] AS first_name, [last name] AS last_name, [STATE], [phone], [Company], [S_Descript], [Job_Title] AS Job_Title, [MONTH], [S_DATE], [END_DATE], [IS_CONFIRMED], [IS_PAID], [Account], [SCHEDULE_ID] FROM [VIEW_TRAINING]"
UpdateCommand="UPDATE view_training SET email=@email, [FIRST NAME] = @FIRST_NAME, [LAST NAME] = @LAST_NAME, [STATE] = @STATE, [PHONE] = @PHONE, [JOB_TITLE] = @JOB_TITLE, [COMPANY] = @COMPANY WHERE Account = @Account">
</asp:SqlDataSource>
</form>
</body>
</html>
View or function 'MutualFund' is not updatable because the modification affects multiple base tables.
I get the above error when i try to delete rows from a view.
i tried the following code with a table. it works perfectly. i want the same behaviour for a view.
can anyone help.
Delete MutualFund
from MutualFund inner join MutualFund_history
on MutualFund.SecurityID= MutualFund_history.SecurityIDwhere MutualFund.MorningstarCategoryID=MutualFund_history.MorningstarCategoryID and
MutualFund.MorningstarAssetClassID= MutualFund_history.MorningstarAssetClassID and
MutualFund_history.insertdt =(Select MAX(DsegLastUpdated) from Timestamp_History(nolock))
"MutualFund" is a view.
if it were a table, the above works well. but how can i simulate the same effect for a view.
any help will be greatly appreciated.
thanks
Hi all,
I have writen a Function which call's the same function it self. I'm getting the error as below.
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
Can any one give me a solution for this problem I have attached the function also.
CREATE FUNCTION dbo.GetLegsFor(@IncludeParent bit, @EmployeeID float)
RETURNS @retFindReports TABLE (EmployeeID float, Name nvarchar(255), BossID float)
AS
BEGIN
IF (@IncludeParent=1)
BEGIN
INSERT INTO @retFindReports SELECT MemberId,Name,referredby FROM Amemberinfo WHERE Memberid=@EmployeeID
END
DECLARE @Report_ID float, @Report_Name nvarchar(255), @Report_BossID float
DECLARE RetrieveReports CURSOR STATIC LOCAL FOR
SELECT MemberId,Name,referredby FROM Amemberinfo WHERE referredby=@EmployeeID
OPEN RetrieveReports
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
WHILE (@@FETCH_STATUS = 0)
BEGIN
INSERT INTO @retFindReports SELECT * FROM dbo.GetLegsFor(0,@Report_ID)
INSERT INTO @retFindReports VALUES(@Report_ID,@Report_Name, @Report_BossID)
FETCH NEXT FROM RetrieveReports INTO @Report_ID, @Report_Name, @Report_BossID
END
CLOSE RetrieveReports
DEALLOCATE RetrieveReports
RETURN
END
Hello,
I am running this query
"delete from ims_domains where id=61"
and got the error
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32)
Please let me know what should be the reason?
Thanks,
Ravi
Hi,
I face this error when i try to run my store procedure.
The sample of store procedure as following:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE sp_addUserAccess
with encryption
AS
SET NOCOUNT ON
DECLARE @COUNTER INT
SET @COUNTER = 0
DECLARE @i_compId INT
BEGIN
DECLARE C1 SCROLL CURSOR FOR
SELECT i_compId
FROM ltd_cms_company WHERE (i_owner = 176 or i_owner = 268) AND ti_recStatus = 1
END
OPEN C1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO ltd_cms_userAccess ( i_loginId, i_groupId, i_compId, ti_updComp, ti_updLog, ti_updAccess, ti_owner, ti_acctMgr, ti_updContact, ti_updEvent )
VALUES ( 124, 0, @i_compId, 1, 1, 1, 1, 1, 1, 1)
SET @COUNTER = @COUNTER + 1
FETCH ABSOLUTE @COUNTER FROM C1 INTO
@i_compId
END
CLOSE C1
DEALLOCATE C1
SET NOCOUNT OFF
anyone can help me identify this error?
Thanks
Regards,
Jojomay
Hi all, I get this message when trying to update a tabel i have whichhas nested hierarchies.The current hierarchies beginning from root = 1 are up to the level 5.Before going into details and sample data with all the sql queries andprocedures, this limitation from Microsoft for nested levels .. isthere any way or trick to increase the level in generic?
View 1 Replies View RelatedI have created a delete trigger in Table1 and Table2. Once I delete a certain record in Table1 it will also delete that record in Table2 or vice versa. But once i delete certain record either in Table1 or Table2 it will create an error "Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).". Can you help me on this?
View 4 Replies View RelatedMy goal is to select values from the same date range for a month on month view to compare values month over month. I've tried using the date trunc function but I'm not sure what the best way to attack this is. My thoughts are I need to somehow select first day of every month + interval 'x days' (but I don't know the syntax).In other words, I want to see
Select
Jan 1- 23rd
feb 1-23rd
march 1-23rd
april 1-23rd
,value
from
table
I am looking to create a constraint on a table that allows multiplenulls but all non-nulls must be unique.I found the following scripthttp://www.windowsitpro.com/Files/0.../Listing_01.txtthat works fine, but the following lineCREATE UNIQUE CLUSTERED INDEX idx1 ON v_multinulls(a)appears to use indexed views. I have run this on a version of SQLStandard edition and this line works fine. I was of the understandingthat you could only create indexed views on SQL Enterprise Edition?
View 3 Replies View RelatedWrite 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]...