UNION Query Won't Work

Jul 20, 2005

I am having alot of trouble with a union query Im trying to create.
The bizarre thing is I initially created this query and managed to
save it as a view

SELECT ID, DepartureDate, City, Country, Region,
fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' AS Type
FROM dbo.vHoliday
UNION ALL
SELECT ID, ValidTo, DestCity, Country, Region, fkDepartureAirport,
Price, '2' AS Type
FROM dbo.vFlight


However when I tried to update the view to this:


SELECT ID, DepartureDate, fkCity, City, fkCountry, Country,
Region, fkDepartureAirport, CONVERT(MONEY, Price) AS Price, '1' AS
Type
FROM dbo.vHoliday
UNION ALL
SELECT ID, ValidTo, fkCity, DestCity, fkCountry, Country, Region,
fkDepartureAirport, Price, '2'
FROM dbo.vFlight


it comes up with the error: view definition includes no output columns
or includes no items in the from clause.


Any ideas??????

All suggestions appreciated

Ally

View 1 Replies


ADVERTISEMENT

Query Works Fine Outside Union, But Doesn't Work .. .

Mar 31, 2004

hi all

I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.

any one know why that might be the case??


select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc




i've included the union query here for completeness of the question



begin
declare @current_date datetime
set @current_date = GETDATE()


select top 100 _id, callback_date, priority, recency, frequency from
(
(
select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min"
from topcat.class_contact
where status ='callback'
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) <> 0)
order by callback_date asc
--order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc
)
union
(
select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'callback'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
and (DATEPART(hour, callback_date) = 0)
order by callback_date asc
)
union
(
select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency
from topcat.class_contact
where status = 'No Connect'
and callback_date is not null
and (DATEPART(year, callback_date) <= DATEPART(year, @current_date))
and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date))
and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date))
order by callback_date asc
)
union
(
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date
from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone
where contact.phone_num is not null
and contact.status = 'New Contact'
group by contact._id
order by "recency" desc, "frequency" desc
)
) contact_queue
order by priority desc, recency desc, callback_date asc, frequency desc

end

View 1 Replies View Related

How Does Union/union All Work Inside SQL Server?

Apr 29, 2008



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

View 3 Replies View Related

UNION Statement Doesn't Work

Jul 20, 2005

Hi,I have a database stored in MS SQL 2000 and an application written inVB5, which connects the database via JET/ODBC.I have a problem with the UNION statement.When I run a simple query like:"SELECT field1 FROM table1 UNION SELECT field2 FROM table2"I get the following error:"Runtime error 3078 - The Microsoft Jet database engine cannot find theinput table or query 'select field1 from table1'. Make sure it existsand that its name is spelled correctly."I can run the queries separately "SELECT field1 FROM table1" and "SELECTfield1 FROM table2", so that I'm sure table and field names are correctand I have permission to access them.Both field1 and field2 are the same type (int).If I run the query in MS SQL Query Analyzer, it works fine.It doesn't work only when I run it from VB/JET/ODBC.Has anyone already had this kind of problem?Any help will be highly appreciated!Thank you so much for the attention.--Posted via http://dbforums.com

View 1 Replies View Related

UNION For Columns Of CLOB Data Type Does Not Work

May 10, 2007

I have two tables Encounter & Encounter_History. They have same columns. One column is of type CLOB. My requirement is to retrieve all the distinct records from both the tables with order by a date column. But problem is, UNION does not work in case of CLOB data type.

I know it will work if I use UNION ALL, but it returns duplicate records.

Please give me suggestion, how to solve this problem.

For example: The following query does not work since column1 is a CLOB data type

select column1 from table1
union
select column1 from table2

Thanks

View 1 Replies View Related

SELECT With UNION And DATEDIFF Does Not Work On SQL Server Compact Edition

Jan 24, 2008

Hi there,

i've tried using the SELECT SQL sentence on PPC with UNION and INNER JOIN in combination with DATEDIFF function which works fine on ordinary SQL but obviously does not work on compact edition. Is there some workaround?

SQL sentence that works:





Code Snippet

