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