Does anyone know how to count rows of data from 1 table that meet one or two different criteria and then get the probability of occurance for that criteria ... as an output column?
Do I use 'Having' or temp tables or Views?
Here is my output for now. I am trying to solve for 'Prob' - everything else works on it's own.
Craig
SELECT Wins_Lng/Trades_Lng as Prob
FROM Transactions
SELECT Count(Ticker) as Trades_Lng
FROM Transactions
WHERE TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND TransKind='B' (SELECT COUNT(Ticker)as Wins_Lng
FROM TransactionsWHERE Transkind='B' AND TransType='C' AND DateDiff(day,BaseDate,GetDate())<=100 AND Profit_Lng>=0)
I have an Epicor ERP system that enables writing 'queries' in its internal format, that can see Views in the MSSQL database. Very useful, as I can write view definitions with much more flexibility than the ERP tools. As far as I can tell the ERP DB interface can't call SQL procedures.
What I want to to is to pass a value from the ERP engine that the View can use to restrict record selection. The View query produces a field that describes how far down into a nested parts list structure it has got, so the data is not in the database, it is calculated by the query.
A user may wish to restrict the depth of descent to, say 3 levels. I can filter the results set returned by the view in today's solution to only show levels up to 3, but the View still processes all levels (some 12 million records possible return) before the filtering can be applied. SLOW...
So I want to pass a parameter (if that is the right jargon) into the View to limit how much work it will do.
I have just transferred my site to a new server with SBS R2 Premium, so the site's database changed from SQL 2000 to SQL 2005. I find that searches are now returning results in random order, even though they use a view with an Order By clause to force the order I want. I find that the results are unordered when I test the view with Management Studio, so the issue is unrelated to my VB/ASP Net code. Using my SQL update tool (SQL Compare, from Redgate) I find that there are no differences in the views, or the underlying tables. Using Management Studio to test a number of views, I find that I have a general problem with all views. For example, one of the simpler views is simply a selection of fields from one table, with an Order By clause on the tables primary key: - SELECT TOP (100) PERCENT GDQid, GDQUser, GDQGED, GDQOption, gdqTotalLines, GDQTotalIndi, GDQRestart, GDQCheckpointMessage, GDQStarted, GDQFinished, gdqCheckpointRecordCountr FROM dbo.GEDQueue ORDER BY GDQid DESC If I right-click the view (from Management Studio's Object Explorer pane), select Design from the menu to show the view's design, and then click the Execute SQL icon, the view's results are displayed perfectly, in descending order of GDQid. However, if I select "Open View" the view's results are displayed out of order. When I do this with the SQL 2000 database, both Design/Execute and Open View correctly display the data in the correct order. Is there something that I should check in the SQL 2005 installation - some option that has been set incorrectly? Regards, Robert Barnes
The data I am pulling is correct I just cant figure out how to order by the last 8 numbers that is my NUMBER column. I tried adding FOR XML AUTO to my last line in my query: From AP_DETAIL_REG where AP_BATCH_ID = 1212 and NUMBER is not null order by NUMBER FOR XML AUTO) as Temp(DATA) where DATA is not null
but no change same error. Output: 1234567890000043321092513 00050020
Select DATA from( select '12345678'+ left( '0', 10-len(cast ( CONVERT(int,( INV_AMT *100)) as varchar))) + cast (CONVERT(int,(INV_AMT*100)) as varchar) + left('0',2-len(CAST (MONTH(DATE) as varchar(2))))+ CAST (MONTH(DATE) as varchar(2)) + left('0',2-len(CAST (day(CHECK_DATE) as varchar(2)))) + CAST (day(DATE) as varchar(2))+right(cast (year(DATE)
I'd like to write a query that lists all the views for a particular database, can anyone help me out with this? I've played around with the information_schema.views that the online books references, but I can't get it to return a list of the views? I know I can just look at the views through enterprise manager, but I need to do this through a query. Many thanks
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.
I have a table which records the amount of times a user views a webpage
tblpageview
id pagename view_date userid
I would like to have a query that returns the unique pagename and the amount of times that page has been viewed. I've had a go with count etc. but cant seem to get it. Anyone can help?
Client database has a complex view with eight nested subqueries used to return "dashboard" information. The application code uses NHibernate to call and filter the view with three parameters, one of which is the CustomerID.
A certain customer, (the biggest client), has more than ten times the number of records of the next largest customer.
Occasionally, the database reaches a state where when this particular customer tries to run the dashboard view, the application times out.
If I open up the view and re-save it, all is well again for a few days.
What gives?
Views are supposedly not pre-compiled, though I know that 2000 stores bits and pieces of query plans.
Any ideas on what causes this and what to do about it?
We know that a query execution plan exists for Stored Procedures in the Procedure Cache.
What about Views? Does a view have a query execution plan? We know that a View is a virtual table and that virtual table is populated when the view is induced but does it have a Query Execution Plan?
I have tried to find this info for Views in BOL but I cannot see it anywher in BOL.
Ok, What I want to achieve is plain stuff, I want to join 2 views on a common key.It all works well with the SQL2000 Query Analyzer, but not trough ADO.NET or should I say my webapplication.With that I mean that my query return rows when executed from SQL2000 Query Analyzer, But not when used in my application or Executed from the Visual Studio Server Explorer.I have struggled with this one for several hours, I cant get this one right.So lets bring in the one who actually know what his doing View1: 1 select 2 cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2)) AS Rabatt 3 ,cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2)) AS MVA 4 ,cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2)) AS Belop 5 ,PS.* 6 ,K.Kunde_ID 7 FROM 8 tbl_ProduktSalg AS PS 9 INNER JOIN 10 tbl_Ordre AS O 11 ON 12 O.Ordre_ID = PS.Ordre_ID 13 INNER JOIN 14 tbl_Kunde AS K 15 ON 16 K.Kunde_ID = O.Kunde_IDView2: 1 SELECT 2 PS.Ordre_ID 3 ,SUM(cast((PS.RabattProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalRabatt 4 ,SUM(cast(PS.Antall * ((PS.Pris*(100-PS.RabattProsent))/100)*((PS.MvaProsent/100.00)+1) AS decimal(11,2))) AS TotalBelop 5 ,SUM(PS.Pris) AS TotalPris 6 ,SUM(cast((PS.MVAProsent/100.00)*PS.Pris AS decimal(11,2))) AS TotalMVA 7 FROM 8 tbl_ProduktSalg AS PS 9 GROUP BY 10 PS.Ordre_ID MyQuery/SPRC: 1 create procedure %PROC% (@Kunde_ID int, @Ordre_ID int) 2 as 3 begin 4 SELECT 5 v_PSD.* 6 ,v_OTS.TotalRabatt 7 ,v_OTS.TotalBelop 8 ,v_OTS.TotalPris 9 ,v_OTS.TotalMVA 10 FROM 11 v_ProduktSalgDetaljer AS v_PSD 12 INNER JOIN 13 v_OrdreTotalSum AS v_OTS 14 ON 15 v_OTS.Ordre_ID = v_PSD.Ordre_ID 16 WHERE 17 v_PSD.Kunde_ID = @Kunde_ID 18 AND 19 v_PSD.Ordre_ID = @Ordre_ID 20 21 end 22
When working with databases containing myriad of huge tables, I am very much tempted to create categorized views on those tables in order to simplify and facilitate data query programming? Some developers I talk to say such views generally slow down query performance. Is this true? Thanks.
Over the years I've read and experienced where joining more then 5 tables can lead to performance problems. This number can vary based upon the amount of data in each table, if and how indexes are used and the complexity of the query, but 5 has always been a good rule of thumb. Unfortunately I do not know what rule to apply in regards to joing views.
A developer has experienced timeout problems periodically when opening a view in EM or when running the code which makes-up the view. I decided to look at the view and noticed it references tables and views, which reference more views, which in turn reference other views. In all the initial view references 5 tables and 8 views directly and indirectly, with some of the views containing function calls. What are your thoughts on how many views and tables are too many when it comes to joins and query performance.
I've just installed SQL Server 2005 Developer Edition, upgraded from SQL Server 2000, and have encountered a problem in trying to query against the System Catalogs.
I've tried SELECT * FROM sys.objects in addition to SELECT suser_name( role_principal_id ) FROM sys.server_role_members. In both cases I receive the following error: Invalid object name 'sys.objects' or 'sys.server_role_members' in the second example.
I changed the properties of the registration for my local db instance to use SQL authentication in lieu of Windows authentication. I used the sa login account to see if that made a difference, no go.
Can someone *please* tell me what I'm doing wrong here?
I would also like to know if it's possible to run a query confirming the user account I'm logged in to the system as.
I am building a search application that has several fields in it - I want to be able to allow the user to put in specific search criteria or all them to say "All" and then pull back any records with anything in that row. Example Select *From mytableWHERE (SDate >= 1-1-2007) AND (EDate <= 1-25-2007) AND (Location = "ALL" or In ALL) this is where I am cloudy.. What/How would I write in the query to pull back everything, I know I could just leave the Location out of the query all together, but then that does not allow them to select just one the next time they run the application. Thoughts? Thanks,Ad.
Hi everyone, I am trying to run this query and it is not returning a single row. Although I can see one row with this 10/14/2005 date in the table.SELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate >= '10/14/2005' and dtInsertDate <='10/14/2005'I also tried to rewrite the query this waySELECT iSourceId, UserId,FROM tblEmployeeWHERE dtInsertDate = '10/14/2005' but still it is not returning a single row.Please let me know what am I doing wrong.Thanks.
SELECT t1.fname, t1.lname, t2.district_name, t2.district_number FROM t1 AS table1, t2 AS table2 WHERE t2.district_name LIKE 'S%'
i have these values in the table
table1
fname lname mike jackson roy mires
table2
district_name district_number South 123 Daggerty 7845 duffel 7224 rubble 7545
Now the query is dynamic (the letter is that the like clause is run against)
When the letter D is searched for i get the 2 colums duffel, daggerty BUT when S is searched against I get nothing..
I am confused as to why, It doesn't seem to be case sensitive, as the 2 colums duffel and Daggerty 1 is d is in lowercase and the other is in uppercase. andive tries both lowercase s and uppercase S and still got nothing.
Is tehre a better way to use the LIKE clause? Ive looked and looked for documentation about the LIKE clause but I cannot find anything
How can I get all names that start with "sci" or "eng" without using the OR clause. In other words, how can I modify the following statement so I don't use the OR clause: select * from course where (name like 'sci%' or name like 'eng%')
Yes, I do know what this means and why the error is thrown but this is not my question.
I have two servers that are both running Windows Server 2003 and SQL Server 200 SP3. Below are the results from both servers using @@version
Sever 1 (BB)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
Server 2 (Genesis)
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
These servers are identical or so it seems. I've got a real ugly query that uses views and a derived table to get results. The problem is the 256 limit message only comes up on one server and on the other (Genesis) the query runs fine. I get the error though it reads a 260 limit on a box with SP4 applied. I've also run the query on a box that is Windows 2003, sql2k and sp4 and the query runs but not on a similar server here. This is all very odd. Please note that the database structure, views, etc are all exactly the same as far as I know.
Any suggestions? There seems to be no pattern between versions of Windows and/or SP levels.
I have come across this problem with SQL server both on 2000 and 2005. I am stating an example here.
I have two partitioned tables and a view on top of both tables as below: create table [dbo].[Table_1] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1 AND 999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO
create table [dbo].[Table_2] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK NOT FOR REPLICATION ([TableID] BETWEEN 1000 AND 1999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO create view TableView as select * from Table_1 union all select * from Table_2 GO
Note the NOT FOR REPLICATION clause on the check constraint on the TableID column.
I then ran the query execution plan for the following query on both SQL server 2000 and 2005. select * from TableView where TableID = 10
On both the versions the execution plan shows and Index seek on both the tables in the view. This means that my partitioning is not working. If I remove the primary key constraint from the TableID column, the same query on the view shows a table scan on all the underlying tables. This is even worse.
Next, create the same tables and views again, now without the NOT FOR REPLICATION clause on the check constraint as show below: create table [dbo].[Table_1] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK ([TableID] BETWEEN 1 AND 999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO
create table [dbo].[Table_2] ( [TableID] INTEGER PRIMARY KEY NONCLUSTERED CHECK ([TableID] BETWEEN 1000 AND 1999), [AnyOtherColumn] int NOT NULL , ) ON [Primary] GO
create view TableView as select * from Table_1 union all select * from Table_2 GO
Now run the query execution plan for the same query again.
select * from TableView where TableID = 10
This time you would see that it does an index scan only on the first parititon table. This time it proves that the partitioning works.
I would like to know why does the NOT FOR REPLICATION clause in the check constraint make such a huge difference?
I want to export an SQL Server table to an Excel Spreadsheet driven by a web interface. I am using Cold Fusion to call a SQL Server Stored procedure. The SP accepts a variable (IDlist) from the web page and sets this to a Global Variable.
The SP then executes a DTS package to export to Excel. The DTS package uses the Global variable in the SQL Query thus:
SELECT ... FROM ... WHERE tblPropertyRegister.IDProperty IN (?);
This works fine when I pass one single ID (@outIDlist = "20") into the stored procedure. But it returns no records when I pass multiple IDs (@outIDlist = "19, 20, 21") into the stored procedure. It works fine also if I "hard code" the IDlist into the DTS query (eg WHERE tblPropertyRegister.IDProperty IN (19, 20, 21);). The problem appears to be in the setting of the global variable in the stored procedure.
Has anyone had any experience with this? Any feed back would be greatly appreciated. TIA
sSQL = "SELECT VIN, Year, MakeID AS 'Make', ModelID AS 'Model', Name AS 'Dealer', PhoneOne AS 'Phone', StockDate AS 'Stock Date', SoldDate AS 'Sold Date', RepairCost AS 'Repair Cost' FROM " sSQL = sSQL & sView sSQL = sSQL & " WHERE VIN = '" & sVIN & "'"
The error I got is invalid column 'MakeID' and invalid column 'ModelID'. I'm not familiar with the term "AS" in SQL so can anyone explain what's the problem here?
I've put together the following query, but it has been unsuccessful running it so far.
Code:
select * from Contractors.dbo.Contacts where VendorNo in (select No_ from [BMIS Live Database].dbo.[BMIS Live Database$Vendor] where [Name] like '%of%')
I receive the following error when I run it in Query Analyzer:
Code:
Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation.
I'm trying to grab values from the column, "No_" in the Bmis.. database then only select data from the Contractors database if the VendorNo column holds one of the values grabbed from the Bmis.. database. I think my syntax is just completely wrong but hopefully someone might pick up on my mistakes. Any help would be greatly appreciated, thanks!
This is my queryselect ano,max(date),a_subject from MY_TAB where table_name='xyz' andano=877group by a_subject,ano order by a_subjectANOmax(Date)A_Subject8772005-01-20 00:00:00.000Subject_18771900-01-01 00:00:00.000Subject_28772004-12-20 00:00:00.000Subject_38772005-01-19 00:00:00.000Subject_4--------------------------------------------------------------------------When I put the status column in, it fetches all the rows.select ano,max(date),a_subject,status from MY_TAB wheretable_name='xyz' and ano=877 group by a_subject,ano,status order bya_subjectANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8771900-01-01 00:00:00.000Subject_3Not Started8771900-01-01 00:00:00.000Subject_4Not Started8772005-01-19 00:00:00.000Subject_4Not Started-----------------------------------------------------------------------now what i want isANOmax(Date)A_SubjectStatus8772005-01-20 00:00:00.000Subject_1Not Started8771900-01-01 00:00:00.000Subject_2Not Started8772004-12-20 00:00:00.000Subject_3Completed8772005-01-19 00:00:00.000Subject_4Not StartedThanks a lot for your help.AJ
OK ... I am using UPS Worldship that issues an ODBC query to my MS2Kserver ... Worldship can query either a table or a view and retreiveshipping info for a supplied orderid.I need to create a DB table that will track the orderids requestedfrom Worldship so that I can stop doubleships. That is to set up afunction to allow the info to be sent only once to worldship.I need to execute a stored procedure to write to a table and enforcebiz logic.So .. I've created a view that Worldship can execute an ODBC queryagainst (v_upsPull) ... in which I guess the query issued will belike: SELECT * FROM v_upsPull WHERE orderid = 123456The view is:CREATE VIEW dbo.v_upsPullASSELECT * FROM OPENROWSET ( 'SQLOLEDB', '[db]'; '[user]'; '[password]','exec sp_ups_pull')When the ODBC query calls the view the sp_ups_pull store procedurer isexecuted.However ... I do not have access to the original Where clause in theODBC query in the stored procedurer.Is there a way I can get access to the ODBC Where clause and pass itinto the stored procedurer?If not is there some other way I can create a DB table and run aselect against it ... based on the Worldship query?
HiI want a simple select query on a column-name (smalldatetime) withvalues dislayed in desc order with null values FIRST.i.e.Select orderdate from ordersorder by ( null values first and then orderdate in desc order)could any one please helpThanks
I have a problem. My provider(ISP) is supporting SQL Native Client driver and my forum supplier is only supporting SQLOLEDB. I am trying to access our sql2005 DB located at our ISP.
I have changed this line: strCon = "Provider=SQLOLEDB;Connection Timeout=90;" & strCon To: strCon = "Driver={SQL Native Client};Connection Timeout=90;" & strCon
Now I can access the database, but when I am trying to loging I get this error: Server Error in Forum Application An error has occured while writing to the database. Please contact the forum administrator.
Support Error Code:- err_SQLServer_loginUser()_update_USR_Code File Name:- functions_login.asp
Error details:- Microsoft OLE DB Provider for ODBC Drivers Query cannot be updated because the FROM clause is not a single simple table name.
What can I do?? I am stuck in between and need a solution.....
I've been using partitioned views in the past and used the check constraint in the source tables to make sure the only the table with the condition in the where clause on the view was used. In SQL Server 2012 this was working just fine (I had to do some tricks to suppress parameter sniffing, but it was working correct after doing that). Now I've been installing SQL Server 2014 Developer and used exactly the same logic and in the actual query plan it is still using the other tables. I've tried the following things to avoid this:
- OPTION (RECOMPILE) - Using dynamic SQL to pass the parameter value as a static string to avoid sniffing.
To explain wat I'm doing is this:
1. I have 3 servers with the same source tables, the only difference in the tables is one column with the server name. 2. I've created a CHECK CONSTRAINT on the server name column on each server. 3. On one of the three server (in my case server 3) I've setup linked server connections to Server 1 and 2. 4. On Server 3 I've created a partioned view that is build up like this:
SELECT * FROM [server1].[database].[dbo].[table] UNION ALL SELECT * FROM [server2].[database].[dbo].[table] UNION ALL SELECT * FROM [server3].[database].[dbo].[table]5. To query the partioned view I use a query like this:
SELECT * FROM [database].[dbo].[partioned_view_name] WHERE [server_name] = 'Server2'
Now when I look at the execution plan on the 2014 environment it is still using all the servers instead of just Server2 like it should be. The strange thing is that SQL 2008 and 2012 are working just fine but 2014 seems not to use the correct plan.
Table:ColumnsUsersList:UserID, UserName, Country I need a query which select all the rows from the above mentioned table with all fieldsButThe order the rows is First all the users from "Pakistan"Second all the users from rest of the countries except "Pakistan" in ascending order So the query first return all the users from Pakistan and the the users from rest of the world in ascending order. Forexample, 1, ABC, USA2, XYZ, Saudi Arabia3, LMN, Pakistan4, TQR, India5, PTR, Afghanistan then the query returns. 3, LMN, Pakistan5, PTR, Afghanistan4, TQR, India2, XYZ, Saudi Arabia1, ABC, USA
