Why does this not work? When I run this query in Query Analyzer, I get the error "Incorrect syntax near the keyword 'UNION'." This seems simple enough...
SELECT * FROM SalesLead WHERE Age = '50' ORDER BY FirstName
UNION ALL
SELECT * FROM SalesLead WHERE Age = '60' ORDER BY FirstName
Thanks for any advice
Aaron
I have two tables one with current data and a second with the same design that holds purged history.
I was going to create view and then jsut use a where clause to filter both tables but I figured it would be faster if the where clause was passed into each query as opposed to the whoel view having to load and then be filtered.
Select PatientName, MemberId
FROM CLAIMS
WHERE MemberID = @MemberID
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
WHERE MemberID = @MemberID
But it appears that if there are no records in the first statement that the whole thing fails. I tried the union all operator with out any luck either.
Now if had a view like this;
Create view vAllClaims as
Select PatientName, MemberId
FROM CLAIMS
UNION
Select PatientName, MemberId
FROM CLAIMSHistory
And said select * from vAllClaims where memberid = 12345 would the query optimizer put the build the where statement onto each subquery or pull all the data first and query it?
I want to start off by saying I am lacking when it comes to an indepth knowledge of databases and programming. I contracted with a major company to program a site that has major social networking components to it and other higher level applications. The site was suppossed to be built to handle thousands and eventually millions of people. We already have thousands of people. I recently found out that they used a MS SQL DB in conjunction with an ACCESS DB. I have had conversations with 3 other developers and they cannot come up with a reason why this company would have done this. This is a major company who has years of experience. I have to believe there was some advantage to doing it this way. So far I have not really seen any issues in the performance of it, but have not been able to get an answer to why they would have done it this way. Does anyone have any thoughts on this? It would be much appreciated. Thank you...
View 4 Replies View RelatedOK...
I have an sql statement that i have manually built as a string. normally i would just return a recordset with it using
Set @sqlBuild = 'SELECT * FROM ' + @Table + ' WHERE value = ' + @value
EXEC (sqlBuild)
something like that works fine...
however, i would like to know if i can instead of just executing the SQL statement use that dynamically built statement with a cursor somehow.
basically what i'm trying to do is loop through that result set with a cursor instead of just simply executing it.
Any suggestions?
I have a report (stored procedure) that I have set up in SQL 2005 Reporting Services. I've designed this report (using SQL server business intelligence studio), and several other reports, thinking that running totals or summing may be the issue, but it hasn't been. The latest iteration has been just a display of product info, about 100 records, no fields formatted or summed. Very very simple, straight-forward report. If I go to the Report server & upload the rdl file, it displays fine, performs as it should, paging & exporting - everything works fine. The issue comes up when in a web app, I put a Reportviewer control on the page, and call the report.
It works, sort of.
Originally, I had written the page using ASP.NET AJAX Enabled web project, and I had been using the Tab Container on the page. What happens is when I open it on that tab (I am using AJAX Tab panels, which had been working fine without this behavior prior to finally getting the reportviewer working), and the report displays, the "e" on Internet explorer at the top of the tab now flickers, like the page is reloading. It also runs the CPU up to 100% on the computer and although I can go from tab to tab in it (I am using AJAX tab panels in the page), it will take like up to a minute to go to the next tab. I'm not doing anything really data-intensive on those tabs, and they had been functioning fine prior to putting in the report viewer (i.e. they weren't flickering & clocking the CPU).
Thinking that the Tab Container may be the issue, I created just a plain AJAX Enabled web project and put the same reportviewer on it. Same performance. It'll display the report, and then take the system up to 100% and stay there until I kill the browser.
After that, I did just a plain old ASP.Net web project and put the report viewer control on it. Same result. The report will display, but as soon as it does, the "e" on Internet Explorer tab starts flickering and you see the CPU go to 100% and stay there. I've left it for 20-30 minutes with no change. It appears as if the page is constantly refreshing.
Thinking that the issue may be related to having the reportviewer report hard coded in the app, I put a button on the page, and assigned the button to put in the report. It displays, but it again runs the CPU up to 100% and stays there.
I thought that having Asynch = True (run the report asynchronously) might be the issue, but setting it to false made no difference.
I eventually have to kill the page to do anything, because it has the system up to 100%.
The code I am using on this page follows:
Here's the code in the codefile:
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Me.ReportViewer1.Visible = False
End If
ReportViewer1.ServerReport.ReportPath = ""
End Sub
Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
ReportViewer1.Visible = True
ReportViewer1.ServerReport.ReportPath = "/BA10listing"
End Sub
The code in the aspx part of the page:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %>
<%@ Register Assembly="Microsoft.ReportViewer.WebForms, Version=8.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a"
Namespace="Microsoft.Reporting.WebForms" TagPrefix="rsweb" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
<title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Button ID="Button1" runat="server" Text="Button" />
<rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt" Height="400px" ProcessingMode="Remote" Width="400px" AsyncRendering="False" ShowDocumentMapButton="False">
<ServerReport ReportPath="/BA10listing" ReportServerUrl="http://mycomputer/ReportServer$SQL2005/" />
</rsweb:ReportViewer>
</div>
</form>
</body>
</html>
.... so you can see there's a lot going on here. The report returns ~100 records or so, no calculations, no summation, no grand totals. Basically a "nothing" report, just listing product info data.
In SQL Reporting Services in the web browser, this report displays fine, no problems. It's only when I call it from the Reportviewer inside a web page that it hangs.
Any idea why reportviewer might make this act this way?
My system is running Windows XP, VS 2005, I have both SQL 2000 and SQL 2005 on this same box. I have a server that has both SQL 2000 & SQL 2005 on it as well, and the behavior is the same for both, whether I run the web page with the Reportviewer control on it with the report being on the local system, or the remote system.
Any help or advice would be very much appreciated.
Thanks!!!
SC
I know the answer must be somewhere, but for the moment every place I look ...
How many tables can be unioned together (I think I remember that 6.5 had a limit of 16) in SQL7?
Thanks,
Judith
hi
I have MSSQL query that performs multiple UNION ,but I would like to perform a GROUPBY on the whole result set.
How Can i do this?
plz help...
bono
Hi everyone. I was wondering if I could get some pointers in creating a union between two tables. This is the sproc I currently have:
CREATE Procedure spGetReturnCheckForCriteria
@SearchCriteria VARCHAR(8000),
@SortOrder VARCHAR(8000),
@PageSize INT
AS
-- Declare vars
DECLARE @SQLStatement NVARCHAR(4000)
DECLARE @bldSQLStatement VARCHAR(8000)
DECLARE @retValue INT
-- Initialize vars
SET @SQLStatement = ''
SET @bldSQLStatement = ''
SET @retValue = -1
-- Sanity Check(s)
IF (@PageSize IS NULL OR @PageSize < 1)
-- Paging Size can not be Null, nor less than One
BEGIN
SET @RetValue = -30 -- "Must have a valid Paging Size for pagination: Error (-30)
RETURN
END
-- Build the Paging SQL Statement
SET @bldSQLStatement = 'SELECT TOP '
-- Add the Page Size
SET @bldSQLStatement = @bldSQLStatement + CAST(@PageSize AS VARCHAR)
-- Add Columns/Tables/Relationships
SET @bldSQLStatement = @bldSQLStatement + '
ReturnCheck.ReturnCheckID AS ReturnCheckID,
ReturnCheck.FiscalNumber AS FiscalNumber,
ReturnCheck.ReturnedDate AS ReturnedDate,
ReturnCheck.CheckNumber AS CheckNumber,
ReturnCheck.AssessPenaltyIndicator AS AssessPenaltyIndicator,
ReturnCheck.ReturnCheckCollectionStatusCode AS ReturnCheckCollectionStatusCode,
ReturnCheck.ReturnCheckReasonCode AS ReturnCheckReasonCode,
ReturnCheck.CentralCollectionsID AS CentralCollectionsReferralNumber,
TaxPayment.PaymentID AS PaymentID,
TaxPayment.DocumentLocatorNumber AS DocumentLocatorNumber,
TaxPayment.PaymentEffectiveDate AS PaymentEffectiveDate,
TaxPayment.PaymentAmount AS PaymentAmount,
TaxPayment.PaymentQuarter AS PaymentQuarter,
TaxPayment.PaymentYear AS PaymentYear,
TaxPayment.InternalReferenceNumber AS InternalReferenceNumber,
TaxPayment.PaymentTypeCode AS PaymentTypeCode,
TaxPayment.PaymentOriginCode AS PaymentOriginCode,
TaxPayment.VoucherNumber AS VoucherNumber,
TaxPayment.ReversedIndicator AS ReversedIndicator,
TaxPayment.PaymentDate AS PaymentDate,
CAST(NULL AS DATETIME) AS CCReferralDate,
DistributionPoint.UIDPrime AS UIDPrime,
DistributionPoint.UIDCheck AS UIDCheck,
DistributionPoint.UIDDistPoint AS UIDDistPoint,
CASE
WHEN ReturnCheck.UpdatedDate IS NULL THEN ReturnCheck.CreatedDate
ELSE ReturnCheck.UpdatedDate
END AS ReturnCheckTimeStamp
FROM TaxPayment
INNER JOIN DistributionPoint
ON (TaxPayment.DistributionPointID = DistributionPoint.DistributionPointID)
INNER JOIN ReturnCheck
ON (TaxPayment.PaymentID = ReturnCheck.PaymentID)
'
-- Add Search Criteria
If (@SearchCriteria IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' WHERE ' + @SearchCriteria
-- Add Sort Order
IF (@SortOrder IS NOT NULL)
SET @bldSQLStatement = @bldSQLStatement + ' ' + @SortOrder
-- Set the SQLStatement
SET @SQLStatement = @bldSQLStatement
-- Execute the Paging Query
EXECUTE @retValue = sp_executeSQL @SQLStatement
GO
Look at the SQL build where I'm doing an INNER JOIN between TaxPayment and RefundCheck. Instead of this INNER JOIN, I'd like to do a union instead. If I can get any help on this I'd greatly appreciate it. Cheers.
I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
I doing a union of two select queries, and I keep getting
the following error:
syntax error converting the nvarchar value 'foo' to a column of data type int.
I've tried using CAST and CONVERT in the select statement, but it doesn't change the outcome.
The table it's complaining about (the one containing the value 'foo'), is of data type nVarChar, so I don't have any idea why SQL server would try to convert it to an integer.
Is this a common problem? I'd love to know what I'm doing wrong.
I was having a chat with a chap over lunch today and he asked if I knew of any performance issues when doing unions in stored procedures. I couldn't think of anything but he seemed sure there was.
Is there such an issue I've missed?
Mike
I'm creating a program that allows users to submit a report on equipment at regular intervals. If a piece of equipment has a problem, it is given a job entry that refers back to the report for various information.
However, there will be times when a problem is noticed, and someone wants to submit it immediately; these are made into extra jobs.
To this end, I have three tables:
Reports
Jobs
ExtraJobs
Because ExtraJobs cannot be associated with a report, they have their own table, which holds information that would otherwise be grabbed from both Reports and Jobs. While there are seperate submission forms for regular jobs and extra jobs, I would like them to appear on the same query result when a user looks at submitted jobs or reports.
What I'm currently trying to do is this:
Code:
SELECT*
FROMReports LEFT JOIN Jobs
ON Reports.reportID = Jobs.reportID
UNION ALL
SELECT ExtraJobs.*
FROM ExtraJobs
This won't work because the first half of the union has an extra column (reportID) that the second half does not. Is there any way to add in a value for that non-existant column (say, ExtraJobs.reportID = -1) to make sure that both sides have an equal number of columns?
If worst comes to worst, I could add a reportID column to ExtraJobs and have it set to -1 for everything, but I'd like to keep from adding fat, if at all possible.
How do I do this? I have two queries that create temp tables. I need to union them together and create one temp table. Anyone done this with success?
View 4 Replies View RelatedI have 4 archive tables and 1 active table that are created the same, but contain different data based on the date. I need to get results that have three columns: AuthorName, Month, Total. This is currently working, but through my research I can't find how to start going about dealing with the fact that each Author has some of his results from one month in one table and some in another table and how to add those together into one row. Example:
(What I'm Getting)
AuthorName Month Total
Test, Fred 3 43
Test, Fred 3 12
Test, Fred 2 56
Test, Fred 5 35
[code]....
I created a view V1 that uses an outer join with a table and calls a sub-view VS1 (ds_proj_report_date) which uses an inner join) and does an inner join with VS1. I also created another view V2 that uses inner join but does not call the sub-view VS1.
When I run the two views as below it works fine
Select * from V1
Union
Select * from V2
I then created another view V3 of the above union as
Create view V3
As
Select * from V1
Union
Select * from V2
Now when I run select * from V3, I get the following error.
Joined tables cannot be specified in a query containing outer join operators. View or function 'ds_proj_report_date' contains joined tables
Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.
Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)
Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0
What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0
I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.
Hi there!
We are experiencing a problem using order clause in the statement below:
SELECT
person.pkey
, first_name
FROM
person
, private_person
WHERE
fkey_person = person.pkey
AND first_name = 'A'
ORDER BY
first_name
, person.pkey
Where person table contains the following columns:
pkey, name etc
and private_person
pkey, fkey_person (indexed), first_name (indexed)
The output is:
STEP 1
The type of query is SELECT
FROM TABLE
private_person
Nested iteration
Index : i$private_person$first_name
FROM TABLE
person
Nested iteration
Using Clustered Index
pkey first_name
----------- ---------------------
2000512 A
10994 A
2299 A
1097 A
1218 A
5133 A
1329 A
1387 A
1465 A
7532 A
5513 A
1884 A
512 A
591 A
(14 row(s) affected)
STEP 1
The type of query is SETOFF
Why SQL server does not order by pkey? Is there any information about it somewhere? Is it a bug or what?
If we are ordering by fkey_person everything is Ok. Can anybody help?
Thanks a lot!
Kind Regards,
Vladimir
I have a table named as C1_Messages with fields Id, Messages, Dates.
My Id filed looks like this:
1000
1001
1008
1009
1084
1093
1098 etc.
But here I need to make these Ids in a order, means it sould be in a consecutive order. Like
1000
1001
1002
1003
1004
1005 etc.
So is there anyway to do this method in SQL?
Regards
Shaji
Hello,
I have made sql table called costreallocation consists of two columsn:
RuleID varchar(10)
Type varchar(20)
I run the following three queries:
insert into costreallocation(RuleId,Type)values('aa','a')
insert into costreallocation(RuleId,Type)values('dd','d')
insert into costreallocation(RuleId,Type)values('cc','a')
then when i notice that these three records are not inserted as they are run:
In the table thn there are three records :
aa a
cc d
dd d
but i need to be filled in the table as they run as:
aa a
dd d
cc d
your help is highly appreciated
Best regards
I have an int field - that will hold number for an ordering system. It's a databound field to a textbox in a grid, I want the default to be empty, however not all fields are required so i may have 1-6 as bound values, and the rest should fall in line behind them - the only thing i can think of to do is default the values to 9999 so they come after 1,2,3,4,5,6 etc... I don't like seeing 9999 in my bound fields.
thanks for any suggestions you may have.
Jeff
Hello.
I want to order a resultset from a table by two columns in another table. Here's how it looks:
[Books]
ID
Title
[linkBookAuthor]
BookID (Books.ID)
AuthorID (Authors.ID)
[Authors]
ID
Firstname
Lastname
AuthorOrder
As you can see it's a many to many relationship. Is there a way to order the books by Authors.Lastname (the first if there are several authors) with one query ? If not, what is the fastest way ? I don't need the names in the resultset so I only want to order the books by Authors.Lastname, not include Authors.Lastname.
Thanks for any help.
Hiya!
I've got a little stored procedure:
CREATE PROCEDURE rICDCode @param1 char(15) = 'Description'
AS
SELECT DiagCode, ICD9ID, Description
FROM ICDCodes
ORDER BY @param1
and SQL is returning error 1008 - "The SELECT item identified by the ORDER BY number %d contains a variable as part of the expression identifying a column position. Variables are only allowed when ordering by an expression referencing a column name."
I want to be able to use the same stored procedure for several different functions which all need the same rowset sorted differently. Any way to do this?
Thanks,
Sarah
Hi guys.
I want to create a top 20 product list from a few thousand products. I want the rest of the products to be grouped into 'others'...
I also want the products to be ordered by the facts in the cube. Thus the product dimension would dynamically change depending on the Time dimension thats being selected.
is this plausible ?
Thanks
Tom
Is it possible without using CASE statements, to order a result set by one field (if the second field return value is null) or another (if the second field return value is not null)
This would be on datetime fields.
Let's say I have 5 records with 2 date fields
sched_dt and arriv_dt.
sched_dt will always have a value, arriv_dt may or may not have a value.
In a single result set I would like to have the records with an arriv_dt sorted by arriv_dt and the ones without an arriv_dt sorted by sched_dt.
*Hope that makes sense*
Thanks
I have a table that has 4 dates for each record (birthday, anniversary, policy_start & policy_end). What I want to do is cycle through each record and then do some calculations using the gaps between the dates. So it
might be Calc1 using (birthday->anniversary), Calc2(anniverary->policy_start), Calc3(policy_start->policy_end), etc. BUT the dates might be in different orders (ie the birthday could come first - or the anniverary could, etc).
In VBA in Excel, I can use the SMALL function within my calcs and this returns the minimum date, the next smallest, the next & then the biggest. But how can I do this in SQL. I guess I can push the 4 dates out to a temp table, order it and then suck them back in - but I have no idea how to do this for all the records in my primary table
I've also tried using various WHERE statements (ie do Calc1 using Birthday & Anniversary WHERE Birthday < Anniversary and ........ - but the code gets awfully long.
Is it possible to write my own SMALL function which uses a bubble sort on the 4 dates & then returns the smallest, next smallest, etc
Any thoughts ?
Hi all,
Ive got a table with a field called 'morder' which orders a menu based on the values in this field (1 for position 1, 2 for position 2 etc...) for example
1 - menu item 1
2 - menu item 2
3 - menu item 3
4 - menu item 4
If I want to add a record to this table and put it at number 2 in the list, i need to update the table to then read...
1 - menu item 1
2 - NEW menu item 2
3 - menu item 2
4 - menu item 3
5 - menu item 4
I want to use a mssql or php function to re-order this field... is it possible??
Hope you can help, and hope it makes sense...
Ash
Hi, I was wondering, I have several columns of data that are able to be sorted on - the user just clicks on the column name. However, we have 1 column called order status where we don't want the order to be alphabetical (and it is a text field - a couple current possible values are Processing...In Production...Waiting For Approval). This order is not good for logical sorting - we would want to be able to specify what value would get order. Does that make sense? So we would want to be able to say that Processing is first in the display, Waiting For Approval would be next, then In Production would be next, and so on. Is there any possible way of doing this in a SQL query? Or am I going to have to manually modify all the data adding numbers to the beginning of each data so that it will sort on the numbers? Thanks everyone.
View 4 Replies View Relatedi add new column using alter command but i always found it in the end of table but
i want to add it in particular position between the columns...........
how to do so .............:)
Hi,I'm streaming data monitoring histories of components into a database table with the following three columns: Hour (DateTime), Id (Int64), and Value (Int32). The Value entry is an aggregate of all values sent from component Id during, and 59 minutes and 59 seconds after, the time listed in the Hour column.I had rather not have to sort the queries after pulling them from the database by date. So I tried to index the DB by the Hour column. Any column will inevitably have duplicates, since the uniqueness depends on a combination of Hour and PortId. But Indexing the Hour column doesn't result in INSERTs being in order as I had expected. Instead, every entry is listed in order of insertion.So. . .how can I keep such a table ordered by date on the disk? I'm afraid this will become very inefficient if this isn't nipped in the bud right now.Thanks so much for your help!-Brandon
View 4 Replies View RelatedI have a little query that returns me all the days in a month, but the days are not in the order I need. They come out like so..
January 10
January 11
January 12
January 13
January 14
etc
January 19
January 2
January 20
January 21
January 22
here is my sample code
select [month] + ' ' + [day] as [Date] from mytable
where [month] = 'january'
and [year] = '2004'
Thanks, Jeff
I am wondering if someone maybe able to help me, I am needing to order my data via months in the calendar sense not alphabetically, below is what I currently have, but it only does it alphabetically.
select to_char(created,'yyyy-Mon'), matdesc, count(*)
from test
group by to_char(created,'yyyy-Mon'), matdesc
order by to_char(created,'yyyy-Mon') desc
any help would be greatly appreciated
Thank you
Stephen
I am trying to do a simple SQL query..the result will be sorted alphabetically by default, but i need to specify say, if there is a "Others" it will be placed last in the list. Is there any way to write the query using ORDER BY?
e.g
Australia
China
Others
USA
and i want it to be:
Australia
CHina
USA
OThers
Hi all,
Ive got a bit of a problem,
I have two tables:
CATEGORY
id
name
parent_CATEGORY_id
RECORD
id
CATEGORY_id
Stock_Held_Number
ps. Ive stripped out any non relevant fields
I also have the following query (again ive stripped out the non-relevant fields)
SELECT TOP (100) PERCENT SUM(dbo.RECORD.Stock_Held_Number) AS TotalStock, CATEGORY.Name AS FundName
FROM CATEGORY
LEFT OUTER JOIN dbo.RECORD ON CATEGORY.ID = dbo.RECORD.CATEGORY_id
GROUP BY CATEGORY.Name, RECORD.Stock_Held_Number
At the moment its grouping all the CATEGORIES and giving me a sum total for each which is great.
The problem I have is the CATEGORY table, there is an optional join to parent CATEGORY records on the table.
What Im trying to do is to provide a fully ordered result within the CATEGORIES and I don't have a clue.
For example:
The CATEGORY table has the following values
ID Name Parent_Category_id
1 Pens
2 Animals
3 Rocks
4 Horses 2
5 Dogs 2
When I currently run the query I get:
Name TotalStock
Pens 20
Animals 30
Rocks 40
Horses 50
Dogs 60
It's technically correct because I don't want the parent to calculate the total value of the children
What Im really trying to do is order them within the hierarchy though and indent (if possible) the result so I would get
Name TotalStock
Animals 30
---Dogs 60
---Horses 50
Pens 20
Rocks 40
Does anybody have any pointers as to how I can achieve this.
Many thanks
Mike