I am creating a VIEW with an ORDER BY clause. When I do a straight SELECT it tells me I can only use ORDER BY in a VIEW with TOP specified. I want all rows in the Table to be selected so I tried to get the count of the table and feed it to the SELECT TOP like this (see query below) but I get an "Incorrect syntax near '@count'" error.
Any ideas ?
Thanks,
Jeff
Here's what I tried:
CREATE VIEW vwCSTProvQuery
AS
declare @count int
select @count = (select count(*) from TP)
Select TOP @count a.IDQual as 'ISA Qual', a.IDCODE as 'ISA No', a.GSID as 'GS ID', a.Name,
CASE
WHEN b.Vers LIKE '004010X096%' THEN '837I'
WHEN b.Vers LIKE '004010X097%' THEN '837D'
WHEN b.Vers LIKE '004010X098%' THEN '837P'
WHEN b.Vers LIKE '004010X091%' THEN '835'
WHEN b.Vers LIKE '004010X061%' THEN '820'
WHEN b.Vers LIKE '004010X095%' THEN '834'
ELSE 'N/A'
END as 'Tran Type'
FromTP as A,
TradStat as B
where a.custno = b.custno
andMap_Tran in ('837', '835', '820', '834')
anda.custno <> 'DMA7384'
order by a.idcode, a.GSID, b.Map_tran, b.Vers
We plan to upgrade to SQL 2000 soon. We have couple of views , which are defined with order by clause
Front end application, select from this views, On SQL 7.0 we get the sorted results as per the defined order in view's definition. But on SQL 2k , the same query on same view doesn't return sorted results.
Hi all, What's different from SELECT FROM A TABLE to SELECT FROM A VIEW? Which is faster? Can anyone explain my result following? Example for: -- TABLE: set statistics io on select * from TABLE_NAME -->Table 'TABLE_NAME'. Scan count 2, logical reads 106, physical reads 0, read-ahead reads 0
-- VIEW: Create view VIEW_NAME as SELECT * FROM TABLE_NAME set statistics io on select * from VIEW_NAME -->Table 'TABLE_NAME'. Scan count 1, logical reads 53, physical reads 0, read-ahead reads 0.
We have a view that is created by the union of a bunch of select statements. In our standard 32 bit database the sql statement 'select * from view' returns 1600 rows in about 2 seconds. This exact same view in a 64 bit system takes over 4 minutes to return data. However, if I run the sql that creates the view against the 64 bit system I get my results very quickly. Is there a known issue with selecting against views in a 64 bit database?
I've been running into more and more complexity with an application, becauseas time goes on - we need more and more high-level, rolled-up information.And so I've created views, and views that use other views.. and the queriesare getting slower and slower.This morning, I'm working on something like this:select<some columns>,"calculatedcolumn" = (select top 1 crap from stuff wherethingy='whatchamacallit')fromsomeviewnow, I realized that I need to really return "calculatedcolumn" in a coupleother places in the select like this - well, this is what I WANT to do:select<some columns>,calculatedcolumn = (select top 1 crap from stuff wherethingy='whatchamacallit'),otherfield = case SomeBitwhen 1 then calculatedcolumnelse count(somefield)end,otherfield1 = case SomeotherBitwhen 1 then calculatedcolumnelse sum(somefield)end,otherfield2 = case SomeBit2when 1 then calculatedcolumnelse avg(somefield)end,otherfield3 = case SomeBit3when 1 then calculatedcolumnelse count(somefield)end,fromsomeviewPoint is, I CAN'T do that, so I have to re-run that sub-select for EACH ofthese cases, and that is KILLING this stored procedure. It seems to me, thatif the database when and already got that field, for that row - I should beable to re-use, rather than going back out additional times.Is there a way to so this? Put simpler:selectx = (select top 1 user_id from users),bestUser=x,smartestUser=xfromUserscan I re-use "x" in that example. Thanks!
Hi, I'm developing a fresh SQL DB which is result of a deep analysis of an old Access DB. THe thing is, this old one had very complex consultations to the Access tables, and some consultations were using another consultations as way to select some specific data. THe ideia in SQL is to avoid that too, however, there are some data that may serve exactly the same to some bigger stored procedures. This way, I have three options I guess: 1. Create every stored procedure making select queries directly to the table and it's done!2. Create auxiliary stored procedures which will select the redundant data, and when it is needed, another stored procedures call this one and use its returned data. (is this possible anyway?).3. I create a view to this redundant data, and the greater depth stored procedures access this data of the view when needed. I've heard, however, that a select to a view is slower than directly to the table, once the view adds an extra query to the process.. What is your guess on this issue of mine?I'm almost sure that option 1 is the best, however, I'd love to hear from you guys your opinion on this. The greater issue above this all is just one - performance. Thanks a lot!
I am trying to create a view that encapsulates some specific info from many different tables. I have about 30 tables all with exactly the same field names and field types. I want to take 3 of the fields from every table and put them together into one 'VIEW' so I can run more efficient queries. SQL however, doesn't let you 'combine' 2 columns from different tables into one column in the view. (making sense?) I tried running a 'UNION' but you are specifically NOT allowed to run a union in a create view statement. Anyone have any ideas?
In a SQL db we have we get the following error when just doing a simple select query against the view. Msg 217, Level 16, State 1...Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).
No changes have been made to triggers or stored procedures recently and all was good prior to that.I understand that if my triggers loop this error will occur. But the select query does not fire any triggers functions or any other items. and the select worked with no issues last week.
I have the following a view on a SQL2K box that uses the following SELECT statement:
SELECT
SF.SKU, SAT.PublicationDate AS SATPubDate, SAM.PublicationDate AS SAMPubDat FROM SkuFlags SF LEFT OUTER JOIN SpringArbor_ttlsparb SAT ON SF.ISBN = SAT.ISBN LEFT OUTER JOIN SpringArbor_music SAM ON SF.ISBN = SAM.PrimaryKey WHERE ( ( ( SAT.PublicationDate IS NOT NULL ) AND ( SAT.PublicationDate <> '010001' ) AND ( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAT.PublicationDate, 2) + '/01/' + RIGHT(SAT.PublicationDate, 4) AS DATETIME) ))) ) OR ( ( SAM.PublicationDate <> '010001' ) AND ( SAM.PublicationDate IS NOT NULL ) AND ( GETDATE() <= DATEADD(day, -1, ( CAST(LEFT(SAM.PublicationDate, 2) + '/01/' + RIGHT(SAM.PublicationDate, 4) AS DATETIME))) ) )
The view works in SQL2K. When I try to run it under SQL2K5, I get a "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value." error. I know what the error is, the SAM.PublicationDate field has NULL values in it (and this is vendor supplied data that is updated frequently, so not dealing with NULL values isn't an option), so during the CAST function it's try to CAST NULL + /01/ + NULL into a DATETIME value and crashing.
My question is why this works in SQL2K and not SQL2K5?
I am creating a view and want to select records where the value of a Customer field (Klant Test Plan) is NULL or has exact the same value as for example customer field 2 (Klant Schedule).
I have already the code below:
SELECT DISTINCT TOP (100) PERCENT dbo.Product.ERPKey, dbo.TestPlan.CoA, dbo.TestMethod.WorkInstruction, dbo.Customer.Name AS [Klant Testplan], Customer_1.Name AS [Klant Schedule], LEFT(dbo.ShopFloor.ShopFloorNumber, 7) AS Schedule, CASE WHEN Customer_1.Name = 'ItsMe BV' THEN Customer.Name ELSE Customer_1.Name END AS Customer FROM dbo.Customer AS Customer_1 INNER JOIN
Dear All, i am making small web application using asp.net, C# ,sql2000. i want a about regarding how to access view or table from other server to local server. i have base database where there is a view which need to access in my database of local server.that is how to make select query to access view in other server Please help thanks
SQL 2005 9.00.3402.00 (x64) As Above really when I select * OR select a single column from the view the wrong column data is returned. in SQL Management Studio when I expand the Columns of the view it reflects the old table structure not the new table structure. I can easily fix by compiling the view again but this would mean I would have to recompile all referencing views when I make a change to table structures. I've tried various DBCC Clean Buffers & drop cache with no effect. Is there a command to recompile all views & poss stored procs in a database. Any help or explanation would be appreciated GW
I create a new user who will have a read only permission on TestDB.
I want to give only select permission on TestDB and also I don't want that the new user will not see any other database.
DENY VIEW ANY DATABASE to user_readonly
ALTER AUTHORIZATION ON DATABASE :: TestDB TO user_readonly
but when I am using the above query then the new user is the owner of the testdb. i don't want that. I want that the user will have only select permission on the table.is there any way?
The below would work if all of the values in A.Value were numbers but they are not. So I need to restrict the view to only look at the following measures but still show all the other row.
WHERE [Measure] IN ('RTT-01','RTT-04','RTT-07') SELECT M.[Description] ,A.* ,M.Threshold ,M.[Threshold Direction]
[Code] ....
Is there any way that I can create a select statement in the case when to only look for them measures that I know contain numbers?
I've got a view that creates a parent child relationship, this view isused in Analysis Services to create a dimension in a datastore. Thisquery tends to deadlock after about 10 days of running smoothly. Onlyway to fix it is to reboot the box, I can recycle the services for aquick fix but that usually only works for the next 1-2 times I call theview.This view is used to create a breakdown of the bill-to locations fromContinent-Global Region-Country-Sub Region-State/Province- City-ZipCodeYes, I know that sounds crazy, but it was a requirement.So why would I get a deadlock on a SELECT Query? Is there a way to setthe Isolation level to Repeatable Read for a view?Here is the view code:CREATE View dbo.vwBillToas-- US ZipCodeSelect 'Parent'=z.City+' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')',z.Zipcode_WK as 'Child',z.ZipCode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToWherez.US_Region_wk IS NOT NULLGroupby z.City,z.ZipCode_WK,US_Region_wk, z.State_shrtUnion--CitySelect 'Parent'=z.State_Long+' ('+cast(IsNull(z.US_Region_wk,0) asvarchar)+')',z.City as 'Child',z.City + ' ('+ ISNULL(RTRIM(z.State_shrt), '0') +cast(IsNull(z.US_Region_wk,0) as varchar) + ')' as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_US zWherez.US_Region_wk IS NOT NULLGroupby z.State_Long,z.City,z.State_shrt,z.US_Region_wkUnion-- Canada ZipCodeSelect 'Parent'=z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') +')',z.Zipcode_WK as 'Child',z.Zipcode_WK as 'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion--CitySelect 'Parent'=z.Province_Long,z.City as 'Child',z.City+ ' ('+ ISNULL(RTRIM(z.province_shrt), '0') + ')' as'Child_ID'Fromdbo.DIM_POSTAL_CODES_CAN zinnerjoin dbo.FACT_SALES fonz.ZipCode_WK=f.Bill_ToGroupby z.Province_Long,z.ZipCode_WK, z.City, z.province_shrtUnion-- Canada ProvinceSelect 'CANADA',Province_Long,Province_LongFromdbo.DIM_POSTAL_CODES_CANGroupby Province_LongUnion-- CountrySelect t.Region_NK,c.Country_Name,c.Country_NameFromdbo.DIM_COUNTRY cInnerJoin dbo.DIM_WORLD_REGION tOnc.Region_WK=t.Region_WKWherec.Country_Name Is Not NullGroup by t.Region_NK, c.Country_NameUnion-- SubRegionSelect c.Country_Name,sr.US_Region_Name,sr.US_Region_NameFromdbo.DIM_US_REGION srInnerJoin dbo.DIM_COUNTRY cOnsr.Country_wk=c.Country_WKGroupby c.Country_Name, sr.US_Region_NameUnion--RegionSelect sr.US_Region_Name,c.State_Long,c.State_Long+' ('+cast(c.US_Region_wk as varchar)+')'Fromdbo.DIM_US_REGION srInnerJoin dbo.DIM_POSTAL_CODES_US cOnsr.US_Region_WK=c.US_Region_WKGroupby sr.US_Region_Name, c.State_Long,c.US_Region_wkUnion-- ContinentSelect Null,Region_NK,Region_NK[color=blue]>From dbo.DIM_WORLD_REGION[/color]WhereRegion_NK Is Not Null
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList] as select m.id as Max4SaleId, mt.[Description] as [TypeDescription], CAST(m.[type] as tinyint) as [Type], m.start_time as [StartTime], m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
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)
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):
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
I have two databases DB1 and DB2 DB1 has a source table named 'Source' I have created a login 'Test_user' in DB2 with Public access. I have also created a view named 'Test_view' in DB2 which references data from DB1.dbo.Source
I have a list of ClassID that is stored based on users multi select on a listview
For example ClassID might contain
301 302 303 304
Now I need to find InstructorID where classID matches all the value in the above list.
I am using this query
Code: Dim assSQL = "Select InstructorID from ClassInstructors where ClassID = @P0" For i = 1 To classIDs.Count - 1 assSQL &= " UNION Select InstructorID from ClassInstructors where ClassID = @P" & i.ToString Next
[Code] ....
But the problem is the query is returning InstructorID where ClassID matches any of the ClassIDs. I want it to return Instructor ID where ClassID matches all of the ClassIDs in the string.
Has anyone encounted a problem with Reporting Services (2005) with the following scenario: A report with a dropdown parameter allowing a Select All choice -
If the selection list is fairly long and Select All is selected - if the user clicks the View Report button the selection list is completely reset (i.e. all items are unselected). This seems to happen if the user quickly clicks the View Report button immediately after clicking the Select All box on the parameter dropdown.
This happens inconsistently - some users experience it frequently while others do not see it at all (using the same parameter values, etc.)
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?
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
I created a query, which makes use of a temp table, and I need the results to be displayed in a View. Unfortunately, Views do not support temp tables, as far as I know, so I put my code in a stored procedure, with the hope I could call it from a View....
I compared view query plan with query plan if I run the same statementfrom view definition and get different results. View plan is moreexpensive and runs longer. View contains 4 inner joins, statisticsupdated for all tables. Any ideas?
My 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 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.
CREATE VIEW dbo.vwFeat AS SELECT dbo.Lk_Feat.Descr, dbo.Lk_Feat.Price, dbo.Lk_Feat.Code, dbo.SubFeat.SubNmbr FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
When ever I open using SQL Entreprise manager to edit it by adding or removing a field i inserts Expr1,2.. and I don t want that. The result I get is:
SELECT dbo.Lk_Feat.Descr AS Expr1, dbo.Lk_Feat.Price AS Expr2, dbo.Lk_Feat.Code AS Expr3, dbo.SubFeat.SubNmbr AS Expr4 FROM dbo.Lk_Feat INNER JOIN dbo.SubFeat ON dbo.Lk_Feat.Idf = dbo.SubFeat.Idt
I don t want Entreprise manager to generate the Expr fields since I use the real fields in my application. Thanks for help
I was looking through our vendors views, searching for something Ineeded for our Datawarehouse and I came across something I do notunderstand: I found a view that lists data when I use it in t-sql,however when I try to use the statement when I modified the view (viaMS SQL Server Management Studio) I can not execute the statement. I getThe column prefix 'dbo.tbl_5001_NumericAudit' does not match with atable name or alias name used in the query.Upon closer inspection, I found two ON for the inner join, which I dontthink is correct.So, how can the view work, but not the SQL that defines the view?SQL Server 2000, up to date patches:SELECT dbo.tbl_5001_NumericAudit.aEventID,dbo.tbl_5001_NumericAudit.nParentEventID,dbo.tbl_5001_NumericAudit.nUserID,dbo.tbl_5001_NumericAudit.nColumnID,dbo.tbl_5001_NumericAudit.nKeyID,dbo.tbl_5001_NumericAudit.dChangeTime,CAST(dbo.tbl_5001_NumericAudit.vToValue ASnVarchar(512)) AS vToValue, dbo.tbl_5001_NumericAudit.nChangeMode,dbo.tbl_5001_NumericAudit.tChildEventText, CASEWHEN nConstraintType = 3 THEN 5 ELSE tblColumnMain.nDataType END ASnDataType,dbo.tbl_5001_NumericAudit.nID,CAST(dbo.tbl_5001_NumericAudit.vFromValue AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_NumericAudit WITH (NOLOCK) LEFT OUTER JOINdbo.tblColumnMain WITH (NoLock) INNER JOIN---- Posters comment: here is the double ON--dbo.tblCustomField WITH (NoLock) ONdbo.tblColumnMain.aColumnID = dbo.tbl_5001_NumericAudit.nColumnID ONdbo.tbl_5001_NumericAudit.nColumnID =dbo.tblCustomField.nColumnID LEFT OUTER JOINdbo.tblConstraint WITH (NOLOCK) ONdbo.tblCustomField.nConstraintID = dbo.tblConstraint.aConstraintID AND(dbo.tblConstraint.nConstraintType = 4 ORdbo.tblConstraint.nConstraintType = 9 ORdbo.tblConstraint.nConstraintType = 3)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(CAST(vToValue AS decimal(19, 6)) AS nVarchar(512)) ASvToValue,nChangeMode, tChildEventText, 5 AS nDataType,nID, CAST(CAST(vFromValue AS decimal(19, 6)) AS nVarchar(512)) ASvFromValueFROM dbo.tbl_5001_FloatAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CAST(vToValue AS nVarchar(512)) AS vToValue, nChangeMode,tChildEventText, 2 AS nDataType, nID,CAST(vFromValue AS nVarchar(512)) AS vFromValueFROM dbo.tbl_5001_StringAudit WITH (NOLOCK)UNION ALLSELECT aEventID, nParentEventID, nUserID, nColumnID, nKeyID,dChangeTime, CONVERT(nVarchar(512), vToValue, 121) AS vToValue,nChangeMode,tChildEventText, 3 AS nDataType, nID,CONVERT(nVarchar(512), vFromValue, 121) AS vFromValueFROM dbo.tbl_5001_DateAudit WITH (NOLOCK)
