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.
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
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
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
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)
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
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.
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)
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
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
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--------------------------------------
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
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,
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:
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.
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 >'
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.
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?
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
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
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,
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.
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]
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
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!
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
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))));
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.