Trouble With Temp Tables And UNION Keyword

Feb 18, 2008

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

View 3 Replies


ADVERTISEMENT

Union 2 Temp Tables

Nov 8, 2007

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

View 7 Replies View Related

Using Union All When Inerting Into Temp Tables

Oct 4, 2007

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!

View 1 Replies View Related

Having Trouble With In Keyword ; Maybe Need A Subquery?

Aug 2, 2007

 I am displaying advertisers on a dynamic .net page that users will
view via querystrings from a county  page,  then a city page , as they
can pick a zone , then view the advertiser's ad  predicated on the
zone they pick. I want to have advertisers on a premium plan that can
be viewed in all areas of the city, regardless what zone the user
picks. Trying the below does not suffice my resultset (the queriedzone
is a variable as the request.querystring for the zone ; 'all' is the
indicator for an advertiser who should be seen in all zones)

sqlstr = "select company, address, email from advertisers where zone IN ('" + queriedzone + "','all') "
??? thanks in advance ed

View 1 Replies View Related

Union Trouble

Feb 4, 2004

In a SQL 2000 SP 1 installation I've a problem with a view.

When I run a select from this view with a where clause the view return me a result set, but if I insert a order by clause the result set is empty.
I try to re-organize indexes and update statistics without results.

The server is on a client site so I can't update the SQL 2000 installation immediatly, I try to ask about the installation of the SP 3.

But in the meanwhile I need some information. Anyone see this problem before.

Thanks in advice for any help!

View 9 Replies View Related

Trouble With Union Queries

Feb 18, 2004

I've run into some trouble with an apparently simple UNION query:

SELECT [Date] AS D, C7, NULL AS L72A, NULL AS P02, PAV
FROM [Process Variables Level 1]
UNION
SELECT [Date] as D, NULL AS C7, L72A, P02, NULL AS PAV
FROM [Process Variables Level 2]
ORDER BY D

Records in the 2 tables may or may not have the same Date/Time values.

The product of the above UNION query appears to be using dates from the [Process Variables Level 2] table only. I'm getting values returned for field C7 with Date/Time values from table [Process Variables Level 2]??

Any suggestions would be welcome!!

View 10 Replies View Related

Select Query Union Trouble

Nov 26, 2007

Given the following tables:

[Members]
-memberID (PK)
-memberName

[Questions]
-questionID (PK)
-questionText

[Surveys]
-surveyID (PK)
-surveyName
-surveyDescription
-surveyType (FK)

[SurveyQuestions]
-surveyID (PK/FK)
-questionID (PK/FK)

[SurveyQuestionMemberResponse]
-surveyID (PK/FK)
-memberID (PK/FK)
-questionID (PK/FK)
-yesResponse(bit)
-noResponse(bit)
-undecidedResponse(bit)

How can I write a query to return the results for a given survey for all members (including members who have not given responses) given the surveyID.

In the [SurveyQuestionMemberReponse] table I record survey results for any members who have answered the survey. However, if a member has not responsed to the survey they will not have a record in this table.

I want to return a list of members with their response to each question in the survey. If a member has not given a response I would like to indicate they have not responded to the survey and they should still appear in the list.

When I attempt to write a query to UNION the results of a query aimed at gathering all of the results in the [SurveyQuestionMemberReponse] to all of the people in the [Members] table I recieve an error when I include the questionText field in my result set.

The error indicates:

The text data type cannot be selected as DISTINCT because it is not comparable.

Can someone please point me in the right direction. I suspect I am going about this all wrong.

[NOTE] The 'surveyType' in the [Surveys] table indicates which subset of members a given Survey should be available to. For this example let's just assume that every survey should belong to all members.

Thanks,

Zoop

View 3 Replies View Related

Results Of Union Into Temp Table

Apr 3, 2008

This may be a dumb question, but I can't seem to get the syntax right. I have two temp tables that have the same columns, I want to do a union on them and store the results in a temp table. Any ideas?

Ie.

select * from #tmpTable1
union
select * from #tmpTable2
into #tmpTable3

Thanks!!!

View 4 Replies View Related

Temp Table Vs. Union: Which Has Better Performance?

Aug 13, 2007

Right now, a client of mine has a T-SQL statement that does thefollowing:1) Create a temp table.2) Populate temp table with data from one table using an INSERTstatement.3) Populate temp table with data from another table using an INSERTstatement.4) SELECT from temp table.Would it be more efficient to simply SELECT from table1 then UNIONtable 2? The simply wants to see the result set and does not need tore-SELECT from the temp table.

View 1 Replies View Related

