I have 2 Tables one called Reps and the other called STORES
I need to use a UNION statement but my Server is saying that the "UNION" is ERROR The Query Designer does not support the UNION SQL construct.
What command would I use to put these 2 tables together?
HELP!
I am trying to get a consolidated sum of all the columns of the two tables that I am Using the UNION on. I can not get the sum function to work or the group by.
SELECT T2.State,t2.Taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal)
Else T1.DistribSum
End,
T1.VatSum as 'Total Tax', (T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM inv1 t1 inner JOIN oinv t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
UNION ALL
SELECT t2.state,t2.taxcode,
Taxable =
Case
When T1.TaxCode <> 'exempt' and T1.TaxStatus = 'Y' then -(T1.LineTotal - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else 0
End,
'NonTaxable' =
Case
When T1.TaxCode = 'exempt' or T1.TaxStatus = 'N' then -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal))
Else T1.DistribSum
End,
-T1.VatSum as 'Total Tax', -(T1.LineTotal + T1.DistribSum - ((T0.DiscPrcnt/100) * T1.LineTotal) + T1.Vatsum) as 'Line Total'
FROM RIN1 t1 inner JOIN ORIN t0 ON T0.DocEntry = T1.DocEntry inner join CRD1 T2 on T0.Cardcode = T2.CardCode
WHERE T0.DocDate >='[%1]' AND T0.DocDate <='[%2]' and t2.address = T0.shiptocode and t2.adrestype = 's'
order by T2.STATE
Hi,
I have three tables named as BroadCastetails,PaymetMaster,MemberMaster.
I have two query as mentioned below--
SELECT MemberMaster.FirstName, MemberMaster.LastName, BroadCastDetails.BroadCastName FROM BroadCastDetails INNER JOIN MemberMaster ON 5 = MemberMaster.MemberID AND BroadcastCreationDateTime between '01/01/2007' AND '12/12/2007'
SELECT BroadCastDetails.ScheduledStartDateTime, BroadCastDetails.TotalCalls FROM BroadCastDetails UNION SELECT PaymentTransaction.TransactionDate, PaymentTransaction.CallsToCredit FROM PaymentTransaction
I want the result of the above two query in a GridView.How can I do that ? Its urgent...
Hi,
I have an Orders Table and a Freeshipping Table.
ORDERS
======
CustomerId OrderDate Quantity
========== ========= ========
1000 01/01/2003 5
1000 01/04/2003 9
1000 01/08/2003 14
2000 01/01/2003 4
1000 06/03/2003 9
4000 05/02/2003 4
FREESHIPPING
=============
CustomerID FreeDate
========== ========
1000 01/01/2003
1000 01/03/2003
How can I write a query that will return the following result to show the order details for customerid 1000
for a date range between 01/01/2003 and 01/08/2003
OrderDate Quantity FreeShipping
========= ======== ============
01/01/2003 5 Y
01/03/2003 0 Y
01/04/2003 9 N
01/08/2003 14 N
Note that even if an order was not placed and the date was a freeshipping date, it is still displayed in the resultset
Thanks in advance,
-ron
I have 2 temporary tables from a previous operation, Tab1 and Tab2, with the same dimensions. How do I create a third table Tab3 with the same dimensions containing the the combined rows of the 2 previous tables? TIA!
Tab1
Col1 Col2 Col3
A1 B1 C1
A2 B2 C2
Tab2
Col1 Col2 Col3
X1 Y1 Z1
X2 Y2 Z2
X3 Y3 Z3
After the required sql operation I should have
Tab3
Col1 Col2 Col3
A1 B1 C1
A2 B2 C2
X1 Y1 Z1
X2 Y2 Z2
X3 Y3 Z3
Hi there, I have some identical tables that I want to query for a search Is there anyway I can execute the unions first then a where command on all the tables at once I have tried using go but it doesn't seem to work, so I put the where statemtents at the end of each union for now. Here's my code:
strSQL = "SELECT * FROM england WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM ni WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM wales WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%' " &_
"UNION ALL SELECT * FROM scotland WHERE company LIKE '%" & iKeyword & "%' OR address1 LIKE '%" & iKeyword & "%' OR address2 LIKE '%" & iKeyword & "%' OR address3 LIKE '%" & iKeyword & "%' OR address4 LIKE '%" & iKeyword & "%' OR address5 LIKE '%" & iKeyword & "%' OR postcode LIKE '%" & iKeyword & "%'"
I'm trying to create a query that is going against 2 views. The query looks like this
select * from view1
UNION
select * from view2
but when I run it, I keep getting this error:
Cannot resolve collation conflict for UNION operation.
what would cause this error and what do I have to do to be able to run my query?
This query works perfectly and orders by just as I need
Code:
Select
'1st' As [Type],
#Uno.ID
#Uno.Address,
#Uno.shippingInfo
FROM #Uno
[Code] ....
However, when I use it in a Union All so I can pull data from 2 diff tables, the order by statement no longer works. How can I order by data in 2 tables?
Code:
Select
'1st' As [Type],
#Uno.ID
#Uno.Address,
#Uno.shippingInfo
FROM #Uno
[Code] ....
I try to build some query on hierarchy data and after two days thinking about it i have null result. What I need is union more trees tables by root id to one table.
-- tree
-- this query is functional: OK
WITH tree (sid, parend_id, level) as
(
SELECT sid, parend_id, 0 as level
[Code] ....
But this query work with one root id (100); what can i do when i have more roots id ? -> generate each tree table separated by roots id and then all tables join to one (union).
Is it possible to combine the following tables:
Table1:
id
1
2
3
Table2:
licenses
3
2
1
To one table, like this:
id licenses
1 3
2 2
3 1
-There is always the same number of rows in both tables.
Thanks a lot!
Hello,
I'm using SQL2005, SP2
I have multiple temp tables with the same column structure that I would combine into into a single temp table using Unions.
Is this possible?
Example:
Select * From #temp1
Union All
Select * From #temp2
Union All
Select * From #temp3
Into #Temp4
I thought I would ask while I continue to research this in case someone came back with the solution before I was able to track it down elsewhere.
Thanks!
What is the standard way to union tables with exactly the same schema that are in different databases?
For example:
Code Block
USE db1 SELECT * FROM table1
UNION
USE db2 SELECT * FROM table2
this will return the following error:
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'use'.
I have 2 tables
tblOpenSiteDates
Site: int
OpenDate: smalldate
Comment: VarChar
tblCloseSiteDates
Site: int
CloseDate: smalldate
Comments: VarChar
newLocationID: int
I am trying to get a view which would display a site with the open and close dates. Null is ok for a close date for those opens that are still open.. not every office has an open date and close date.. it is possible to have just a close date and not an open date (ie unsure of open date but I know its closing)
so the output would be
viewOpenCloseSites
Site: int
opendate: smalldate
closedate: smalldate
PLEASE HELP i just can not figure it out
thanks...
I have two tables with data that I need to get and display in a combobox. What I want to do is have the parent table listed in the combobox with all of its children indented. Sorted by parent then by child.
This SQL seems to be more complex than I have done previously
I can get all of the records from both tables easily:
Code:
SELECT strName, ID FROM tblParents as pp
INNER JOIN tblChildren as cc
ON cc.pID = pp.uiGUID
UNION (SELECT strName FROM tblChildren as c
INNER JOIN tblParents as p
ON c.pID = p.ID)
However, this simply returns a list that is not ordered in any fashion. I'd like to have all of the parent's children shown under the parent name (there is only 1 parent per child and multiple children per parent)
Hi!
I'm trying to get the results from three different tables, where they have some of the same results. I'm only interested in where they match and then trying to order by date (that's in three columns - M, D, Y). I read previous post in 9/07 but the result doesn't seem to order correctly. It does not have any rhyme or reason to the outputed results as it bounces back and forth through Oct, Nov and Dec posting and throughout all three tables. Here's my query below. Any ideas how I can get my ordering correct for all three tables to display all Oct, all Nov and all Dec?
Thanks so much
select date3, date2, date1, who, what
from
(
select date3, date2, date1, who, what from shows
union
select date3, date2, date1, who, what from shares
union
select date3, date2, date1, who, what from soiree
)
a order by date3, date2, date1
I have got the following union statement:
SELECT plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr,
FROM plan2008
GROUP BY plan2008.jahr, plan2008.monat, plan2008.kdkrbez, plan2008.kdgrbez, plan2008.abgrbez, plan2008.artnr
UNION
SELECT fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
FROM fsp_auftrag
GROUP BY fsp_auftrag.jahr, fsp_auftrag.monatnr, fsp_auftrag.kundenkreis, fsp_auftrag.kundengruppe, fsp_auftrag.abnehmergruppe, fsp_auftrag.artnr
My problem is that each table contains additional values like art_amount, art_turnover etc... whereby the first table contains plan values while the second table contains actual values.
My goal is to get plan as well as the actual values in one row, how is that possible? If I put the values into each of the selects I get two rows, which is not the wished output.
Is it possible to join the tables after the union took place?
Thanks in advance!
HI, Guys:
I've some AT_DATE tables (eg: AT_20080401, AT_20080402, ...) in SQLServer DB, and these AT_XX table have same columns. but table count could be variant, so I have to query sysobjects to get all of these tables. like this:
select name from sysobject where name like 'AT_%'
Now I try to create a view AT which is the union of all these AT_XX tables, such as:
Code Snippet
Create View AT as
select * from AT_20080401
union
select * from AT_20080402
union ...
but since I'm not sure how many tables there, it would be impossible to write SQL as above.
though I could get this union result via stored-procedure, view couldn't be created on the resultset of a procedure.
Create View AT as
select * from AT_createView() <-- AT_createView must be a function, not procedure
I've checked msdn, there is Multi-statement table-valued function, but this function type seems to create one temporary table, I don't want to involve much of insert operation because there could be more than 1million records totally in these AT_XX tables.
So is there any way to achived my goal?
any reference would be appreciated, thanks !
I'm trying to summarize costs assigned to active jobs for a manufacturing business. I need to aggregate work in process (WIP) cost that resides in labor-transaction and part-transaction tables based on transaction types, and transaction dates. Some transactions increase the WIP cost of the job while others decrease WIP. The business needs to see how much $$ is tied up in each job as of a particular date -- the calculation is:
ToDate (cost of materials and labor assigned to job)
- ToInv (cost of materials returned to inventory)
- ToSales (cost of materials sold).
I developed this query incrementally and, so far, the #ToDate, #ToInv, and #ToSales temp tables seem to be populating with the correct data. My thought was to combine these three tables with a UNION and then extract the grand totals and here's where I started getting the following error:
------------------------------------------
Incorrect syntax near the keyword 'UNION'.
------------------------------------------
The problem is with the UNIONs going into #myTotal.
I would appreciate any help with this. Also, please let me know if you can suggest a better design for this. Thanks!
Below is a simplified version of my query:
--#ToDate
CREATE TABLE #ToDate (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToDate (JobNum, Cost)
--M&S To Date
SELECT pt.jobnum,
SUM(pt.extcost) AS Cost
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN ( <valid trans types> )
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
UNION -- This one works ok.
--L&B To Date
SELECT jh.JobNum,
sum(l.LaborRate*l.LaborHrs) + sum(l.BurdenRate*l.BurdenHrs) AS Cost
FROM LaborDtl l
JOIN JobHead jh ON l.JobNum = jh.JobNum
WHERE jh.JobReleased = 1
AND l.PayrollDate < '2007-9-30'
GROUP BY jh.JobNum
--#ToInv
CREATE TABLE #ToInv (JobNum varchar(14), Cost decimal (16,2)
INSERT INTO #ToInv (JobNum, Cost)
SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
--#ToSales
CREATE TABLE #ToSales (JobNum varchar(14), Cost decimal (16,2))
INSERT INTO #ToSales (JobNum, Cost)
SELECT pt.jobnum,
SUM(pt.extcost) AS ToInv
FROM parttran pt
JOIN jobhead jh ON pt.jobnum=jh.jobnum
WHERE trantype IN (<valid trans types>)
AND jh.JobReleased = 1
AND pt.TranDate < '2007-9-30'
GROUP BY pt.jobnum
--#myTotal
CREATE TABLE #myTotal (JobNum varchar(14), Cost decimal (16,2), Source varchar(9))
INSERT INTO #myTotal (JobNum, Cost, Source)
SELECT d.JobNum, SUM(d.Cost) AS Cost FROM #ToDate d GROUP BY d.JobNum ORDER BY d.JobNum
UNION -- Problem**********************
SELECT i.JobNum, SUM(-1*i.Cost) AS Cost FROM #ToInv i GROUP BY i.JobNum ORDER BY i.JobNum
UNION -- Problem**********************
SELECT s.JobNum, SUM(-1*s.Cost) AS Cost FROM #ToSales s GROUP BY s.JobNum ORDER BY s.JobNum
--Select grand total for each job
SELECT JobNum, SUM(Cost) FROM #myTotal ORDER BY JobNum
--Drop temp tables
DROP TABLE #ToDate
DROP TABLE #ToInv
DROP TABLE #ToSales
DROP TABLE #myTotal
Hi,I'm coming back to Sql Server after 4 years away, using other RDBMS,and there's a few things I'm struggling to remember how to do (if Icould do them in the first place...)Main amongst those is EXCEPT syntax.In DB2, if I have two sets of data and I want to exclude the second setfrom the first, I can do:SELECT col1, col2, col3, ... colNFROM table1EXCEPTSELECT col1, col2, col3, ... colNFROM table2;But SQL Server balks at this. I've had a quick look in the T-SQL helpfor EXCEPT, but I didn't find that particularly enlightening. Anypointers as to how I should be doing this?ThanksJames
View 5 Replies View RelatedCurrently I have a standard query with a join to several tables. There are two additional tables MAS_CTB and MAS_STB. I would like to do a union between those two tables to get FIELDVALUE which will exist in either the CTB table or the STB table and then have that value be returned with the results of the original query.
I can of course write a UNION from the main query to the CTB and then to the STB table, however it's about 80 dummy fields I would have to replicate in the union which is why I was wondering if there was a more simple way.
Main Query:
SELECT Field1, Field2...Field80
From Table1
Join Table2
Union Portion:
Select FieldValue <------
From MAS_CTB
UNION
Select FieldValue <-----Return to main query above
From MAS_STB
SQL Server 2008 r2...
I have a query which does 3 selects and Union ALLs each to get a final result set. The performance is unacceptable - takes around a minute to run. If I remove the Union All so that the result sets are returned individually it returns all 3 from the query in around 6 seconds (acceptable performance).
Any way to join the result sets together without using Union All.
Each result set has exactly the same structure returned...
Query below [for reference]...
WITH cte AS (
SELECT A.[PoleID], ISNULL(B.[IsSpanClear], 0) AS [IsSpanClear], B.[SurveyDate], ROW_NUMBER() OVER (PARTITION BY A.[PoleID] ORDER BY B.[SurveyDate] DESC) rownum
FROM[UT_Pole] A
LEFT OUTER JOIN [UT_Surveyed_Pole] B ON A.[PoleID] = B.[PoleID]
[Code] .....
Why the sequence different?
select * from (
select id=3,[name]='Z'
union all select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
---------
--1 G
--2 R
--4 Z
--3 Z
select * from (
select id=3,[name]='Z'
union select 1,'G'
union all select 2,'R'
union all select 4,'Z'
) as t
order by [name]
--result:
----------
--1 G
--2 R
--3 Z--changed
--4 Z
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies View RelatedHi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
Hi,
Is there any way to open .TPS(TopSpeed) tables via SQL Server 2000???
hi, i installed sql server 2005 express on my tests server, i need to move a database from my sql server 2000 to test how it works with 2005.
i understood that the export/import option is not available in express version. but i still need to pass my database, other wise i cant check the power of the 2005 version.
please tell me about other way to do!
I'm trying to migrate some tables from 2000 to 2005. I used the wizard in SSIS to create a package bringing them in.
The first database I did this on it worked great, in no time.
However, I tried this on another database and I get a LOT of these:
Information: 0x4004800C at {0417E15E-7306-4DAD-BEDD-1FDB17390EB0}, DTS.Pipeline: The buffer manager detected that the system was low on virtual memory, but was unable to swap out any buffers. 2 buffers were considered and 2 were locked. Either not enough memory is available to the pipeline because not enough is installed, other processes are using it, or too many buffers are locked.
Now, some things to note:
1. I'm trying to do about 170 tables at once - although none of them are bigger than a MB
2. Copying files between these 2 servers is also taking a long time
Is it too many tables at once? Is it the actual pipeline between the servers? The message above seems to indicate that it is a problem local to the machine, and maybe our file-copying problem is just another issue entirely.
Hi, I need to link an Access 97 database to sql server 2000. I can't use Enterprise Manager so I have created a simple asp.net page using sp_addlinkedserver and sp_addlinkedsrvlogin.
The mdb file is on a network share, then I have permissions problems: here is a sample
sp_addlinkedserver 'test', 'Access 97', 'Microsoft.Jet.OLEDB.4.0',
'\SVKNFS080Pshareaccess97db1.mdb', 'efco'
sp_addlinkedsrvlogin 'test', false, 'sa', 'Admin', NULL
I have no problems executing these stored procedures (the linked server is added in sysservers table), but when I try to query the access database
SELECT * FROM test...profile
I get this error
Microsoft][ODBC SQL Server Driver][SQL Server][OLE/DB provider returned message:
The Microsoft Jet database engine cannot open the file '\SVKNFS080Pshareaccess97dboutlet.mdb'. It is already opened exclusively by another user, or you need permission to view its data.]
The mdb file is not locked, I think is a permission's problem.
Can anyone help me ?
Thanks in advance
Hi,
whats the best way to copy a table from sql server 2005 to 2000?
Thanks for any help...
I need to find an ERD of MS SQL Server 2000 system tables. Any suggestions where to look?
View 2 Replies View RelatedI need to find an ERD of MS SQL Server 7.0 and MS SQL Server 2000 system tables. Any suggestions where to look?
View 1 Replies View RelatedI have some tables in the employee database, this database created from sql sever 2000. I build a employee management application by C# and sqlserver 2000.
My goal is after design complete the empployee database by sqlserver 2000, any users can not modify my tables and unkonw table's structure.
help me please
thanks and reagards
Hello All,
I am new to this forum and hope that some one can help me with this problem.
I know that in MS Access you can have a linked table in your databse such that as long a the like is not broken, it behaves similar to any other table in the database.
My problem is that I need to do the same thing for multiple databases that I have on a MS SQL Server that ALL currently have copies of the same table in common.
I wish to have a single table that they all share and have access to just link any other table that they currently have but is actually linked.
Can this be done?
And if so, then could someone please guide me in this?
My logic is that if MS Access can link tables then I could suspect that MS SQL Server should be able to also, but I could be wrong.
Thanks in advance,
Lonnie