Column Name In Union All
Jan 24, 2014
I have a union all query from 2 databases that I would like to have the same column name returned on it
Code is below
SELECT ‘Managed Data’ as Table1,
NLPostedNominalTran.NLPostedNominalTranID, NLNominalAccount.AccountNumber, NLNominalAccount.AccountName, NLNominalAccount.AccountCostCentre, NLNominalAccount.AccountDepartment, SYSAccountingPeriod.PeriodNumber, NLPostedNominalTran.NLNominalTranTypeID, NLPostedNominalTran.TransactionDate,
[Code] ....
View 3 Replies
ADVERTISEMENT
Aug 22, 2006
Hi there,
With a Union all Query is there a way to have it also generate
an Auto Increment (number) column that is appended to the Union all results
?
View 2 Replies
View Related
Feb 3, 2005
I have a problem with a union all that seems to be changing the results of a column based on using the union, when I run the querys separatly I get the expected results in the field!
Basically 3 queries pulled together for reporting purposes.
Orders (which can include a gift so the gifts are separated in the second query).
OrderGifts (pulled out of the orders).
Gifts (straight gifts with no associated orders).
The problem is showing up in the first ORDERS query. I can pull a specific order and the calculation for the AMOUNT(eg:$15.20) is correct, but when I combine the tables I get a different AMOUNT(eg:$15.199999999999999).
Here are the values for the fields in the amount calc:
totitems=12.9500
totcredits=.0000
discount=.0000
tax=.0000
refund=.0000
convrate=1.0
postage=2.2500
giftamt=15.0000
I tried converting each of the fields to an decimal befre the calc and same results
I tried ROUND and same thing!
I have been trying to chase this down all day, cant figure out what the problem is or how to get around it...whats going on here I am missing?
Here is the query, if you need any other info just let me know, I would GREATLY appreciate ANY direction of figuring this out!
select * from
(
--orders
select
acctnbr as PartnerId,
'O' as TrnType,
ordnbr as TrnId,
0 as FundId,
odate as WorkDate,
adate as PostDate,
sdate as ShipDate,
cast(
(
IsNull(totitems, 0) -
IsNull(totcredits, 0) -
IsNull(discount, 0) -
IsNull(tax, 0) -
IsNull(refund, 0) +
IsNull(postage, 0)
) * IsNull(convrate, 1)
as Decimal(10,2))
as Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(0,motvcode) as ProjMotvName
from kmai.dbo.o01omst
UNION all
--orders gifts
select
acctnbr as PartnerId,
'G' as TrnType,
giftref as TrnId,
giftfundid as FundId,
odate as WorkDate,
null as PostDate,
null as ShipDate,
ROUND((giftamt * IsNull(convrate, 1)),2) as Amount,
batchnbr as BatchId,
giftmotvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(giftfundid,giftmotvcode) as ProjMotvName
from kmai.dbo.o01omst
where adate is null
and giftfundid <> ''
UNION ALL
--gifts
select
acctnbr as PartnerId,
'G' as TrnType,
gh.giftref as TrnId,
convert(int, fundid) as FundId,
gdate as WorkDate,
applydate as PostDate,
null as ShipDate,
ROUND((IsNull(gd.amt, 0) * IsNull(convrate, 1)),2) AS Amount,
batchnbr as BatchId,
motvcode as MotivationCode,
currcode as CurrencyType,
Status,
paycode as PaymentType,
paytype as CardType,
paynbr as CardNbr,
expire as CardExpire,
ChargeAuth,
ConvRate,
MediaCode,
hcf.dbo.KmaiProjMotvTest(fundid,motvcode) as ProjMotvName
from kmai.dbo.g03ghdr gh
inner join kmai..g04gdtl gd
on gh.giftref = gd.giftref
)
UnionOfGiftsAndOrders
View 4 Replies
View Related
Jul 15, 2013
Trying to figure out a SQL query.I have 2 identical tables: Table A and Table B..This query doesnt seem to be working:
SELECT * FROM (
SELECT ClientID, sum(ABC), count(*)
FROM Table A
Where <condition>
[code]...
The result I am looking for is that for each ClientID, I need the total sum of ABC and total count.
View 4 Replies
View Related
Mar 9, 2008
I have a query similar to the following, but it gives the following error:
'Invalid column name 'GroupName'.'
SELECT 'Primary' as GroupName, City
FROM PrimaryTable
UNION ALL
SELECT SpecialGroupName AS GroupName, City
FROM Table2
GROUP BY GroupName, City
GroupName is varchar(30)
Any ideas why this would fail. Thanks in advance.
Terry
View 6 Replies
View Related
Sep 9, 2004
Hello All,
We all were new at one point.... any help is appreciated.
Objective:
Combining two 49,000 row tables and remove records where there is only 1 column difference. (keeping the specified column value removing the one with a blank.)
Reason:
I have 2 people going through a list, coding a specific column with a single letter value. They both have different progress on each sheet. Hence I am trying to UNION them and have a result of their combined efforts without duplicates.
My progress/where I'm stuck:
Here is my first query/union:
SELECT * FROM [Eds table]
UNION SELECT * FROM [Vickis table];
As shown above, I have unioned these 2 tables and my results removed th obvious whole record duplicates, but since 1 column is different on these, a union without criteria considers them unique.....
an example of duplicates that I must remove are as follows:
142301 - Product 5000 - 150# - S (Keep)
142031 - Product 5000 - 150# - "" <--- Blank (Remove)
I am trying to run another query on my first query results so I don't mess my first query up. Here it is:
SELECT DISTINCT [Prod #], [Prod Name], [Prod Description], [Product Type]
FROM [Combined Tables]
WHERE [Product Type]<>" ";
Please Help! Thank you in advance.
--------------------
5 minutes away from pulling my last one!
BaldNAskewed
View 7 Replies
View Related
May 19, 2015
I want to create a raw SQL resultset for outputting to Excel with some artificial headers transposed over the top of the 2nd part of the Union's column names. The first part of the Union will be the Headers. Like this, the space to the left of the topmost columns is preferably empty ....
COL 1 COL2 COL3 COL4 etc. BEH BIG BPL etc.
************************************* INTAKT DEFEKT INTAKT DEFEKT INTAKT
************************************* B E B E B
I just want the text above as a 3 line header and there won't be any values obviously. Then the 2nd query will be joined to the above with a Union all. The 2nd query has all the same column names as what will be given in the first set above. What is the SQL Syntax for doing so? Do I have to use a from clause?
View 4 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
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
Nov 6, 2006
Hi all,
I have a Union All transformation with 4 inputs and one output when I debug the package the sum of the different inputs rows does not match the row count in output.
I don't understand, I've used the Union All transform many times and I've never seen this.
Any idea why this could happen ?
View 18 Replies
View Related
Jan 23, 2001
Hi,
I am using DTS to import data where the query involves the UNION .
Select a,z,y from x
UNION
select b,z,y from y;
Only the columns Z,y appearing in the output. Is there any known problem with UNION in DTS?
Thanks
View 3 Replies
View Related
Dec 3, 2004
Help me again . :) ..
Query :
Select 1 AS ColA, ColB,ColC
FROM Table1
Where ColA = 1
Union
Select 2 AS ColA, ColB,ColC
FROM Table1
Where ColA = 2
Expected Result.
ColA ColB ColC
1 Apple Sweet
1 Wine Alcohol
2 Home Nice
2 Travel Fun
But gives
ColA ColB ColC
1 Apple Sweet
1 Wine Alcohol
1 Home Nice
1 Travel Fun
any suggestions .please reply.
View 1 Replies
View Related
Mar 23, 2007
Could someone explain me Union function in MDX? How is different from the T-SQL Union?
My need is urgent.
View 1 Replies
View Related
Jul 12, 2004
i have a table with country names
table name:country
column name:country
it is populated with:
UK
USA
africa
asia
japan
i want a query which gives the foll output
USA
UK
africa
asia
japan
(i.e) USA and UK must be on the top 2 rows
while the rest of the countries should come below these 2 and sorted in the ascending order.
if it is possible with any other query also it is ok
View 1 Replies
View Related
Jul 14, 2004
Hi
I just found out that I can do an ORDER BY clause on entire records set retrieve from a query that combines several sub queries with UNION from different tables with the same structure... so this is great to know, BTW, is this a new feature of MSSQL 2K ? I don't recall being able to do this in MSSQL 7 or 6.5.
Anyway, the main question is, can I use the TOP command in a query that has UNION in it?? Meaning, there are two queries (or more) from two tables (or more) and I need to fetch the top 10 records by an ORDER BY clause from the combined results, when I try to add each sub query TOP 10 the results are not correct at all, when I try to add TOP 10 only to the first query hoping that the analyzer will refer to the whole query, it's selecting TOP 10 from the first query and combines it with all the records from the others...
So, can anyone help? I hope the problem is understood.
Thank you,
Inon.
View 2 Replies
View Related
Nov 8, 2004
Hi!
I am trying to join to different queries into one table ( I accomplished this)
Next I need to ADD or SUM the results of 2 rows to form a single row.
As you can see in the query below, I run 2 separate queries and use ' ' as a place holder for the UNION to work. I get duplicate rows, one with a value and the other with a '0'. I want to have a single row.
Any help is greatly appreciated!
(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
Count(ProdID0) as '# copies installed',
'' as '# legitimate copies installed'
FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
GROUP BY
v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )
UNION
(SELECT
v_gs_supportedpackages.ProdID0 as 'Product Name',
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 as 'Site',
'' as '# copies installed',
Count(ProdID0) as '# legitimate copies installed'
FROM
v_R_System SYS,
v_GS_Workstation_Status HWSCAN,
v_gs_SupportedPackages
inner join
v_RA_System_SMSInstalledSites on v_RA_System_SMSInstalledSites.ResourceID = v_gs_SupportedPackages.ResourceID
WHERE
SYS.ResourceId = HWSCAN.ResourceId
AND
SYS.ResourceId = v_gs_SupportedPackages.ResourceId
AND
v_gs_supportedpackages.ProdID0 = substring('MS Security Patch MS04-030,031,032,034,037,038',1,60)
AND
DateDiff(Day,HWSCAN.LastHWScan,GetDate()) <= '20'
GROUP BY
v_gs_supportedpackages.ProdID0,
v_RA_System_SMSInstalledSites.SMS_Installed_Sites0 )
View 4 Replies
View Related
Dec 15, 2006
Just a pet peeve....
From BOL:
By default, the UNION operator removes duplicate rows from the result set.
If you use ALL, all rows are included in the results and duplicates are not
removed.
Why is it assumed that one would want the duplicates removed by default?
Isn't that what SELECT DISINCT is for?
View 1 Replies
View Related
Mar 18, 2004
I was wondering if there is a way I could write this query as one query using union instead of 3 different queries?
SELECT columna as column,count(ID) as Applications
FROM tablea
GROUP columna
SELECT columna as column2, count(ID) as Approved
FROM tablesa where substring(APP_DATE,1,6) >'200304' and in 'Approved')
GROUP BY columna
SELECT columna as column3, count(ID) as Booked, sum(AMT) as amt, sum(AMT)/count(ID) as lavg
from tablea where substring(APP_DATE,1,6) >'200304' and STATUS in('book')
group by columna
View 2 Replies
View Related
Apr 22, 2008
Hi All,
I am doing a UNION of 8 views(with 3 million rows in each view approx). I am sure that there is NO DUPLICATE data between the views. SO specifying a UNION ALL would be better than specifying a UNION in this case?
Thanks!
Prakash.P
The secret to creativity is knowing how to hide your sources!
View 8 Replies
View Related
Jun 20, 2008
Hi folks,
I have a question about the performance of the following query:
(SELECT name, lastName
from Data
where salary >= 2200
)
union
(SELECT name, lastName
from Data
where salary >= 2200
) union
(SELECT name, lastName
from Data
where salary >= 2200
)
How that query works? Is it slow for a larger data? Does it do 3 queries and then combine them?
Thank you.
View 3 Replies
View Related
Feb 11, 2008
Hi All
I want to do the following report and I want the 2nd select statement to appear at the bottom to display the totals
SELECT Item,B.Mar, B.Apr, B.May, B.Jun,B.Jul, B.Aug, B.Sep, B.Oct, B.Nov, B.Dec, B.Jan
FROM fncWineSales(2007) AS A
UNION
SELECT 'Total',SUM(B.Mar), SUM(B.Apr), SUM(B.May), SUM(B.Jun),SUM(B.Jul), SUM(B.Aug), SUM(B.Sep), SUM(B.Oct), SUM(B.Nov), SUM(B.Dec), SUM(B.Jan)
FROM dbo.fncWineSales (2007) AS B
thanx in advance
View 11 Replies
View Related
Jul 23, 2005
Hello,Bear with me (not had much sleep last night), pls see following ddl,dml and comments for what is desired, I don't have a problem gettingdesired result(s), however, I'm wondering if there's another (better)solution than UNION operator in this case. TIA.-- DDLCREATE TABLE #TMP (col varchar(10));-- DMLinsert into #TMPvalues('A124');insert into #TMPvalues('A127');insert into #TMPvalues('A12728');insert into #TMPvalues('A17282');insert into #TMPvalues('BCD');insert into #TMPvalues('BCD');insert into #TMPvalues('CDSS');insert into #TMPvalues('DS');insert into #TMPvalues('YUUEI');-- goal: get one row with col data starting with 'A' and distict rowsfor the restselect top 1 colfrom #TMPwhere col LIKE 'A%'UNIONselect distinct colfrom #TMPwhere col NOT LIKE 'A%'
View 4 Replies
View Related
Jul 20, 2005
Hi,I can return results of a union easily enough, but I wish to sum the recordsfirst. Is this possible in a single SQL statement? Or do I have to useseparate ones to sum up after the union?Cheers,Chris
View 2 Replies
View Related
Aug 20, 2007
Why if run this script, Always Incorrect syntax near the keyword 'group'.
somebody helpme, how to sum the union script
SELECT NUMMSTR3, sum(qty), sum (AP)
FROM (
SELECT c.NUMMSTR3, count(*) qty,sum(a.DEBIT) DEBIT,sum(a.CRDT) CRDT,Sum(a.CRDT - a.DEBIT) AP
FROM MSTR1 a,MSTR2 b,MSTR3 c
where a.IDMSTR1 = b.IDMSTR1
and a.KDMSTR1 = c.KDMSTR3
and c.NUMMSTR3 = '21010100'
and year(b.TRXDATE) <= '2007'
group by c.NUMMSTR3
UNION ALL
SELECT c.NUMMSTR3, count(*) jml,sum(a.DEBIT) DEBIT,sum(a.CRDT) CRDT,Sum(a.CRDT - a.DEBIT) AP
FROM HISTR4 d,MSTR3 c
where d.KDHISTR4 = c.KDMSTR3
and c.NUMMSTR3 = '21010100'
group by c.NUMMSTR3
) group by NUMMSTR3
View 5 Replies
View Related
Jul 5, 2006
Of the two queries below, the first returns the desired result, the union of the select-except statments. Why doesn't the second query return the same result as the first? Is this a mistake/bug on Microsofts side?
select * from
(select * from TEST1
EXCEPT
select * from TEST2) AS A
UNION
select * from (select * from TEST2
EXCEPT
select * from TEST1) AS B
select * from TEST1
EXCEPT
select * from TEST2
UNION
select * from TEST2
EXCEPT
select * from TEST1
View 3 Replies
View Related
Sep 3, 2006
When any changes have been made to the underlying table structure, the Union all reports error and does not automatically correct the error. I then have to delete it and recreate it. Is this a bug?
View 3 Replies
View Related
Apr 22, 2008
hi need help
i have tow tables
no primary key
evry table only one ROW
tb1
fld1 fld2 fld3 fld4 fld5 fld6
---------------------------------------
2 4 5 6 7 8
tb2
fld1 fld2 fld3 fld4 fld5 fld6
---------------------------------------
6 1 2 4 1 9
i need to summing the tow table like this (evry table only one ROW)
view_SUM (summing)
fld1 fld2 fld3 fld4 fld5 fld6
---------------------------------------
8 5 7 10 8 17
how to do
select * from tb1
union all --------------SUM ?
select * from tb2
TNX
View 1 Replies
View Related
Feb 27, 2008
Hello all can anybody help me out with this one?
These are the errors that I am getting. The 2nd error only comes up when I try to put in "UNION". Any help would be very much appreciated. Thank you!
Msg 156, Level 15, State 1, Procedure searchUser, Line 79
Incorrect syntax near the keyword 'UNION'.
Msg 102, Level 15, State 1, Procedure searchUser, Line 100
Incorrect syntax near 'END'.
Code Snippet
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[searchUser]
-- Add the parameters for the stored procedure here
@Username VARCHAR(25),
@AppID INT,
@ResID INT,
@Access VARCHAR(5),
@Region INT
AS
BEGIN
IF @Username IS NULL
BEGIN
IF @Access IS NULL
BEGIN
PRINT 1
SELECT a.XID, a.AppID, a.ResID,
b.AppDescription,
c.ResDescription,
d.Username, d.Custom, d.Region
FROM [Application] a INNER JOIN ApplicationName b
ON a.AppID = b.AppID LEFT JOIN ResTable c
ON a.ResID = c.ResID LEFT JOIN AppResCustom d
ON a.XID = d.XID
WHERE a.AppID = @AppID
AND a.ResID = @ResID
END
ELSE
BEGIN
PRINT 2
SELECT a.XID, a.AppID, a.ResID,
b.AppDescription,
c.ResDescription,
d.Username, d.Custom, d.Region
FROM [Application] a INNER JOIN ApplicationName b
ON a.AppID = b.AppID LEFT JOIN ResTable c
ON a.ResID = c.ResID LEFT JOIN AppResCustom d
ON a.XID = d.XID
WHERE a.AppID = @AppID
AND a.ResID = @ResID
AND d.Custom = @Access
END
END
ELSE
BEGIN
PRINT 3
SELECT a.XID, a.AppID, a.ResID,
b.AppDescription,
c.ResDescription,
d.Username, d.Custom, d.Region
FROM [Application] a INNER JOIN ApplicationName b
ON a.AppID = b.AppID LEFT JOIN ResTable c
ON a.ResID = c.ResID LEFT JOIN AppResCustom d
ON a.XID = d.XID
WHERE d.Username = @Username
AND a.AppID = @AppID
AND a.ResID = @ResID
AND d.Custom = @Access
END
UNION
PRINT 4
SELECT a.XID, a.AppID, a.ResID,
b.AppDescription,
c.ResDescription,
d.Username, d.Custom, d.Region
FROM [Application] a INNER JOIN ApplicationName b
ON a.AppID = b.AppID LEFT JOIN ResTable c
ON a.ResID = c.ResID LEFT JOIN AppResCustom d
ON a.XID = d.XID
WHERE d.Username = @Username
AND a.AppID = @AppID
AND a.ResID = @ResID
AND d.Custom = @Access
AND d.Region = @Region
END
View 4 Replies
View Related
Jun 1, 2006
I want to know the top 5 patterns in sales, the report will like this:
Month Top1 Top2 Top3 Top4 Top5
2005-06 A1 A2 A3 A4 A5
2005-07 B1 B2 B3 B4 B5
2005-08 C1 C2 C3 C4 C5
--- --- ---
What I did is:
declare @StartDate and @EndDate, set @StartDate and @EndDate
Delete AAAA (AAAA is a table)
Begin
While (@EndDate<somedate)
Insert into AAAA(sales, Month, Pattern)
Select TOP 5 sales, Month,pattern from sometables order by sales DESC
increase @StartDate and @EndDate by a month
End
Select * from AAAA
It works fine. My question is: Can I get rid of table AAAA? Is there a better way that just use Top 5 combine with something say Union? ( I tried Union and failed )
Thanks in advance,
Long
View 4 Replies
View Related
Dec 28, 2007
Hi All,
Pls look at the following:
http://blogs.conchango.com/jamiethomson/archive/2006/07/10/SSIS_3A00_-MULTICAST-bug.aspx
The first comment, by 'zpeceno', exactly describes a problem that I have hit upon.
However despite looking thro the list of issues resolved in SP1, I've found no mention of it.
Any chance I could get some offical comment re. the bug described, when it was(/has it been?) officially acknowledged, and what service pack (if any) resolves it?
Many thanks,
View 3 Replies
View Related
Feb 25, 2007
hi!
What do you think is the error in this sql squery?
What I'm trying to do is to get all the records having dates from 2007/01/21 to 2007/02/20 where
YearFr =2007 MonthFr =01 DayFrom =21 YearTo =2007 MonthTo =02 DayTo =20
By the way, this is part of my code: '||||| Open connection
MyConn.Open()
'||||| Create Command Object for filtering time ins and time outs base from Year and Month
Dim odbcCommand_date As OdbcCommand = New OdbcCommand("Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day > ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day UNION Select TT0001.Year, TT0001.Month,TT0001.Day, TT0001.in_hh, TT0001.in_mi,TT0001.out_hh,TT0001.out_mi,TM1001.kbn_name from TT0001 inner join TM1001 on TT0001.kintai_status = TM1001.kbn where TT0001.syain_id =? AND TT0001.Year = ? and TT0001.Month = ? and TT0001.Day = ? or TT0001.Day < ? ORDER BY TT0001.Year,TT0001.Month,TT0001.Day", MyConn)
'||||| Add Parameters and set values.
odbcCommand_date.Parameters.Add("@P1", OdbcType.VarChar).Value = label_emp_id.Text.ToString
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = YearFr
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = MonthFr
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayFrom
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayFrom
odbcCommand_date.Parameters.Add("@P1", OdbcType.VarChar).Value = label_emp_id.Text.ToString
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = YearTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = MonthTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayTo
odbcCommand_date.Parameters.Add("@P1", OdbcType.Char).Value = DayTo
The error is in the UNION part of the query...
PLs help me.
Thanks!
Sheila
View 1 Replies
View Related
Jul 8, 2007
I have n Tables: T_1, T_2, ... T_n that all have the same exact fields/columns.
Ultimately, I want to search through ALL n tables and return a single table of relevant results from all tables, arranged in order by rank.
I'm not sure if this is possible. So far, I have:
1 SELECT RANK, field_1, field_2, ..., field_m FROM
2 (
3 SELECT RANK, field_1, field_2, ..., field_m FROM T_1,
4 CONTAINSTABLE(T_1, field_i,@searchText) searchTable
5 WHERE KEY = T_1.field_i
6 UNION
7 SELECT RANK, field_1, field_2, ..., field_m FROM T_2,
8 CONTAINSTABLE(T_2, field_i,@searchText) searchTable
9 WHERE KEY = T_2.field_i
10 UNION
11 .
12 .
13 .
14 UNION
15 SELECT RANK, field_1, field_2, ... field_m FROM T_n,
16 CONTAINSTABLE(T_n, field_i,@searchText) searchTable
17 WHERE KEY = T_n.field_i
18 )
19 ORDER BY RANK DESC
I haven't tried it yet, but it seems wrong. How do we actually do this?
View 3 Replies
View Related
Mar 19, 2008
hi,i have a union statement that works like a charm:SELECT
[Name],
[EventID] AS [ItemID],
[TourID],
[Date],
NULL AS [StartDate],
[Date] AS [SortDate],
[Type] FROM [Events]
WHERE
[TourID] IS NULL
AND
AccessLevel <= @AuthenticationLevel
UNION
SELECT
[Name],
[TourID] AS [ItemID],
[TourID],
NULL AS [Date],
[StartDate],
[StartDate] AS [SortDate],
'2' AS [Type]
FROM
[Tours]
WHERE
AccessLevel <= @AuthenticationLevel
ORDER BY
[SortDate] this statement selects all the records i want from both tables, and orders the entire new table by sort date.what i want to do is to add a limitation on the number of records. i have tried using both LIMIT and TOP commands, but i can't get it working. both of the below statements give me a syntax error saying "Incorrect syntax near the keyword 'ORDER'"first try:SELECT * FROM (<same_statement_as_above>) ORDER BY [SortDate] LIMIT 2second try:SELECT TOP 2 * FROM (<same_statement_as_above>) ORDER BY [SortDate]what am i doing wrong, and how do i achieve what i want?i am working with mssql server 2005 (express locally, and standard on my hosting service). thanks for your help!
View 2 Replies
View Related
