I Am Struggling Through Views
Jul 20, 2005
Hi there, it's me again. I am having trouble with a view again. I am
trying to do a calculation, but there are some checks that need to be
taken into consideration. Maybe a view is not the right way to deal
with this. I don't know.
This is the beginning of my query.
SELECT coalesce(f.filenumber, i.filenumber) as filenumber,
i.InvoiceNumber, i.InvoiceValue, il.lineid, MPF = .21 * (il.UnitCost *
il.UnitQty + il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt)
FROM tblFILE f inner join tblINVOICE i on (f.filenumber =
i.filenumber) left outer join tblINVOICE_LINE il on (i.Invoiceid =
il.invoiceid)
This works just as it should. However, if the Sum of all MPFs per
file total less than 25.00 or more than 485.00 then each MPF has to be
recalculated as:
Percentage of TotalEnteredValue = (InvoiceValue / (il.UnitCost *
il.UnitQty + il.AddMMV - il.MinusMMV - il.MinusNDC + il.ErrorAmt)
Percentage of TotalEnteredValue * 25.00 = MPF or
Percentage of TotalEnteredValue * 485.00 = MPF
Can you do something like this in a View? Or do I need to do
something like a trigger?
I greatly appreciate all help. I am struggling to get a foothold on
views. I am getting there.
View 4 Replies
ADVERTISEMENT
Jan 23, 2007
Hi everyone,
We've got running an vb .net service which throws on-demand .DTSX. Now a new feature has been added: it must be able to launch dts 2000/7.0 too. No problem with that. It works fine using the same thread.
Issue comes when we were accostumed to cancel any DTSX execution for sake IDTSEvents interface and OnQueryCancel event. We want to the same but for the old ETL.
My wonder is how do the same but related with DTS???
I attached you this snippet of code which is responsible for launching a dts:
Private WithEvents paquete As DTS.Package2
paquete = New DTS.Package2
paquete.LoadFromSQLServer("SRVDESA1", , , DTS.DTSSQLServerStorageFlags.DTSSQLStgFlag_UseTrustedConnection, , , , "pruebaenric")
paquete.Execute()
Private Sub paquete_OnQueryCancel(
stuff (NEVER ENTRY HERE) ???
Private Sub paquete_OnProgress(ByVal EventSource As String, _
stuff (NEVER ENTRY HERE) ???
Private Sub paquete_OnError(ByVal EventSource As String, _
stuff (NEVER ENTRY HERE) ???
Let me know what's happening? Is something related with managed and unmanaged code? Any COM feature or something like that??
Or is only that I've got to define a private class how I did with SSIS and to use one specific interface??
Primary platform is Framework 2.0
Thanks in advance for your time and information provided,
Enric
View 1 Replies
View Related
Jul 25, 2006
Have my first .NET app deployed to remote host and configured successfully, except for one thing: can't figure out how to export my SSE tables to the SQL server database on the remote server. I tried SQL Server Management Studio Express ... with that, I can access the remote database, but can't access the local database file I produced with VSWDE. What is the appropriate tool to use to export tables from a local SSE database file to a remote SQL Server 2005?
View 3 Replies
View Related
Jan 17, 2008
I am trying to get the SUM of the values from a subquery and I just can't wrap my head around the subquery thing! Can anyone please point out the error of my ways?
I have tried 2 slightly different ones:
Select project.[id] "ID", companyname.[name]"Company", project.projectname "ProjName", project.startdate "StartDate"
,SUM(BudgetTotal + AddTotal + StockTotal) "TotalBudget"
(SELECT ISNULL(SUM(budget.budget),0) AS BudgetTotal
FROM budget WHERE budget.projectID = project.[id])
(SELECT ISNULL(SUM(budget_additionals.budget),0) AS AddTotal
FROM budget_additionals WHERE budget_additionals.projectID = project.[id])
(SELECT ISNULL(SUM(project_stock.saleprice),0) AS StockTotal
FROM project_stock WHERE project_stock.projectID = project.[id])
From project, companyname
WHERE project.companyid = companyname.[id]
AND (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY "Company"
This gives me an "Server: Msg 156, Level 15, State 1, Line 10
Incorrect syntax near the keyword 'From'" error...
The next one i tried looked like this:
Select project.[id] "ID"
,companyname.[name]"Company"
,project.projectname "ProjName"
,project.startdate "StartDate"
,SUM((SELECT ISNULL(SUM(budget.budget),0)FROM budget WHERE budget.projectID = project.[id]) +
(SELECT ISNULL(SUM(budget_additionals.budget),0)FROM budget_additionals WHERE budget_additionals.projectID = project.[id]) +
(SELECT ISNULL(SUM(project_stock.saleprice),0)FROM project_stock WHERE project_stock.projectID = project.[id])
) "Budget"
From project, companyname
WHERE project.companyid = companyname.[id]
AND (project.startdate >= '2006-11-01')
AND (project.startdate <= '2007-10-31')
AND project.jobtypeid = 1
ORDER BY "Company"
And I get "Cannot perform an aggregate function on an expression containing an aggregate or a subquery"
Cheers in advance :-)
Glyn
View 2 Replies
View Related
Apr 2, 2007
I have two tables I need to create in msSQL and I have the .sql files with the information in them, is it possible to insert them through enterprise manager?
Or another way? Or are they meant for mySQL?
cheers
View 3 Replies
View Related
Oct 31, 2007
Hello all
I am trying to insert data from the Report table to the Revised_Maintable
my problem is that I get an error message every time. I dont know what to do
INSERT INTO dbo.Revised_MainTable([IR Number], Date, [I/RDocument], [Violation Type] )
SELECT [Date], [I/RDocument], TypeOfIncident, [Incident Report No] FROM dbo.Report
WHERE NOT EXISTS(SELECT * FROM dbo.Report WHERE [Incident Report No] = [IR Number])
This is the Report Table [dbo].[Report](
[Incident Report No] [nvarchar](100) NOT NULL,
[Date] [datetime] NULL,
[Time] [datetime] NULL,
[Investigators Name] [nvarchar](100) NULL,
[Reported by] [nvarchar](100) NULL,
[Law Enforcement Agency] [nvarchar](100) NULL,
[Name of Officer] [nvarchar](100) NULL,
[Evidence Seized] [nvarchar](100) NULL,
[Associated Reports] [nvarchar](50) NULL,
[Corrective Action] [nvarchar](50) NULL CONSTRAINT [DF_Report_Corrective Action] DEFAULT (N'YES'),
[Comments] [ntext] NULL,
[I/RDocument] [ntext] NULL CONSTRAINT [DF_Report_I/RDocument] DEFAULT (N'SCANNED REPORT'),
[TypeOfIncident] [nvarchar](300) NULL,
[Exclusion] [nvarchar](50) NULL CONSTRAINT [DF_Report_Exclusion] DEFAULT (N'NO'),
[86_D] [nvarchar](50) NULL CONSTRAINT [DF_Report_86_D] DEFAULT (N'NO'),
[Loss] [money] NULL CONSTRAINT [DF_Report_Loss] DEFAULT (0.0000),
[LossType] [nvarchar](50) NULL,
[Area] [nvarchar](75) NULL,
[Action/Incident] [nvarchar](50) NULL,
[Security/GC] [nvarchar](50) NULL CONSTRAINT [DF_Report_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Report] PRIMARY KEY CLUSTERED
(
and the Revised_MainTable
[dbo].[Revised_MainTable](
[I/RDocument] [ntext] NULL CONSTRAINT [DF_Revised_MainTable_I/RDocument] DEFAULT (N'Scanned Report'),
[IR Number] [nvarchar](100) NOT NULL,
[Date] [datetime] NULL,
[Inspector] [nvarchar](50) NULL,
[Area] [nvarchar](50) NULL,
[Violation] [nvarchar](50) NULL,
[Violation Type] [nvarchar](100) NULL,
[Loss] [money] NULL CONSTRAINT [DF_Revised_MainTable_Loss] DEFAULT (0.0000),
[Loss Type] [nvarchar](50) NULL,
[Employee] [ntext] NULL,
[Guest] [ntext] NULL,
[Action] [nvarchar](50) NULL,
[Action Type] [nvarchar](50) NULL,
[Notes] [ntext] NULL,
[Security/GC] [nvarchar](50) NULL CONSTRAINT [DF_Revised_MainTable_Security/GC] DEFAULT (N'GC'),
CONSTRAINT [PK_Revised_MainTable] PRIMARY KEY CLUSTERED
(
View 14 Replies
View Related
Jun 5, 2006
We are attempting to rollout a name and address system to 10,000 users who will use an application connected to an MSDE database.
We are using transactional replication to distribute data updates to them. Clients are connecting via the On-Idle feature of Synchronization Manager to grab transactions.
Server spec:
Network card: 1GB
Processors: 2* Xeon 3.2Ghz
Server spec: DL380 2Gb memory
Concurrent connections set to: 600
Disc: RAID 10 with 6400 controller
We are not using hyper-threading.
So far we have rolled the system out to 3500 subscribers, 500 per day.
Each day a subsciber will receive at least 400 transactions and 5000 commands.
Latency is 6 seconds, delivery rate 180 commands per second at less busy times.
Latency is 14 seconds, delivery rate 127 commands a second at busy times.
I have seen it get as slow as 0.04 commands a seconds at busy times.
The server becomes incredibly slow when there are more than 50 concurrent connections.
We are seeing 100 CPU for most of the day as clients connect to the distributor at various times. Lunchtime is particularly busy when people go to lunch, leaving their machines idle. We see lots of "time-outs" and "unable to connect to distributor" messages on the replication monitor during peak times.
What can we do to improve the performance of the distribution server?
Are we being over-ambitious by selection SQL Replication for this scenario?
Thank you for any help!
Best wishes
Julian
View 1 Replies
View Related
Apr 1, 2008
Hello all,
We have been trying now for the past 2 days to import data from a flat file to sql server database but with no luck.
The real issue here is that one of the field names has a very long value.
As a result, the import fails because it is unable to truncate the value.
We really don't want the value truncated but we have not been able to import the entire data file.
We have used nvarchar(max) but it doesn't work.
Can someone please let me know if you have encountered this type of issue and how was it resolved?
Thanks in advance.
View 12 Replies
View Related
Jan 23, 2007
So here's the issue
16 flat files all fixed width. Some over 350 columns.
Open flat file 1
extract id and go see if its in table 1, if true update table 1 with first 30 columns
otherwise insert into table 1 first 30 columns.
goto table 2, lookup id, insert/update next 30 columns...etc..etc..for 10 different tables
So I've got my flat file source, I do a derived column to convert the dates, i've got a lookup for table 1, then 2 ole db commands, 1 for update if lookup successful, 1 for insert if lookup fails.
How can I pass the id as a param into the update command so it updates where x = 'x'
also I need a pointer on doing the next lookup, eg table 2, would I do this as some sort of loop?.
If you can help great, but, please don't just reply with "I'd use this object"...then no explanation of how
v.v.frustrated newbie to SSIS
View 8 Replies
View Related
Apr 3, 2006
Fellow database developers,I would like to draw on your experience with views. I have a databasethat includes many views. Sometimes, views contains other views, andthose views in turn may contain views. In fact, I have some views inmy database that are a product of nested views of up to 6 levels deep!The reason we did this was.1. Object-oriented in nature. Makes it easy to work with them.2. Changing an underlying view (adding new fields, removing etc),automatically the higher up views inherit this new information. Thismake maintenance very easy.3. These nested views are only ever used for the reporting side of ourapplication, not for the day-to-day database use by the application.We use Crystal Reports and Crystal is smart enough (can't believe Ijust said that about Crystal) to only pull back the fields that arebeing accessed by the report. In other words, Crystal will issue aSelect field1, field2, field3 from ReportingView Where .... eventhough "ReportingView" contains a long list of fields.Problems I can see.1. Parent views generally use "Select * From childview". This meansthat we have to execute a "sp_refreshview" command against all viewswhenever child views are altered.2. Parent views return a lot of information that isn't necessarilyused.3. Makes it harder to track down exactly where the information iscoming from. You have to drill right through to the child view to seethe raw table joins etc.Does anyone have any comments on this database design? I would love tohear your opinions and tales from the trenches.Best regards,Rod.
View 15 Replies
View Related
Sep 6, 2007
Which is more efficient? One large view that joins >=10 tables, or a few smaller views that join only the tables needed for individual pages?
View 1 Replies
View Related
Jun 28, 2007
Hello.
Newbie here. I've only been using SQL for about a year now and have some minor questions about sql objects that reference other objects.
We have some views which reference other views in the joins. I will call one the primary view and the one being referenced in the joins as the secondary view.
Recently we made changes to the secondary view.
After which the primary views which referenced it would not work because of this change and had to be 'refreshed' by using drop/create scripts which essentially just dropped it and recreated the exact same view. I do not recall the exact error message that was returned other than it seemed to suggest that it could no longer see the secondary view since it had been changed. Nothing in the primary view was changed in any way, just the secondary.
Some here where I work have suggested off hand that this was a recompile of the primary view because the contents of the secondary changed.
My questions are:
1. Exactly why did this happen and is there a proper name for it when it does?
2. The same problem does not seem to occur when we have stored procedures referencing views in the joins which had just been changed. Why is that?
Thanks for any help on the matter. I greatly appreciate it.
View 3 Replies
View Related
Feb 22, 2007
Hello,
to make a report easier I'm developing it using a view of joined views of joined views.
Is there any significant performance penalty as opposed to just having one big select?
Cheers.
View 1 Replies
View Related
Mar 14, 2006
Hello There,I'm trying to create a view that has calculations dependent oncalculations, where the problem resides is that each time I make acalculation I must create an intermediate view so I can reference aprevious calculation.for example lets say I have my_table that has columns a & b. now I wanta view that has a & b, c = a + b, and d = c + 1.this is grossly simplified, the calculations I actually use are fairlycomplex and copying / pasting them is out of the question.so what I have is my_view_a which makes column c, and my my_view_finalwhich makes column d (however, in my real application I have 5 of theseviews, a/b/c/d/e/)is there anyway I can consolidate all these views into one? I wasthinking of using a stored procedure with temp tables or somethingalong those lines.I just which I can use the aliases that I create for c in d in onestep.any insight would be greatly appreciated.
View 5 Replies
View Related
Apr 15, 2008
Hi everyone, I have a problem like this . I have tables Coursegroupcode, which has groupname, codeI have Courses That has Coursename, its code(group code),Term, Course Number Enrollment table which has Foreign keys Term,Course NUmber , SSN I need to get a view like thisI should list all the coursecodes and people enrolled for each course code for selected terms Course Table Primary keys(TERM,COUSE Number)Enrollment Table(Foreign keys) TERM ,COURSE NUMBER, SSNplease help
View 9 Replies
View Related
Apr 7, 2005
Hi!
I need to know if i can build an aspx file on top of an sql view? I
can only see my tables when i connect to the database....
Collette.
View 3 Replies
View Related
Oct 18, 2005
Can SQL Views insert to the tables they are created from?I have a database that is not well structured. It has a lot of redundency. What I want to do is create a SQL View that brings in all the data I need and have my application use that new SQL View instead of the data tables. Then I want to be able to insert new information to a SQL View that actualy gets inserted into the tables that the SQL Viewer is created from.Can this be done?Does this make any sense?
View 1 Replies
View Related
Mar 26, 2001
I have a query which unions the four select statements.....
the select statements are joined with other tables and views.....
When i execute the query i get ODBC timeout error........
But the strange thing is that if i execute the view individually once and again execute the query it works fine.......and later it justs works fine....
Can anyone tell why is it like that.......
Thanks,
Sajai.
View 1 Replies
View Related
May 23, 2000
Is there a performance hit running a sp against a view versus a base table. The view just excludes
several of the records based on some criteria, and all the data I will be retreiving is included in
the view. Or should I just stick my criteria in the SP to exclude the data?
View 1 Replies
View Related
Jun 19, 2000
Is there a way to imbed "iif"-like logic in a SQL view? "case when" works in regular queries, but apparently is not supported in views...
View 1 Replies
View Related
Aug 14, 2002
I have some rather complex views to work with. Do they slow you down? Would it be better to move a view into a stored procedure? Is there any difference between these three solutions?
<B>1.Using views:</b>
Create view X
As
Select Col1, Col2, Col3 from Table1
Go
Create view Y
As
Select Col1, Col2, Col3 from Table2
Go
Create proc Z
As
Select X.Col2, X.Col3, Y.Col2, Y.Col3
From X inner join Y on X.Col1 = X.Col1
GO
<b>2.Using just a stored proc:</b>
Create proc Z
As
Select X.Col2, X.Col3, Y.Col2, Y.Col3
from
(Select Col1, Col2, Col3 from Table1) X inner join
(Select Col1, Col2, Col3 from Table2) Y on X.Col1 = Y.Col1
<b>.Joining tables</b>
Create proc Z
As
Select Table1.Col2, Table1.Col3, Table2.Col1, Table2.Col3
FromTable1 inner join Table2 on Table1.Col1 = Table2.Col1
go
Thank you!
View 1 Replies
View Related
Aug 1, 2001
I am struggling with setting up views in SQL Server 7.0 and 2000. What types of views are available.
I have one database with 112 tables and 2308 fields.
Thanks in Advance
View 1 Replies
View Related
Jun 9, 2004
I have created a database connected to a GUI using VB 6.0. When editing the views from the front end, do the tables also get updated
Thanks
View 3 Replies
View Related
Aug 17, 2004
Hi,
Is there a way to create a view with read only option. i.e the view should not allow the user to perform insert, update or delete action on it.
Please advise,
Thanks,
Sam
View 1 Replies
View Related
Dec 23, 2004
Is there any kind of lock on a view in ms sql database?
what happens if one of my users is looking at the view and another one is adding something to the database?
Thanks.
View 3 Replies
View Related
Sep 26, 2005
My First time building "Views" in SQL...... I'm trying to figure out how to return a 1 instead of a two when I Count the number of records that return inn my view.
here's what I have;
SELECT TOP 100 PERCENT dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate,
COUNT(dbo.tbl_ProcTimesheet.TransAmt) AS Shifts, dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.YCode,
dbo.tbl_ProcTimesheet.XCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink
FROM dbo.tbl_ProcTimesheet INNER JOIN
dbo.tbl_SysVarRestEeShiftPayCodes ON dbo.tbl_ProcTimesheet.Code = dbo.tbl_SysVarRestEeShiftPayCodes.PayCode INNER JOIN
dbo.tbl_SysVarRestEeShiftRules ON dbo.tbl_ProcTimesheet.YCode = dbo.tbl_SysVarRestEeShiftRules.YCode AND
dbo.tbl_ProcTimesheet.ZCode = dbo.tbl_SysVarRestEeShiftRules.ZCode
WHERE (dbo.tbl_ProcTimesheet.Sequence <> N'0') AND (dbo.tbl_ProcTimesheet.Week < 3)
GROUP BY dbo.tbl_ProcTimesheet.idCalendar, dbo.tbl_ProcTimesheet.erNum, dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.TransAmt,
dbo.tbl_SysVarRestEeShiftRules.DailyHours, dbo.tbl_ProcTimesheet.[Employee Number], dbo.tbl_ProcTimesheet.XCode,
dbo.tbl_ProcTimesheet.YCode, dbo.tbl_ProcTimesheet.ZCode, dbo.tbl_ProcTimesheet.eeLink, dbo.tbl_ProcTimesheet.Sequence,
dbo.tbl_ProcTimesheet.Week
HAVING ('IIf' > '1,1,0') AND ('if' > '1,1') AND (dbo.tbl_ProcTimesheet.erNum LIKE N'5648 ') AND (SUM(dbo.tbl_ProcTimesheet.TransAmt)
>= dbo.tbl_SysVarRestEeShiftRules.DailyHours) AND (dbo.tbl_ProcTimesheet.PayDate = CONVERT(DATETIME, '2005-09-06 00:00:00', 102)) AND
(COUNT(dbo.tbl_ProcTimesheet.TransAmt) > 0)
ORDER BY dbo.tbl_ProcTimesheet.PayDate, dbo.tbl_ProcTimesheet.[Employee Number]
When it counts shifts, I only want to return a maximum of i, as in either you had a shoft that day, or not. I gave up trying to figure out the "IF" string in SQL, and for the life of me I can not figure this out.
Does anyone know a good site with info on how to construct an SQL View?
View 12 Replies
View Related
Jun 1, 2004
Hi,
I'm creating a stored procedure that will Drop all views when executed, this is what i have...
DECLARE crsViews CURSOR FOR
SELECT
name AS strViews
FROM
__________ <what do i put here?
WHERE
type ='U'
AND NAME LIKE'V%'
Thanks for any help!
View 2 Replies
View Related
Apr 21, 2008
MS SQL Server 2005
What happens if 2 or more users use the same View at the same time or while other user is using it, normally from Crystal Reports 10
It rebuilds again? Or?
Thanks
View 2 Replies
View Related
Apr 25, 2008
Hi,
I have a SQL server: SQL2005
2 databases
db_repl: contains replicated data from mainframe
db_my: my database
db_my.vwRepl_WorkOrders: Select 1,2,3 from db_repl.dbo.WorkOrders
db_my.vwWorkOrdersOpen: select 1,2,3 from db_my.vwRepl_WorkOrders where Status=1
which approach is more optimal to do in order to find open work orders from Texas:
[select 1,2,3
from db_repl.dbo.workOrders where Status=1 and State=1
or
select 1,2,3
from db_my.vwWorkOrdersOpenwhere where State=1]
Is it best practice to use 'subviews' that acess db_repl directly or acess views in my_db that acess db_repl (2 hops or more if views call other views that call other views) Answer might be the obvious but just in case I am missing something here .
Thanks!
View 2 Replies
View Related
Apr 17, 2007
Can someone tell me when I would get an error like the one below:
"Update or insert of view or function failed because it contains a derived or constant field."
Here is how I create a VIEW
CREATE VIEW vSample
AS
SELECT ilm.ITEM_ID,
CAST (ilm.CURRENT_QUANTITY AS INT) CURRENT_QUANTITY
FROM SAMPLE_1 AS ilm INNER_JOIN SAMPLE_2 AS ilm2
on ilm.ID = ilm2.ID
Now, I am trying to update data using the view
UPDATE [vSample] SET [CURRENT_QUANTITY] = 598.00 WHERE (1 = 1)
I have tried casting the number 598.00 as int and no luck.
Can someone please help or guide me on this problem?
Thanks,
Javid
View 4 Replies
View Related
Nov 6, 2007
Hello,
I am kind of new to using views. What types of queries can i use with view (i.e. select, insert, update, delete) in mssql 2000?
DigitalSolvers
Yahoo: DigitalSolvers
MSN Live: lew26@msn.com
Skype: DigitalSolvers
View 4 Replies
View Related
Nov 17, 2007
I've created views ProductsSold that will list all the products- ProductID and quantity, that were ordered (OrderDate) in last twenty four hours between getdate()-1 and getdate().
create view ProductsSold
as
select orderdetails.ProductID, orderdetails.OrderQty, orders.OrderDate
from OrderDetails inner join Orders
on OrderDetails.salesOrderID = Orders.salesOrderID
where OrderDate > dateadd(d, -1, getdate()) and OrderDate < getdate()
i just wonder if there is a more simpler way to query it?
View 17 Replies
View Related
Sep 11, 2006
Hi all,We have a big table connected to a web server, and I want the number ofrows to be limited that get returned.So I could do something like this:select top 10 *from objectorder by codeI then realised looking at the help file, because the "order by" clauseis set the SQL Server has to build the complete dataset, do the orderby and then filter it to the first ten rows.So I was wondering if I put the query into a view like so :create view vw_object asselect top 10 *from objectThen ran the query like so:select *from vw_objectorder by codewould the SQL server just get the top 10 rows from the view first, thenapply other order by on it afterwards?
View 7 Replies
View Related