A Curious Error Message, Local Temp Vs. Global Temp Tables?!?!?

Nov 17, 2004

Hi all,

Looking at BOL for temp tables help, I discover that a local temp table (I want to only have life within my stored proc) SHOULD be visible to all (child) stored procs called by the papa stored proc.

However, the following code works just peachy when I use a GLOBAL temp table (i.e., ##MyTempTbl) but fails when I use a local temp table (i.e., #MyTempTable). Through trial and error, and careful weeding efforts, I know that the error I get on the local version is coming from the xp_sendmail call. The error I get is: ODBC error 208 (42S02) Invalid object name '#MyTempTbl'.

Here is the code that works:SET NOCOUNT ON

CREATE TABLE ##MyTempTbl (SeqNo int identity, MyWords varchar(1000))
INSERT ##MyTempTbl values ('Put your long message here.')
INSERT ##MyTempTbl values ('Put your second long message here.')
INSERT ##MyTempTbl values ('put your really, really LONG message (yeah, every guy says his message is the longest...whatever!')
DECLARE @cmd varchar(256)
DECLARE @LargestEventSize int
DECLARE @Width int, @Msg varchar(128)
SELECT @LargestEventSize = Max(Len(MyWords))
FROM ##MyTempTbl

SET @cmd = 'SELECT Cast(MyWords AS varchar(' +
CONVERT(varchar(5), @LargestEventSize) +
')) FROM ##MyTempTbl order by SeqNo'
SET @Width = @LargestEventSize + 1
SET @Msg = 'Here is the junk you asked about' + CHAR(13) + '----------------------------'
EXECUTE Master.dbo.xp_sendmail
'YoMama@WhoKnows.com',
@query = @cmd,
@no_header= 'TRUE',
@width = @Width,
@dbuse = 'MyDB',
@subject='none of your darn business',
@message= @Msg
DROP TABLE ##MyTempTbl

The only thing I change to make it fail is the table name, change it from ##MyTempTbl to #MyTempTbl, and it dashes the email hopes of the stored procedure upon the jagged rocks of electronic despair.

Any insight anyone? Or is BOL just full of...well..."stuff"?

View 2 Replies View Related

Insert Into Temp Results Of A Union Query

Nov 20, 2007

Hi,
I have follwing union query. I want to put this all in a temp table.

select Store_Id,batchnumber
From
Adjustments where updatedDt between '10/30/2007' and '11/20/2007' and Store_id in(8637 ,8641)
group by Store_Id, batchnumber
Union
select DestinationId,b.batchNumber
from
batch b
inner join Carton C on C.Carton_Id = b.General_ID
inner join Document d on d.Document_Id = c.Document_Id
where b.BatchType = 'Warehouse' and b.TranTable = 'Carton'
and (d.DestinationId in (8637 ,8641) ) and c.UpdatedDt Between '10/30/2007' and '11/20/2007'
Union
select d.DestinationId,b.Batchnumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferIn' and b.TranTable = 'Document'
and (d.DestinationId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
Union
select d.SourceId,b.batchNumber
From
batch b
inner join Document d
on d.Document_Id = b.General_Id
where b.BatchType = 'TransferOut' and b.TranTable = 'Document'
and (d.SourceId in (8637,8641) ) and d.UpdatedDt Between'10/30/2007' and '11/20/2007'
order by batchnumber

Kindly advice.

Thanks
Renu

View 2 Replies View Related

Can I Make A Temp Table With A Union All Select?

Mar 26, 2008

I'm having trouble creating a temp table out of a select statement that uses multipe union alls.

Here's what I have, I'm trying to get the results of this query into a temp table...

select
parent,
(select cst_id from co_customer (nolock) where cst_key = Parent) as cst_id,
(select cst_name_cp from co_customer (nolock) where cst_key = Parent) as cst_name_cp,
(select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent) as Parent_Total_assets,
sum(own_assets) as Total_child_own_assets

from
(
Select parent,
Child,
(select org_own_assets_ext from dbo.co_organization_ext where org_cst_key_ext = child) as Own_assets

from
(Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,1) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,1) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,2) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,2) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,3) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,3) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,4) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,4) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,5) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,5) is not null
union all

Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,6) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,6) is not null
union all
Select Cst_key as Child,
dbo.return_org_parent(cst_key,0,7) as Parent
from co_customer (nolock)
where cst_type = 'Organization'
and cst_delete_flag = 0
and dbo.return_org_parent(cst_key,0,7) is not null )as c
) as d

group by parent

having sum(own_assets) <> (select org_total_assets_ext from dbo.co_organization_ext where org_cst_key_ext = parent)