SELECT * FROM
(
SELECT 'O' Type, O.Name , OC.Name Contact, Birthday FROM OutletContact OC
INNER JOIN Outlet O ON OC.OutletID=O.OutletID
UNION
SELECT 'W' Type, W.Name,WC.Name Contact, Birthday FROM WholesalerContact WC
INNER JOIN Wholesaler W ON W.WholesalerID=WC.WholesalerID
) S
WHERE Birthday IS NOT NULL AND (DATEDIFF(day,GETDATE(),Birthday) BETWEEN 0 AND 14)


Thanks for any hint or solution to my problem,

Gorazd

View 1 Replies View Related

How To Create A Make-Table Query With A Union Query

Oct 30, 2006

I have successfully execute a union query. How can i create a make-table query to accomodate the resultset of the union query?

View 2 Replies View Related

How To Wrap A UNION Query In A Totals Query?

Jul 20, 2005

I've got some SQL that works as far as returning a recordset from a series ofUNION statements.viz:SELECT whateverUNION thisUNION thatUNION otherNow I want to group and sum on it's results.Started out tying:SELECT * FROM(union stuff)....but couldn't even get past the syntax check.Where I'm headed is a sort of pivot table presentation of some hours dataassociated with various projects with a column for each of six date ranges.Bottom line: can somebody give me a pointer to the syntax needed to wrap thoseUNION statements and then select/group/sum their results?--PeteCresswell

View 9 Replies View Related

Union Query Help

Mar 27, 2006

I need help with a union query.  My table structure is as follows:
OffierID (key field)
CaseFileID 
CurrentOffer
PrevOffer

There table can have multiple entries per CaseFileID.   
I need a query that will tell the highest value in Current Offer or
PrevOffer for each CaseFileID.  I have a union query that combines
CurrentOffer and PrevOffer and then selects the top value for a
specific CaseFileID; however, I want to have a complete list of
CaseFileIDs with one value for PrevOffer.  My current query is as
follows:

SELECT TOP 1 Offer FROM
(
select CurrentOffer as PrevOffer
FROM tblOffers
WHERE tblOffers.CaseFileID = @CaseFileID AND CurrRank <> 1
UNION
select PrevOffer as PrevOffer
FROM tblOffers
WHERE tblOffers.CaseFileID = @CaseFileID
) tmp
ORDER BY 1 desc

How can I get this to work for all CaseFiles?  Thanks for your help.

View 4 Replies View Related

Union Query

Jul 10, 2004

I want to perform the task which is querying the table using the select statemtent and then inserting some values using the insert/update statement.

Can i perform the both (select and insert) using the union statement.

View 1 Replies View Related

UNION Query

Aug 9, 2004

Hi gusy, this is the first time I am trying to use "Union" query. I am trying to create a view(linking and taking data from 3 tables) so I can create a crosstab report out of it.

Basically one table contains about 12 fields and I am trying to grab data from all of them is they are not null.So this is my query,but when it executes it only dispalys result from the first query,what am I doing wrong.

SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
dbo.RelocateeRemovalistAmounts.CarInsurance
FROM dbo.RelocateeRemovalist INNER JOIN
dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
WHERE (dbo.RelocateeRemovalistAmounts.CarInsurance IS NOT NULL)
UNION
SELECT dbo.RelocateeRemovalist.RelocateID, dbo.RelocateeRemovalist.RemovalistNumber, dbo.RelocateeRemovalist.SupplierID,
dbo.RelocateeRemovalistAmounts.CarTransport
FROM dbo.RelocateeRemovalist INNER JOIN
dbo.RelocateeRemovalistAmounts ON dbo.RelocateeRemovalist.RelocateID = dbo.RelocateeRemovalistAmounts.RelocateID
WHERE (dbo.RelocateeRemovalistAmounts.CarTransport IS NOT NULL)

Thanks

View 1 Replies View Related

A WHERE In A Union Query

Apr 22, 2004

Hi, i have a union query that lists all the years from a date field and add the currentyer if its not already listed:

SELECT DISTINCT Cas Yearlist
FROM dbo.ViewPressReleases UNION SELECT datepart(yyyy, getdate())
ORDER BY DatePart(yyyy,[PressreleaseDate])

what i need to do is filter it with something along the lines of:

WHERE Yearlist LIKE myvariable

although i know i cant simply use:

WHERE Yearlist

it would have to be something like:

WHERE DatePart(yyyy,[PressreleaseDate]) UNION datepart(yyyy, getdate()) LIKE myvariable


