How To Rewrite The View
Apr 7, 2008
Currently I just need to rechange a view which is writen by other staff member because of low performance.
This view comprises 4 query statement and link using Union statement.
Each query statement will refer more than 8 tables and other views.
Now I want to rewrite it but I can not know how to do. I change union to CTE, no performance improved.
I want to create indexed view, but because this view refer the other view, I cannot create index on it?
Any tips for me? Thank you for help.
View 6 Replies
ADVERTISEMENT
Apr 18, 2002
How can l rewrite this and trim the code.
CREATE Procedure Disbursements_Cats
(@startdate datetime,
@enddate datetime)
AS
Begin
SELECT Loan.loan_No AS Loan_No,
Loan.customer_No AS Customer_No,
Customer.first_name AS First_name,
Customer.second_name AS Second_name,
Customer.surname AS Surname,
Customer.initials AS Initials,
Bank.Bank_name AS Bank_name,
Branch.Branch_name AS Branch_name,
Branch.branch_code AS Branch_code ,
Bank_detail.bank_acc_type AS Bank_acc_type,
Transaction_Record.transaction_Amount AS Transaction_Amount,
Transaction_Record.transaction_Date AS Transaction_Date,
Loan.product AS Product,
Product.product_Type AS Product_Type,
Product_Type.loan_Type AS Loan_Type
FROM Transaction_Record INNER JOIN
Loan ON Transaction_Record.loan_No = Loan.loan_No INNER JOIN
Product ON Loan.product = Product.product INNER JOIN
Customer ON Loan.customer_No = Customer.customer_no INNER JOIN
Bank_detail ON Customer.customer_no = Bank_detail.customer_no INNER JOIN
Branch ON Bank_detail.Branch = Branch.Branch INNER JOIN
Bank ON Branch.Bank = Bank.Bank INNER JOIN
Product_Type ON Product.product_Type = Product_Type.product_Type
END;
GO
View 1 Replies
View Related
Mar 28, 2008
Hi all,
Here's the code I'd like to update, and below it a set of sample data:
Declare @StartDate DateTime
Declare @EndDate DateTime
Set @StartDate = '20-mar-2008'
Set @EndDate = '25-mar-2008'
SELECT
COUNT (iqs.childid) as Cnt
,CASE
--Search Categories and Create Initial Groupings--
WHEN Category LIKE '%Jewel%' THEN 'Accessories'
WHEN Category LIKE '%Beauty%' THEN 'Accessories'
WHEN Category LIKE '%Accs%' THEN 'Accessories'
WHEN Category LIKE '%Gift%' THEN 'Accessories'
WHEN Category LIKE '%Grooming%' THEN 'Accessories'
WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand'
WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand'
WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand'
WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand'
WHEN Category LIKE '%Female%' THEN 'Womenswear'
WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand'
WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand'
WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand'
WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand'
WHEN Category LIKE '%Male%' THEN 'MensOwnBrand'
END AS CategoryGroup
,CONVERT(VARCHAR(10), iqs.StatusDate, 103) AS StatusDate
FROM InventoryQueryStatus iqs
JOIN InventoryStatus [is]
ON [is].StatusID = iqs.StatusID
JOIN Inventory i
ON i.InventoryID = iqs.InventoryID
JOIN InventoryCategory ic
ON ic.CategoryID = i.CategoryID
WHERE iqs.StatusID = 31000
and Category NOT LIKE 'Force%'
--AND iqs.StatusDate >=GETDATE()-1
AND iqs.StatusDate BETWEEN @StartDate AND @EndDate
GROUP BY
CASE
--Search Categories and Create Initial Groupings--
WHEN Category LIKE '%Jewel%' THEN 'Accessories'
WHEN Category LIKE '%Beauty%' THEN 'Accessories'
WHEN Category LIKE '%Accs%' THEN 'Accessories'
WHEN Category LIKE '%Gift%' THEN 'Accessories'
WHEN Category LIKE '%Grooming%' THEN 'Accessories'
WHEN Category LIKE '%Female%Prem%Brands%' THEN 'WomensPremiumOutsideBrand'
WHEN Category LIKE '%Female%Prem%OB%' THEN 'WomensPremiumOwnBrand'
WHEN Category LIKE '%Female%Brand%' THEN 'WomensOutsideBrand'
WHEN Category LIKE '%Female%OB%%' THEN 'WomensOwnBrand'
WHEN Category LIKE '%Female%' THEN 'Womenswear'
WHEN Category LIKE '%Male%Prem%Brands%' THEN 'MensPremiumOutsideBrand'
WHEN Category LIKE '%Male%Prem%OB%' THEN 'MensPremiumOwnBrand'
WHEN Category LIKE '%Male%Brand%' THEN 'MensOutsideBrand'
WHEN Category LIKE '%Male%OB%' THEN 'MensOwnBrand'
WHEN Category LIKE '%Male%' THEN 'MensOwnBrand'
END
,CONVERT(VARCHAR(10), iqs.StatusDate, 103)
Order By StatusDate
Sample Data:
164WomensOutsideBrand 20/03/2008
5MensOutsideBrand 20/03/2008
78WomensOwnBrand 20/03/2008
92WomensPremiumOutsideBrand 20/03/2008
1Accessories 20/03/2008
However, I'd like to enable a total for the day (340)
Thanks,
JB
View 1 Replies
View Related
Aug 29, 2005
I have a WHERE clause that could be an "=" or a "LIKE" depending uponif the passed variable is populated or not. I would like to know thebest way to write the WHERE clause to make it dynamically switchbetween the 2 and make best use of the indexes.CREATE TABLE myTable(ID INT PRIMARY KEY CLUSTERED, COUNTY VARCHAR(50))CREATE INDEX IDX_myTable_County ON myTable(COUNTY)DECLARE @COUNTY VARCHAR(50)SET @COUNTY = 'SANTA CLARA' -- Could also be SET @COUNTY = NULLSELECT ID FROM myTableWHERE COUNTY LIKE (CASE WHEN @COUNTY IS NOT NULL THEN @COUNTY ELSE '%'END)This does not seem like best practice to me because I am forced to use"LIKE" even when @COUNTY is populated with data. Ultimately I'd like:WHERE (CASE WHEN @COUNTY IS NOT NULL COUNTY = @COUNTY ELSE COUNTY LIKE'%' END)but that is incorrect syntax on "=".Also, I do not want to use a dynamically built statement. Is there away around this?Thanks,Josh
View 14 Replies
View Related
Mar 15, 2007
Is there a efficient way to write this query?
SELECT CASE
WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END AS Population_Range,
COUNT(CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END) AS [No. Of Countries]
FROM Country
GROUP BY
CASE WHEN Population BETWEEN 0 AND 100 THEN '0-100' WHEN Population BETWEEN 101 AND 1000 THEN '101-1000' ELSE 'Greater than 1000' END
View 1 Replies
View Related
May 29, 2007
Hello all
We are load testing SQL 2005 and I need to re-write the index scripts that we have from 2000. Is there an easier way to rewrite the scripts without having to go to each job, then each step and modify it?
Our current index script template is:
Create NonClustered Index [IdxABCDE]
On dbo.blahblah(blahID)
With FillFactor = 90, Statistics_NoRecompute
On [Index2]
Go
and I need to rewrite it as:
ALTER INDEX [IdxABCDE] ON [dbo].[blahblah]
REBUILD WITH (FILLFACTOR = 90, ONLINE = OFF,SORT_IN_TEMPDB=ON, STATISTICS_NORECOMPUTE = ON, MAXDOP=4)
I am thinking I would have to rewrite the scripts from scratch. We have hundreds of index scripts. So before I brace myself to lot of typing, just wanted to find out if there is any easier way..
Thanks..
Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/
View 4 Replies
View Related
May 1, 2008
I have a complex stored procedure that utilises inner joins, and in the WHERE clause there is defined a subquery. This subquery has now cause a performance hit to the ponit where the server is returning a timeout. Need to find an alternate fast solution.....
SELECT BE.BlogEntryID
FROM vw_BlogEntry BE
INNER JOIN @BlogView BC ON BC.CommonID = BE.BlogCommonID
INNER JOIN vw_Blog B ON B.BlogID = BC.BlogID
WHERE
(
...
)
AND
(
....
)
AND
(
-- GET ENTRIES WHERE COMMENT COUNT IS AT LEAST @CommentCount (..or @CommentCount = 0)
@CommentCount <= 0
OR BE.CommonID IN (SELECT bc.EntryCommonID
FROM vw_BlogComment_Current bc
INNER JOIN tblVersionDetails vd ON bc.VersionID = vd.VersionID
WHERE
IsNull(@CommentStatus,'') = ''
OR vd.Status IN (SELECT * FROM fn_CsvToString(@CommentStatus))
GROUP BY bc.EntryCommonID
HAVING COUNT(bc.EntryCommonID) >= @CommentCount)
)
View 10 Replies
View Related
Jan 17, 2008
This works, but it's highly unefficient and generates a lot of IO. Is there another way to do it without the use of temp tables?
Code Block
select eh.*
from Equipment_History eh
where Equipment_History_ID in
(select top 2 Equipment_History_ID
from Equipment_History eh1
where Equipment_ID in
(select Equipment_ID
from Equipment_History eh2
where Equipment_History_ID in
(select min(Equipment_History_ID)
from Equipment_History eh3
where eh2.Equipment_ID = eh3.Equipment_ID
and eh2.Equipment_Status_Type in (1,2,3)))
and eh1.Equipment_ID = eh.Equipment_ID
order by Equipment_History_ID)
Equipment_History_ID is PK
Let me know what other information would be useful, I can barely understand my logic from looking at the code but it does in fact work.
View 4 Replies
View Related
May 26, 2015
I have to modify a stored procedure that is written by someone else.Basically the stored prcoedure uses a cursor to fetch the data from the table and then insert that data in another table. While fetching the code form another table, it also gets some distinct columns from another table Below is my code:
Declare data_cursor cursor for
Select emp_no, emp_name, event_date, Test_no, Code, Test_result
From test_table1
order by emp_no
[code]...
The reason, I have to modify the above stored proc because now because of application changes, I am getting around 50 distinct userID from test_table1 so the above subquery(SELECT @ProcessName = (select distinct userID from test_table1) won't work. How can I loop through the above stored proc so that each @ProcessName can get inserted in table TESTTable2 so in other words
I want to pass each userId one at a time and insert it in table test_table1 and other subsequent tables. I can declare another cursor to accomplish this, but I was wondering if there is any better way to rewrite this stored proc and not use the cursor at all.because of my application changes all these three statements above are throwing the error:
SELECT @ProcessName = (select distinct userID from test_table1)
SELECT @FileProcess = 'EW' + @ProcessName
Select @TestProcess = (Select distinct userID from testTable1) + 'TXT'
View 8 Replies
View Related
Sep 21, 2007
Hello,
I have a query with 11 left joins. Some hits against tables with small amounts of reference data, whereas others are not so small. Should I rewrite this in another way, as performance is a requirement on this one? Or, should I do it another way?
How would I rewrite left joins? Any examples?
Thanks.
View 5 Replies
View Related
Oct 30, 2007
Hello.
I have a query which spends a lot of time calculating my CASE WHEN -statements.
My query looks like this
SELECT TOP 250
UserId,
CASE WHEN
(someCol*0.4+someOtherCol*0.3) > 99 THEN 99
ELSE
(someCol*0.4+someOtherCol*0.3)
END
FROM
(
SELECT
UserId,
CASE WHEN @myparam > 50 THEN
CASE WHEN Col5+@myincrease > 99 THEN 99 ELSE Col5+@myincrease END
ELSE
CASE WHEN Col6+@myincrease > 99 THEN 99 ELSE Col6+@myincrease ENDEND as someCol,
CASE WHEN Col8+@myincrease3 > 99 THEN 99 ELSE Col8+@myincrease3 END as SomeOtherCol
FROM
SomeTable
) t1
This is just a little bit of the full query. I cannot write the whole query since it contains alot of different views and calculations, but I have traced it down to that all these case when-statements is taking a lot of time to do. So I hope that this example will be enough for you to understand my problem.
I know about some tricks that can replace a CASE WHEN, for example using COALESCE or BETWEEN but that does not seem to work in my case.
Any suggestions?
View 3 Replies
View Related
Mar 21, 2006
Use the view master.sys.sql_logins (new in 2005) to get at the varbinary passwords like you did in your Sql Server 2000 scripts (instead of using passwords from master.dbo.sysxlogins).
I have altered the sp_help_revlogin (from Microsoft article # 246133 )
PLEASE TEST/FIX before you use this:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_help_revlogin_2005]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_help_revlogin_2005]
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
CREATE PROCEDURE sp_help_revlogin_2005 @login_name sysname = NULL AS
DECLARE @name sysname
DECLARE @logintype char(1)
DECLARE @logindisabled int
DECLARE @binpwd varbinary (256)
DECLARE @txtpwd sysname
DECLARE @tmpstr varchar (256)
DECLARE @SID_varbinary varbinary(85)
DECLARE @SID_string varchar(256)
IF (@login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name <> 'sa' and type in ('S','U','G')
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, type, is_disabled FROM master.sys.server_principals
WHERE name = @login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
IF (@@fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @tmpstr = '/* sp_help_revlogin_2005 script '
PRINT @tmpstr
SET @tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @@SERVERNAME + ' */'
PRINT @tmpstr
PRINT ''
PRINT 'DECLARE @pwd sysname'
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
PRINT ''
SET @tmpstr = '-- Login: ' + @name
PRINT @tmpstr
IF (@logintype = 'G' OR @logintype = 'U')
BEGIN -- NT authenticated account/group
IF @logindisabled = 1
BEGIN -- NT login is denied access
SET @tmpstr = 'EXEC master..sp_denylogin ''' + @name + ''''
PRINT @tmpstr
END
ELSE BEGIN -- NT login has access
SET @tmpstr = 'EXEC master..sp_grantlogin ''' + @name + ''''
PRINT @tmpstr
END
END
ELSE IF (@logintype = 'S')
BEGIN -- SQL Server authentication
SELECT @binpwd = password_hash FROM master.sys.sql_logins WHERE SID = @SID_varbinary
IF (@binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @binpwd, @txtpwd OUT
SET @tmpstr = 'SET @pwd = CONVERT (nvarchar(128), ' + @txtpwd + ')'
PRINT @tmpstr
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name
+ ''', @passwd = @pwd, @sid = ' + @SID_string + ', @encryptopt = ''skip_encryption'''
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @SID_varbinary,@SID_string OUT
SET @tmpstr = 'EXEC master..sp_addlogin @loginame = ''' + @name
+ ''', @passwd = NULL, @sid = ' + @SID_string
END
PRINT @tmpstr
END
END
FETCH NEXT FROM login_curs INTO @SID_varbinary, @name, @logintype, @logindisabled
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
View 4 Replies
View Related
Aug 14, 2007
I have a activeX script like below in DTS and I am trying to rewrite it in SSIS.
What is the best way to do this?
Can I do this using a look up table? or other transformers in SSIS
'**********************************************************************
' Visual Basic Transformation Script
'************************************************************************
' Copy each source column to the destination column
Function Main()
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then
DTSDestination("RACE_NATIVE_AM_IND") = "Y"
else
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "2" then
DTSDestination("RACE_NATIVE_AM_IND") = "N"
end if
end if
if DTSSource("RACE_ASIAN_YN") = "1" then
DTSDestination("RACE_ASIAN_IND") = "Y"
else
if DTSSource("RACE_ASIAN_YN") = "2" then
DTSDestination("RACE_ASIAN_IND") = "N"
end if
end if
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "1" then
DTSDestination("RACE_BLACK_IND") = "Y"
else
if DTSSource("RACE_AFRICAN_AMERICAN_YN") = "2" then
DTSDestination("RACE_BLACK_IND") = "N"
end if
end if
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then
DTSDestination("RACE_HAWAIIAN_IND") = "Y"
else
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "2" then
DTSDestination("RACE_HAWAIIAN_IND") = "N"
end if
end if
if DTSSource("RACE_CAUCASIAN_YN") = "1" then
DTSDestination("RACE_WHITE_IND") = "Y"
else
if DTSSource("RACE_CAUCASIAN_YN") = "2" then
DTSDestination("RACE_WHITE_IND") = "N"
end if
end if
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 9 then
if DTSSource("RACE_AMERICAN_INDIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 40
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource ("RACE_ASIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 16
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource ("RACE_AFRICAN_AMERICAN_YN") = "1" then
DTSDestination ("RACE_CD") = 32
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource("RACE_NATIVE_HAWAIIAN_YN") = "1" then
DTSDestination ("RACE_CD") = 51
DTSDestination ("RACE_MULT_IND") = "N"
DTSDestination ("RACE_OTH_IND") = "N"
else
if DTSSource("RACE_CAUCASIAN_YN") = "1" then
DTSDestination("RACE_CD") = 31
DTSDestination("RACE_MULT_IND") = "N"
DTSDestination("RACE_OTH_IND") = "N"
end if
end if
end if
end if
end if
else
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) = 10 then
DTSDestination("RACE_CD") = 99
DTSDestination("RACE_MULT_IND") = "N"
DTSDestination("RACE_OTH_IND") = "N"
else
if CInt (DTSSource("RACE_AMERICAN_INDIAN_YN")) + CInt (DTSSource("RACE_ASIAN_YN")) + CInt (DTSSource("RACE_AFRICAN_AMERICAN_YN")) + CInt (DTSSource("RACE_NATIVE_HAWAIIAN_YN")) + CInt (DTSSource("RACE_CAUCASIAN_YN")) < 9 then
DTSDestination("RACE_CD") = 52
DTSDestination("RACE_MULT_IND") = "Y"
DTSDestination("RACE_OTH_IND") = "N"
end if
end if
end if
Main = DTSTransformStat_OK
End Function
View 3 Replies
View Related
Mar 13, 2007
Community:
Suposse that some models are deployed in Report Server for a while, and users have developed some ad-hoc reports on them using Report Builder, (some of the models are SSAS Cubes).
Modifications are required for a Model, what is the procedure to deploy this modifications? What happens with ad-hoc reports of this Model? Rewrite all the reports is a VERY BAD option, I agree that some reports must be rewrote, but only if they reference objects no longer valids in new model.
I suposse that the procedure for SSAS Cube Models will be different for a Relational Database Source because metods of generating models are so different. (I am particularly curious about Cubes, I can't figure out how I can do it)
Suggestions and links will be very appreciate.
Regards
Julio Díaz C.
View 6 Replies
View Related
Jul 23, 2005
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 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
Aug 24, 2007
Hi guys 'n gals,
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 tried:
CREATE VIEW [qryMyView]
AS
EXEC pr_MyProc
and unfortunately, it does not let this run.
Anybody able to help me out please?
Cheers!
View 3 Replies
View Related
Mar 9, 2006
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?
View 10 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
Feb 17, 2006
I have this view in SQL server:
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
View 4 Replies
View Related
Oct 27, 2006
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)
View 1 Replies
View Related
Feb 21, 2006
A colleague of mine has a view that returns approx 100000 rows in about 60 seconds.
He wants to use the data returned from that view in an OLE DB Source component.
When he selects the view from the drop-down list of available tables then SSIS seems to hang without any data being returned (he waited for about 15 mins).
He then changed the OLE DB Source component to use a SQL statement and the SQL statement was: SELECT * FROM <viewname>
In this instance all the data was returned in approx 60 seconds (as expected).
This makes no sense. One would think that selecting a view from the drop-down and doing a SELECT *... from that view would be exactly the same. Evidently that isn't the case.
Can anyone explain why?
Thanks
-Jamie
View 2 Replies
View Related
Mar 29, 2007
I need to create a view that will look at payments made by clients and show me the clients that made payments last year but not this year.
tblPayments (ClientID, PmtID, PmtDate, PmtAmt)
How would I set the criteria for this?
View 1 Replies
View Related
Feb 7, 2008
How do I set up a sql statement to use as a view?
I want to use two tables but they are both from different databases.
From the Offices database I want to use a table called Office Code and only the Name column
The other database is called Library and I want to use a table called Requestors and join it to others.
I've set the stored procedure up that I want to use to get most of the data besides the data in the offices database.
Create view GetDataview
select requestors.officeCode, requestors.Fname + ' ' + requestors.Lname as [Name], Titles.title as Title from requestors
join libraryrequest on libraryrequest.requestorid = requestors.requestoridjoin Titles on Titles.Titleid = libraryrequest.Titleidwhere officecode <> 0order by OfficecodeGO How do I add the database offices, table officecodes to this view to use the Name column?
I hope this makes sense
View 6 Replies
View Related
May 18, 2008
how can i make a view in my sqlexpress 2005 database that get data from oracle database or from RDB (old oracle database)
View 6 Replies
View Related
May 18, 2001
I am trying to write a view to retrieve data from
two different servers..
create view as
select c1 from ser1.database1.dbo.table1
union
select 1 from ser2.database2.dbo.table1
First it is not accepting to write it from EM, hence I wrote it from QA,
the problem when I am trying to execute it is giving following error
"Could not find server 'hercules' in sysservers"
Help me out what needs to be done?
View 2 Replies
View Related
Jan 12, 2001
i need to create a view for a table which is residing on another remote server how to do this could u guide me with the process and give me an example.
ur help is appriciated.
View 1 Replies
View Related
Sep 21, 2000
I have a view which I am supposed to run manually first thing tomorrow morning but I will not be
in tomorrow. Is it possible for me to schedule it as a job to run tomorrow.
View 1 Replies
View Related
May 7, 2002
How many maximum number of tables I can used to create the view in SQL 6.5..?
View 1 Replies
View Related
Oct 24, 2007
When I ran this SQL Query, I got an unexpected result.
//Sql Query
Code:
SELECT distinct Year FROM vw_EMD_Options_Pricing WHERE (Year >= '2003') AND (Year <= '2008')
The view is the "vw_EMD_Options_Pricing". The request I got is the year that go from 1981 to 2008. This is for MS-SQL 2000. Why does the SQL Query does that?
View 2 Replies
View Related
Oct 20, 2004
I have a database with a view to another table in another database which I created a form out of. I was wondering when if this rule applies
"Data Entry" property of the form is set to True. The default value for this property is "False" which means that Access opens the form and shows the existing records. However, if set to True, the Data Entry property of the form specifies that the form will only show a blank record.
The reason I am asking is because you cant see the records after you close the form and reopen it again.
View 1 Replies
View Related
Apr 6, 2007
Hi,
I have 2 databases, db1, db2 in the SQL Server. In db1, I created the view to link one table from db2. When I run it, it is just read-only, I want to edit/insert data in the view. Can we edit/insert the data in the view from db1(which linked table from db2)?
View 1 Replies
View Related
May 18, 2004
Hello Everyone,
When I write a sql joining more than views how will be executed?.
Does SQL Server execute each view and join the result set or interpret into one final sql and execute?
Thanks
Masanam
View 3 Replies
View Related