View 8 Replies View Related

SQL Server 2014 :: UNION ALL In View With Temp Table

May 8, 2015

I have a performance issue with one of the views when I join the view with a temp table

I have 2 Views - View1 and View2.

There is a third view - view_UNION where the

view_UNION =
SELECT * FROM View1
UNION ALL
SELECT * FROM View2

If I have a query like -

Select view_UNION.* FROM
view_UNION INNER JOIN #TMP ON #TMP.ID = view_UNION.ID

the execution is too slow.

But if I execute the views separately, I get good performance.

How to improve the performance of the view_Union

View 7 Replies View Related

Transact SQL :: Confirmation Of UNION ALL Query For INSERT INTO Temp Table

Jul 21, 2015

I have the following UNION ALL query with SELECT INTO @tblData temp table. I would like to confirm if my query is correct.

In my first SELECT statement, I have INSERT INTO @tblData.

Do I need another INSERT INTO @tblData again in my second SELECT statement after UNION ALL?

DECLARE @BeginDate as Datetime
DECLARE @EndDate as Datetime
SET @BeginDate = '7/1/2015'
SET @EndDate = '7/13/2015'
DECLARE @tblData table

[Code] ....

View 3 Replies View Related

Temp Tables Vs Temp Variables

Jul 20, 2005

I have an application that I am working on that uses some small temptables. I am considering moving them to Table Variables - Would thisbe a performance enhancement?Some background information: The system I am working on has numeroustables but for this exercise there are only three that really matter.Claim, Transaction and Parties.A Claim can have 0 or more transactions.A Claim can have 1 or more parties.A Transaction can have 1 or more parties.A party can have 1 or more claim.A party can have 1 or more transactions. Parties are really many tomany back to Claim and transaction tables.I have three stored procsinsertClaiminsertTransactioninsertPartiesFrom an xml point of view the data looks like this<claim><parties><info />insertClaim takes 3 sets of paramters - All the claim levelinformation (as individual parameters), All the parties on a claim (asone xml parameter), All the transactions on a claim(As one xmlparameter with Parties as part of the xml)insertClaim calls insertParties and passes in the parties xml -insertParties returns a recordset of the newly inserted records.insertClaim then uses that table to join the claim to the parties. Itthen calls insertTransaction and passes the transaction xml into thatsproc.insertTransaciton then inserts the transactions in the xml, and alsocalls insertParties, passing in the XML snippet

View 2 Replies View Related

Temp. Tables / Variables / Process Keyed Tables ?

Feb 22, 2008

I have 3 Checkbox list panels that query the DB for the items. Panel nº 2 and 3 need to know selection on panel nº 1. Panels have multiple item selection. Multiple users may use this at the same time and I wanted to have a full separation between the application and the DB. The ASP.net application always uses Stored Procedures to access the DB. Whats the best course of action? Using a permanent 'temp' table on the SQL server? Accomplish everything on the client side?

[Web application being built on ASP.net 3.5 (IIS7) connected to SQL Server 2005)

View 1 Replies View Related

Joining Tables - Return Name / Description And Keyword For Every Record ID

May 7, 2012

I have 3 tables I'm trying to join:

Table 1: Contains ID, Name, Description
Table 2: Contains ID, Keyword
Table 3: Is lookup table for tables 1 & 2

I need a query to return a table having columns of:
Name, Description, Keyword for every record ID in Table 1

The issue is that table 2 contains multiple keywords for each key ID and my query returns an error: Subquery returned more than 1 value.

When I run this query using a specific key, it returns the correct information the way I want it:

DECLARE @ServiceID VARCHAR(10)
SET @ServiceID = '35'
DECLARE @Name VARCHAR(8000) DECLARE @Desc VARCHAR(8000) DECLARE @Keywords VARCHAR(8000)
SELECT @Name = [DefService].[Name],@Desc = [DefService].[Description],@Keywords = COALESCE(@Keywords + ', ', '') + [DefKeyword].[Name] FROM [DefService] INNER JOIN [DefKeywordServices] ON [DefService].serviceid = [DefKeywordServices].[ServiceID] INNER JOIN [DefKeyword] ON [DefKeyword].[KeywordID] = [DefKeywordServices].[KeywordID] WHERE [DefService].[ServiceID] = @ServiceID PRINT @Name + ' | ' + @Desc + ' | ' + @Keywords
SELECT @Name AS Name,@Desc AS Description,@Keywords AS Keywords

Results:
NameDescriptionKeywords
GoToMyPC - Account RequestRequest a Citrix GoToMyPC account.GTMPC, GoTo, application, software, install, Installation, applications