Does anyone know how to write this correctly?

View 8 Replies View Related

Union With Sub Query

Jul 23, 2005

I've been trying to do a union with a subquery - I've made a differentexample which follows the same principles as follows:First bit brings back accounts which are in the top 10 to 15 by accountname.Second bit brings back accounts which are in the bottom 10 to 15 byaccount name.I want to union the two result sets together. These selects work asthey are, but don't when i take the comment away from the unionoperator.select top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName asc)order by c1.accountName asc--union allselect top 5 c1.accountnofrom tbl_customer c1where c1.accountno not in(select top 10 c2.accountnofrom tbl_customer c2order by c2.accountName desc)order by c1.accountName descSo my problem is really about how to have an order by in a sub querywhich is then used in a main query which is then unioned with anotherquery - SQL Server doesn't seem to like that combination of things. Anyclues anyone?Cheers,NAJH

View 5 Replies View Related

Union Query

Jul 23, 2005

hi,Can you union 2 queries with an IF statement between them?e.g.select a, bfrom mtTablewhere a = cunionif ab = xbeginselect a, bfrom mtTablewhere a = cendCheers,Jack

View 4 Replies View Related

Query: Union On Self

Jul 10, 2006

Hello every body.I have a small issue.Problem: I have a table with 4 descriptor columns (type). I need toformulate a query to retrieve a count for each type so I can groupby...etc. The view I have works, but doesn't work when I supplement thequery with some functions... they just don't like the UNION. The realproblem is I can't change any of the udf's or queries, just the view.The view is inner joined back on to the primary table 'qt_ins' againand a heap of other tables. But for this post and to not complicate ittoo much I've just included the primary table and the view...Also my querys work if I don't put a where clause on for the VIEW. eg:.... and cv.type = 'Environmental'.... for some reason with a clause itgets stuck in an *infinite loop.Conditions: The table structure cannot be changed in anyway. Theview/query must return 2 columns qi_id & type.I considered creating a function to return the Types but then I figuredI would ask you folks for a better way.Any help with the view appreciated.Thank you.The below will create the table, with sample data and the view.---------------------------StartQuery--------------------------------------------CREATE TABLE [dbo].[qt_ins] ([qi_id] [int] NOT NULL ,[qi_injury] [bit] NULL ,[qi_environmental] [bit] NULL ,[qi_equipment_damage] [bit] NULL ,[qi_vehicle] [bit] NULL) ON [PRIMARY]GOINSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (20,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (21,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (23,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (24,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (25,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (26,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (27,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (28,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (29,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (30,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (31,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (32,1,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (33,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (34,1,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (35,1,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (36,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (37,0,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (38,0,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (39,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (40,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (41,0,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (42,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (43,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (44,0,1,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (45,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (46,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (47,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (48,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (49,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (50,1,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (51,0,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (52,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (53,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (54,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (55,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (56,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (57,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (58,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (59,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (60,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (61,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (62,0,1,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (63,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (64,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (65,1,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (66,1,0,0,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (67,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (68,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (69,1,0,0,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (70,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (71,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (72,1,1,1,1)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (73,0,0,1,0)INSERT INTO qt_ins(qi_id,qi_injury,qi_environmental,qi_equipment_dam age,qi_vehicle)VALUES (81,1,0,0,0)GOCREATE VIEW dbo.v_qt_in_typeASSELECT qi_id, 'Injury' AS type FROM qt_ins WHERE qi_injury = 1UNION allSELECT qi_id, 'Environmental' AS type FROM qt_ins WHEREqi_environmental = 1UNION allSELECT qi_id, 'Equipment damage' AS type FROM qt_ins WHEREqi_equipment_damage = 1UNION allSELECT qi_id, 'Vehicle' AS type FROM qt_ins WHERE qi_vehicle = 1GOselect count(*),type from v_qt_in_type group by type---------------------------ENDQUERY--------------------------------------

View 7 Replies View Related

UNION ALL Query - HELP!

Jul 20, 2005

I am having conceptual trouble with the following query:select r.ServiceID,r.ContractID,sum(Total)from (selectcsc.ServiceID,c.ContractID, sum(csc.ContainerMovement) as Totalfromiwms_tbl_CustomerSiteContainers csc,iwms_tbl_ContractLines cl,iwms_tbl_Contracts c,iwms_tbl_ContractLinePricing clpwhereclp.ContractLineID = cl.ContractLineID andclp.ContractPriceLineDescription = 'Rental' andclp.ContractPriceLineActive=1 andclp.ContractPriceLineExpiry > getdate() andcsc.ServiceID > 1 andc.ContractID = cl.ContractID andc.ContractStatusCode = 5 andcl.ServiceID = csc.ServiceIDgroup byc.ContractID,csc.ServiceIDunion allselectsi.ServiceID,c.ContractID,sum(j.QuantityCollected) -sum(j.QuantityDelivered)as Totalfromiwms_tbl_Jobs j,iwms_tbl_ServiceInstances si,iwms_tbl_ContractLines cl,iwms_tbl_Contracts c,iwms_tbl_ContractLinePricing clpwhereclp.ContractLineID = cl.ContractLineID andclp.ContractPriceLineDescription = 'Rental' andclp.ContractPriceLineActive=1 andclp.ContractPriceLineExpiry > getdate() andc.ContractID = cl.ContractID andc.ContractStatusCode = 5 andcl.ServiceID = si.ServiceID andj.JobStatusCode <> 80 andj.ServiceInstanceID = si.ServiceInstanceID andsi.ServiceID > 1group byc.ContractID,si.ServiceID) as rgroup byr.ContractID,r.ServiceIDhavingsum(Total) <> 0order by r.ContractIDIt returns 140 rows. However, if I comment out the first selectstatement inside the brackets (select csc.ServiceID,c.ContractID....union all) and run it, it returns 4,785 rows. If I comment out thesecond select statement (union all ...group byc.ContractID,si.ServiceID) it returns 4,786 rows. So why doesn't the*whole* thing return 9,571 rows? That's what I thought a UNION did -append the results of one select to the bottom of the second select.I will supply table defs if it will help, but there's a lot of stuffhere and I think it isn't a data question, but anI-don't-understand-the-SQL question!TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk

View 3 Replies View Related

SQL Query Help. UNION ALL

Jan 30, 2008

Hi, I have a question,

Im trying to use the UNION ALL statement to join my results.
I have multiple talbes, each table has the table name as a date: 28_1_2008, 29_1_2008, etc..
Some dates dont have tables.

What I want to do is write an sql query that gets all the results from a rage of dates..

ie. range : 27_1_2008 to 29_1_2008

What im doing :

sql1: Select * from 27_1_2008 UNION ALL select * from 28_1_2008 UNION ALL select * from 29_1_2008.

It works fine if all tables exist, but in my data base every date DOESNT NECESSARLY have a table.



ie. say I have tables 27_1_2008 , 28_1_2008, 30_1_2008

Now if I do

sql2: Select * from 27_1_2008 UNION ALL Select * from 28_1_2008
This works.

But,
sql3: Select * from 28_1_2008 UNION ALL Select * from 29_1_2008 UNION ALL Select * from 30_1_2008

Causes an error, and DOESNT return any records..




I need a way so that even if one particular table doesnt exist, the others should be returned.

i.e in sql3, it should skip the non existing "29_1_2008" table, and return the rest. As of now, it doesnt return anything,

Anyone with some suggestions?

View 10 Replies View Related

Union Query?

Feb 8, 2008



I have the following query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'LI_759') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value), CONVERT(varchar(15), DateTime, 108)

which produces the following:






LI_759
-0.001
13:28:20

The second query;

SELECT TOP 1 DateTime, TagName, Value, CONVERT(varchar(15), DateTime, 108) AS Time
FROM v_AnalogHistory
WHERE (DateTime >= CAST(CONVERT(VARCHAR(8), GETDATE() - 1, 112) AS DATETIME)) AND (DateTime < CAST(CONVERT(VARCHAR(8), GETDATE(), 112)
AS DATETIME)) AND (TagName = N'LI_759') AND (wwRetrievalMode = N'delta') AND (CONVERT(decimal(38, 3), Value) IS NOT NULL)
ORDER BY CONVERT(decimal(38, 3), Value) DESC, CONVERT(varchar(15), DateTime, 108) DESC

Produces the following:





LI_759
0.3661
06:09:30


I have tried the previous answers to this post and none of them worked, i get various errors saying sql is unable to parse. what I would like to have is one query that produces the following:

min:



LI_759
-0.001
13:28:20

max:




LI_759
0.3661
06:09:30

Thank You.

View 3 Replies View Related

UNION Query..need Help!!!!!

Apr 17, 2008



currently I am investing some time in learning SQL, I have been working with a sample db, and have come across UNION.
I see many examples showing the same thing, 2 tables with identical datatype and either with a alias or same name. My question is, can a UNION be used for more than 2 tables?
I currently have 3 tables: orders(customerNum,orderNum,orderDate), orderline(orderNum,partNum),
part(partNum,description)
I want to display orderdate and ordernum, only if partNum= 1234 or customerNum= 123
everything is char datatype

I am able to innerjoin, however I am somewhat confused attempting a UNION query. is this possible?
so far


select orders.orderNum,orderDate

from orders,orderline

where orders.orderNum=orderline.orderNum and customerNum='xyz'

union

select orderline.orderNum,part.partNum

from orderline,part

where part.partNum=orderline.partNum and orderline.partNum='abcd'

order by 1



as you can see i am somewhat lost. At the moment the orderDate column is showing partnum and dates. But if someone can show how to make this possible I would greatly appreciate it.

View 6 Replies View Related

Advanced Union Query

Sep 6, 2004

Hi!

I'm trying to get the following query to work:

SELECT item.name AS name, t15.f167 AS start, t15.f168 AS end, t15.f180 AS organizer
FROM item, t15
WHERE item.row_id = t15.id AND t15.f167 > getDate()
UNION ALL
SELECT item.name AS name, t30.f221 AS start, t30.f222 AS end, '<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id
+ '> KAN < / a > '
FROM item, t30
WHERE item.row_id = t30.id AND t30.f221 > getDate()
ORDER BY start DESC

The problem is the organizer field. I need to generate an url, but I get all sorts of problems with data types, ie. you can convert an int into ntext.

So the question is basically, how do I get the following to work:

'<a class=kalender_link href=http://www.xxx.no/default.asp?V_ITEM_ID=' + item.id + '> KAN < / a >'


Vidar

View 6 Replies View Related

Problems With UNION-query

Jun 15, 2004

I have a sql statement with 2 SELECT statements combined with the keyword UNION. Something like this

SELECT a, b
FROM ... WHERE ...
UNION
SELECT c, d
FROM ... WHERE ...

If i execute this in the Query Analizer everything works fine and i get the results. But if i execute this through Visual Basic (with database.OpenRecordSet(sql) ) i get following error message:

The Microsoft Jet database engine cannot find the input table or query .... Make sure it exists and that its name is spelled correctly.

If i execute the first part of this combined query in VB (e.g. SELECT a, b FROM ... WHERE ...), no errors occur. The same with the second part.

I find it really strange and any hints and tips are welcome!
thanks in advance.

View 2 Replies View Related

Union Query Discrepancy

Jan 26, 2005

When I run the following query with a UNION:

SELECT dbo.TBLCCINFORMATIONOCTOBER04.NAME, dbo.TBLCCINFORMATIONOCTOBER04.TITLE, LOWFARE, ITPSG.TBLCCONLINETOOL.AMOUNT as PRICE, 15 AS Lostsavings, LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) AS COMPANYCODE, ITPSG.TBLCCONLINETOOL.InYear, ITPSG.TBLCCONLINETOOL.InMonth, 'TRADITIONAL BOOKING' AS Reason FROM ITPSG.TBLCCONLINETOOL INNER JOIN dbo.TBLCCINFORMATIONOCTOBER04 ON ITPSG.TBLCCONLINETOOL.AWID = dbo.TBLCCINFORMATIONOCTOBER04.AWID AND ITPSG.TBLCCONLINETOOL.InMonth = dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH INNER JOIN dbo.TBLMONTHVALUE ON dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH = dbo.TBLMONTHVALUE.monthname WHERE LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) = '1038' AND INYEAR = '2004'AND InMonth = 'OCTOBER' AND (ITPSG.TBLCCONLINETOOL.DESTINATION = 'DOMESTIC') AND (ITPSG.TBLCCONLINETOOL.TYPE = 'TRADITIONAL') UNION SELECT dbo.TBLCCINFORMATIONOCTOBER04.NAME, dbo.TBLCCINFORMATIONOCTOBER04.TITLE, LOWFARE, ITPSG.TBLCCEXCEPTIONS.PRICE as PRICE, ITPSG.TBLCCEXCEPTIONS.Lostsavings AS Lostsavings, LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) AS COMPANYCODE, ITPSG.TBLCCEXCEPTIONS.InYear, ITPSG.TBLCCEXCEPTIONS.InMonth, ITPSG.TBLCCEXCEPTIONS.Reason FROM ITPSG.TBLCCEXCEPTIONS INNER JOIN dbo.TBLCCINFORMATIONOCTOBER04 ON ITPSG.TBLCCEXCEPTIONS.AWID = dbo.TBLCCINFORMATIONOCTOBER04.AWID AND ITPSG.TBLCCEXCEPTIONS.InMonth = dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH INNER JOIN dbo.TBLMONTHVALUE ON dbo.TBLCCINFORMATIONOCTOBER04.COLMONTH = dbo.TBLMONTHVALUE.monthname WHERE (LEFT(dbo.TBLCCINFORMATIONOCTOBER04.CostCtr, 4) = '1038') AND (ITPSG.TBLCCEXCEPTIONS.InYear = '2004') AND (ITPSG.TBLCCEXCEPTIONS.InMonth = 'OCTOBER') ORDER BY Reason

It returns these 16 records, 10 from the 1st table and 6 from the second. If I just remove the UNION operator and run them seperatly I get 11 from the 1st table and 6 from the second.

The record I am losing is the second of these two, but with the fields I am selecting they appear identical:
R,JosephField Operations Director INULL267.00001510382004octoberTRADITIONAL BOOKING
R,JosephField Operations Director INULL267.00001510382004octoberTRADITIONAL BOOKING

Is there any reason why the UNION statement is making that second record vanish? Is there a way I can alter the statement so I can run the query with the UNION and not lose records?

Thanks,

View 2 Replies View Related

Union Query Difference

Oct 8, 2007

Guys can you tell me whether there is any difference between the following queries.......... Both of them are resulting same number of records. Not sure whether the output is same or not. Just wanted to know if result output wise if there is no difference than performance wise there might be some. Kinldy educate me on the same.



SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.Items= SCC.CollectionGID
UNION
SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.PageBreakPositions= SCC.CollectionGID



SELECT * FROM FRProposalOutline FPO
INNER JOIN SC_Collection SCC
ON
FPO.Items= SCC.CollectionGID OR
FPO.PageBreakPositions= SCC.CollectionGID


Thanks,
Rahul Jha

View 14 Replies View Related

Query Help Requested - Union?

Mar 4, 2004

I need to all user IDs where the users have 3 permissions which are in a table holding about 100 different permissions

I need to find the user IDs like I do below but somehow have all three in the WHERE clause (I guess? but this does not work)

SELECT employees.employeeID FROM employees INNER JOIN employeePermissions ON employees.employeeID = employeePermissions.EmployeeID
AND employeePermissions.PermissionID = 'PROJECT_VIEW_ALL'
AND employeePermissions.PermissionID = 'PROJECT_MODIFY'
employeePermissions.PermissionID = 'PROJECT_DELETE'


the Permissions table has an EmployeeID column and a PermissionID column(which holds the permission name)

PLEASE HELP.

Thanks.

View 2 Replies View Related

Sorting In Query With Union

Jul 20, 2005

Hi,I am attempting to write a complex query to group sets of data. I have myquery working correctly, but i ran into a problem with sorting.I want to sort my query by a string field.Is there a way around sorting using a field other than numeric of a querycontaining a union?Thanks,Karen

View 1 Replies View Related

Problems With A Union Query

Mar 6, 2008

Hi Folks,

I am hoping someone can help me with a problem I have with a union query.

I have the following table structure

MyTable
[ColumnOne] [ColumnTwo] [ColumnThree]
Minuture Poodle Poodle Dog
Terrier - Border Terrier Dog
Alsatian Null Dog
Doberman Null Dog
Tiger Large Cat Cat
Lion Null Cat
Tabby Domestic Cat

And I want to perform union on ColumnOne and ColumnTwo to produce output that looks like this:
[OutputCol1] [OutputCol2]
Poodle Dog
Terrier Dog
Alsatian Dog
Doberman Dog
Large Cat Cat
Lion Cat
Domestic Cat

At the moment, I have the following:
Select ColumnOne from MyTable where ColumnTwo = null union select ColumnTwo from MyTable where not ColumnTwo = null

Which created OutputCol1. But how do I include OutputCol2? Any ideas - when i try this i get errors and cant see a way around this.

I hope the example is clear, any questions let me know,

Thanks

Morrislgn

View 1 Replies View Related

SQL Server 2014 :: Loop And Query CSV Files In Folder Using Union All Query To Form Resultant Table On Server?

Jun 27, 2014

I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:

SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],

[Code] ....

What i need is:

1] to create the resultant tbl_ALLCOMBINED table

2] transform this table using PIVOT command with following transformation as shown below:

PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'

3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?

P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.

View 9 Replies View Related

T-SQL (SS2K8) :: Using Union ALL Or Union Kills Performance On Stored Proc

Jun 12, 2014

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

View 4 Replies View Related

Optimize A UNION Query With LIKE (was Query Help)

Jan 30, 2005

Hi,

SQL 2K
Table loaded using BULK INSERT with ~5 000 000 records.
CREATE TABLE [MyTable] (
[SeqNumber] int NOT NULL ,-- sequence unique number
[ObjId] int NOT NULL ,
[HierarchyLevel] varchar(255) NULL ,-- highest level = 0
[NameText] varchar(255) NULL
.
.
)

insert into MyTable
select 1, 1, 0, 'text-10'
union all select 2, 1, 1, 'text-11 state-1'
union all select 3, 1, 2, 'text-12 social-1'
union all select 4, 1, 3, 'text-13 abc social-1.1'
union all select 5, 1, 1, 'text-11 123'
union all select 6, 1, 1, 'text-11 ABCDEF'
union all select 7, 1, 2, 'text-12 bbb'
union all select 8, 1, 3, 'text-13 social-2'
union all select 9, 1, 4, 'text-14 aaa'
union all select 10, 2, 1, 'text-21 state-3'
union all select 11, 3, 5, 'text-31 state-2 social-3'

- Unique Index on SeqNumber

Query:
Select top 100 TI.*
from [dbo].[MyTable] TI
inner join [dbo].[MyTable] TL
on TL.ObjId = TI.ObjId
where (TL.[NameText] like '%state%')
and TI.[NameText] like '%social%'
and TI.HierarchyLevel > TL.HierarchyLevel
and TI.SeqNumber > TL.SeqNumber
and not exists (select *
from [dbo].[MyTable] T3
where T3.ObjId = TL.ObjId
and T3.SeqNumber > TL.SeqNumber
and T3.SeqNumber < TI.SeqNumber
and T3.HierarchyLevel <= TL.HierarchyLevel)
Union
Select T4.*
from [dbo].[MyTable] T4
where T4.[NameText] like '%state%'
and T4.[NameText] like '%social%'
order by Ti.SeqNumber

Result:
SeqNumber ObjId HierarchyLevel NameText
312text-12 social-1
413text-13 abc social-1.1
1135text-31 state-2 social-3

Could somebody help me please to optimize this query?

View 14 Replies View Related

Help With A SQL Union Query Please. Conversion Errors!

Aug 29, 2007

Hi, I've created a SQL union query that grabs the top three values of a test and puts the results together on one line item by a recordnumber.
The only problem is, one value is generating the error: " Syntax error converting the varchar value '=' to a column of data type int."
This field is an operator field, so it will only display operator values ( <, >, = ).  Everything seems to work BUT the union join on that column, which is a varchar data type.  I need this union to work, and Im frustrated that I can't seem to figure out why I can't get the logic to work.  Can someone take a look at this and help me with it?
The union query looks like this:

exec('select
RecordNumber,Sum(rank1) as HbA1CRank1,max(Operator1) as HbA1COperator1,sum(contentValue1) as HbA1CContentvalue1,max(dos1) as HbA1CDOS1,Sum(rank2) as HbA1CRank2,max(Operator2) as HbA1COperator2,sum(contentValue2) as HbA1CContentvalue2,max(dos2) as HbA1CDOS2,Sum(rank3) as HbA1CRank3,max(Operator3) as HbA1COperator3,sum(contentValue3) as HbA1CContentvalue3,max(dos3) as HbA1CDOS3
from
(SELECT DISTINCT                       TOP 100 PERCENT recordnumber, Rank AS rank1, cast(Operator as varchar) as Operator1, contentValue AS contentvalue1, DOS AS DOS1, 0 AS rank2, 0 as Operator2,  0 AS contentvalue2, 0 AS DOS2,                       0 AS rank3, 0 as Operator3, 0 AS contentvalue3, 0 AS DOS3FROM         (SELECT     (SELECT     COUNT(*)                                               FROM         tblDiabetic_HgbA1C_Total vw1                                               WHERE     vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *                       FROM          tblDiabetic_HgbA1C_Total vw2) vw3WHERE     (Rank = 0)ORDER BY RecordNumber
UNION
SELECT DISTINCT                       TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1, 0 AS contentvalue1, 0 AS DOS1, Rank AS rank2, cast(Operator as varchar) as Operator2, contentValue AS contentvalue2, DOS AS DOS2,                       0 AS rank3, 0 as operator3, 0 AS contentvalue3, 0 AS DOS3FROM         (SELECT     (SELECT     COUNT(*)                                               FROM         tblDiabetic_HgbA1C_Total vw1                                               WHERE     vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *                       FROM          tblDiabetic_HgbA1C_Total vw2) vw3WHERE     (Rank = 1)ORDER BY RecordNumber
UNION
SELECT DISTINCT                       TOP 100 PERCENT recordnumber, 0 AS rank1, 0 as Operator1,  0 AS contentvalue1, 0 AS DOS1, 0 AS rank2, 0 as operator2, 0 AS contentvalue2, 0 AS DOS2, rank AS rank3,  cast(Operator as varchar) as operator3,                      contentvalue AS contentvalue3, DOS AS DOS3FROM         (SELECT     (SELECT     COUNT(*)                                               FROM         tblDiabetic_HgbA1C_Total vw1                                               WHERE     vw1.rECORDnUMBER = vw2.rECORDnUMBER AND vw1.ItemCode = vw2.ItemCode AND vw1.Operator = vw2.Operator AND vw1.DOS > vw2.DOS) AS Rank, *                       FROM          tblDiabetic_HgbA1C_Total vw2) vw3WHERE     (Rank = 2)ORDER BY RecordNumber )tblHgA1C
group by RecordNumber
')GO
Can anyone help?  It looks right to me, I just can't figure out why the error keeps coming up =
Thank you!

View 4 Replies View Related

Create Union Query Result

Jun 20, 2008

Hi,I was wondering if someone can help me.  I have 4 tables in an SQL Server 2005 database, for purposes sake called 'table1', 'table2', 'table3' and 'table4'. They all have the same data structure between them.  The columns inside them have the exact same design.  For purposes sake called 'column1', 'column2', 'column3' and 'column4'.  Alot of these tables have duplicate records scattered between them, so basically I want to create 'table5' with the same column names but only unique records.  Can someone specify the syntax I need for this.  I'm pretty sure it's a union query I need so am scouring as I type this also.   Many Thanks Robert  

View 5 Replies View Related

Sqldataadapter Problem Using Union Query

Feb 28, 2005

hello i have this query but the data adapter wont accept it

i get this error

syntax error or access violation

if i hardcode the @person parameter in query analyzier it works fine.

ex: @person = 33 hardcoded

but it doesnt work in data adapter either 33 or @person

can someone please help me out




SELECT m.title, m.movieID, a.personID, 'Actor', p.firstName + ' ' + p.lastName
FROM movieactors a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Director', p.firstName + ' ' + p.lastName
FROM moviedirectors a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Writer', p.firstName + ' ' + p.lastName
FROM moviewriters a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Cinematographer', p.firstName + ' ' + p.lastName
FROM moviecinematographers a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person
UNION
(SELECT m.title, m.movieID, a.personID, 'Producer', p.firstName + ' ' + p.lastName
FROM movieproducers a, people p, movies m
WHERE a.personID = p.personID
AND a.movieID = m.movieID
and a.personid = @person))));

View 2 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







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