When I run the same query without a specific key it fails. The results only return a single row containing Name, Description and then ALL keywords for every key ID...very odd behavior.

BTW, I need to do this in a single SQL query and not a stored proc or other method.

View 10 Replies View Related

UNION OF 2 TABLES - CAN'T GET SUM

Sep 7, 2005

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

View 2 Replies View Related

Union And Join With Three Tables

Sep 17, 2007

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...

View 2 Replies View Related

Join Or Union Two Tables?

Nov 18, 2003

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

View 2 Replies View Related

SQL 2000 2 Tables Using UNION

Mar 27, 2006

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!

View 5 Replies View Related

WHERE Command On UNION Tables

Feb 19, 2008

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 & "%'"

View 15 Replies View Related

Order By In Union - Data In 2 Tables

Feb 10, 2015

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] ....

View 1 Replies View Related

CTE - Union Hierarchy Tables By More Roots ID

Oct 4, 2013

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).

View 1 Replies View Related

UNION 2 Tables Horizontally, To One Table

Aug 5, 2007

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!

View 9 Replies View Related

Standard Way To Union Tables In Different Databases?

Oct 17, 2007

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'.

View 3 Replies View Related

Trouble Joining Multiple Tables

Dec 14, 2004

Hi,
This seems like a basic problem but I can't figure out how to resolve it.

I have a query :

SELECT PR.WBS2, SUM(LedgerAR.Amount * - 1) AS Expr5, LB.AmtBud AS budget
FROM PR LEFT OUTER JOIN
LedgerAR ON PR.WBS1 = LedgerAR.WBS1 AND PR.WBS2 = LedgerAR.WBS2 AND LedgerAR.WBS3 = PR.WBS3 LEFT OUTER JOIN
LB ON LB.WBS1 = PR.WBS1 AND LB.WBS2 = PR.WBS2 AND PR.WBS3 = LB.WBS3
WHERE (PR.WBS2 <> '9001') AND (PR.WBS2 <> 'zzz') AND (PR.WBS2 <> '98') AND (PR.WBS3 <> 'zzz') AND (PR.WBS2 <> '') AND (PR.WBS1 = '001-298')
GROUP BY PR.WBS2, LB.AmtBud
ORDER BY PR.WBS2


The output of the above query:

WBS2Expr5budget
0141
0141953000
0143
121724540
1217500
1217622.5800
12171000
12172000
12174000
12174500
121772908000
121793513500
12173445018000
12176596032000
12173801044000
121838100
121913224.5
1220
1221
122262000
12224000
122312702


I want to sum up the middle column and last column grouping by wbs2. However, when I do SUM(lb.amtbud) the budget column is not summing correctly it is summing the column as if the data appeared like this:

0141
01410101410101410103000
01410101410101410147.53000
01410101410101410147.53000
0143
014305
1217
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
12170101217010121701008000
1217010121701012170101017.58000
121701012170101217010382.58000
12170101217010121701027.58000
121701012170101217010302.58000
12170101217010121701027.58000
121701012170101217010382.58000
121701012170101217010302.58000
1217010121701012170104958000
1217010121701012170102008000
1217010121701012170101017.58000
1217010121701012170101182.58000
1217010121701012170101952.58000
1217060
1217061
121708012170804000



So as a result I am getting 9000 where wbs2 = '0141'

I figure that in my top query I am not joining something correctly. Could someone point out what I am doing wrong?

Thank You.

:)

View 2 Replies View Related

Union Of 2 Tables With Differnt Number Of Rows

May 24, 2006

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...

View 1 Replies View Related

Union Two Tables With Relational Ordering / Grouping?

Jul 16, 2012

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)

View 2 Replies View Related

Query Multiple Tables - Union/Order By

Oct 25, 2007

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

View 4 Replies View Related

Union Select Of Two Tables And Join Of Another Table Afterwards

Apr 2, 2008

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!

View 8 Replies View Related

How To Create View Of Union On Variant Tables ?

Apr 1, 2008

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 !

View 8 Replies View Related

DTS - SP And Temp Tables

Nov 3, 2000

I am attempting to execute a stored procedure as the sql query for a data transformation from sql into an excel file. The stored procedure I am calling uses temp tables (#tempT1, #tempT2, etc.) to gather results from various calculations. When I try to execute this sp, I get
'Error Source: Microsoft OLE DB Provider for SQL Server
Error Description: Invalid Object name "#tempT1"'

Is there a way to make a DTS package call a stored procedure that uses temp tables?

Thanks.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved