February 16, 2012 at 11:30 am
first i cant get this query to insert the records. it shows 'Insert Error: Column name or number of supplied values does not match table definition.'
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblemail1','U') IS NOT NULL
DROP TABLE #tblemail1
IF OBJECT_ID('TempDB..#tblemail2','U') IS NOT NULL
DROP TABLE #tblemail2
--===== Create the test table with
CREATE TABLE #tblemail1
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receive] datetime,
[subject] varchar(20)
)
CREATE TABLE #tblemail2
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receive] datetime,
[subject] varchar(20)
)
--SET IDENTITY_INSERT #tblemail ON
insert into #tblemail1 values('srv-one','Claims Mail',CONVERT(DATETIME,'2/14/2012 14:45',103),';N8> Salem Imagine & Learn Childcare et al; Salem Insured');
insert into #tblemail2 values('srv-one','Claims Mail',CONVERT(DATETIME,'2/16/2012 12:37',103),'LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no ');
insert into #tblemail1 values('srv-two','Claims Mail',CONVERT(DATETIME,'2/14/2012 13:22',103),';N8> Salem Imagine & Learn Childcare et al; Salem Insured: Ralphaele Timpani');
insert into #tblemail2 values('srv-two','Claims Mail',CONVERT(DATETIME,'2/14/2012 14:35',103),'LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no WJR');
insert into #tblemail1 values('srv-two','Show Record Mail',CONVERT(DATETIME,'2/14/2012 14:35',103),'t/a JTL Pharmacy; our file no WJR');
--SET IDENTITY_INSERT #tblemail off
select * from #tblemail
second, i cant get this query to only show the last record as the subject lines do not show the same first five characters.
note - in the end i'll be running this in a MS Access database
SELECT 'srv-one' as server,'Claims Mail' as account, kc.received, kc.subject
FROM #tblemail1 kc, #tblemail2 bc
WHERE left(bc.subject,5) <>left(kc.subject,5)
UNION SELECT 'srv-two' as server,'Claims Mail' as account, bc.receivedate, bc.subject
FROM #tblemail1 kc, #tblemail2 bc
WHERE left(kc.subject,5) <>left(bc.subject,5);
February 16, 2012 at 11:52 am
foscsamuels (2/16/2012)
note - in the end i'll be running this in a MS Access database
before we go any further.....will this be an Access mdb/accdb....or is it an Access Front End to a SQL backend?
If SQL is the backend....what version of SQL? ,,,you have posted in the SQL7/2000 forum.
Once we have this clarified then perhaps we can start with what is wrong with your code.
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 16, 2012 at 11:54 am
foscsamuels (2/16/2012)
first i cant get this query to insert the records. it shows 'Insert Error: Column name or number of supplied values does not match table definition.'
--===== If the test table already exists, drop itIF OBJECT_ID('TempDB..#tblemail1','U') IS NOT NULL
DROP TABLE #tblemail1
IF OBJECT_ID('TempDB..#tblemail2','U') IS NOT NULL
DROP TABLE #tblemail2
--===== Create the test table with
CREATE TABLE #tblemail1
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receive] datetime,
[subject] varchar(20)
)
CREATE TABLE #tblemail2
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receive] datetime,
[subject] varchar(20)
)
--SET IDENTITY_INSERT #tblemail ON
insert into #tblemail1 values('srv-one','Claims Mail',CONVERT(DATETIME,'2/14/2012 14:45',103),';N8> Salem Imagine & Learn Childcare et al; Salem Insured');
insert into #tblemail2 values('srv-one','Claims Mail',CONVERT(DATETIME,'2/16/2012 12:37',103),'LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no ');
insert into #tblemail1 values('srv-two','Claims Mail',CONVERT(DATETIME,'2/14/2012 13:22',103),';N8> Salem Imagine & Learn Childcare et al; Salem Insured: Ralphaele Timpani');
insert into #tblemail2 values('srv-two','Claims Mail',CONVERT(DATETIME,'2/14/2012 14:35',103),'LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no WJR');
insert into #tblemail1 values('srv-two','Show Record Mail',CONVERT(DATETIME,'2/14/2012 14:35',103),'t/a JTL Pharmacy; our file no WJR');
--SET IDENTITY_INSERT #tblemail off
select * from #tblemail
second, i cant get this query to only show the last record as the subject lines do not show the same first five characters.
note - in the end i'll be running this in a MS Access database
SELECT 'srv-one' as server,'Claims Mail' as account, kc.received, kc.subjectFROM #tblemail1 kc, #tblemail2 bc
WHERE left(bc.subject,5) <>left(kc.subject,5)
UNION SELECT 'srv-two' as server,'Claims Mail' as account, bc.receivedate, bc.subject
FROM #tblemail1 kc, #tblemail2 bc
WHERE left(kc.subject,5) <>left(bc.subject,5);
One possible problem with the insert statements is that the subject constants in the insert statements look to be well over 20 characters in length, and that conflicts with the temporary table definition line " [subject] varchar(20)".
February 16, 2012 at 1:26 pm
i updated the sample data query using varchar 200 and removed the explicit convert
I've tested this code using SQL 2005 (v9), maybe this should be moved to tsql 2005?
the access db im using has a linked table to a sql 2005 and other tables linked to a pst.
access 2003
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblemail1','U') IS NOT NULL
DROP TABLE #tblemail1
IF OBJECT_ID('TempDB..#tblemail2','U') IS NOT NULL
DROP TABLE #tblemail2
--===== Create the test table with
CREATE TABLE #tblemail1
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receive] datetime,
[subject] varchar(200)
)
CREATE TABLE #tblemail2
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receive] datetime,
[subject] varchar(200)
)
--SET IDENTITY_INSERT #tblemail ON
insert into #tblemail1 values('srv-one','Claims Mail','2/14/2012 14:45',';N8> Salem Imagine & Learn Childcare et al; Salem Insured');
insert into #tblemail2 values('srv-one','Claims Mail','2/16/2012 12:37','LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no ');
insert into #tblemail1 values('srv-two','Claims Mail','2/14/2012 13:22',';N8> Salem Imagine & Learn Childcare et al; Salem Insured: Ralphaele Timpani');
insert into #tblemail2 values('srv-two','Claims Mail','2/14/2012 14:35','LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no WJR');
insert into #tblemail1 values('srv-two','Show Record Mail','2/14/2012 14:35','t/a JTL Pharmacy; our file no WJR');
--SET IDENTITY_INSERT #tblemail off
select * from #tblemail1
select * from #tblemail2
February 16, 2012 at 1:55 pm
Thanks for the revised script...
foscsamuels (2/16/2012)
second, i cant get this query to only show the last record as the subject lines do not show the same first five characters.
SELECT 'srv-one' as server,'Claims Mail' as account, kc.received, kc.subjectFROM #tblemail1 kc, #tblemail2 bc
WHERE left(bc.subject,5) <>left(kc.subject,5)
UNION SELECT 'srv-two' as server,'Claims Mail' as account, bc.receivedate, bc.subject
FROM #tblemail1 kc, #tblemail2 bc
WHERE left(kc.subject,5) <>left(bc.subject,5);
I think that both the highlighted columns above should be "x.receive" ??....I get syntax error otherwise.
Can you please post what your expected results from running your query should be?
kind regards
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
February 16, 2012 at 2:55 pm
you could change it to x.receive or you can use this modified query for the data load. in the real scenario the other column is receivedate
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblemail1','U') IS NOT NULL
DROP TABLE #tblemail1
IF OBJECT_ID('TempDB..#tblemail2','U') IS NOT NULL
DROP TABLE #tblemail2
--===== Create the test table with
CREATE TABLE #tblemail1
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[received] datetime,
[subject] varchar(200)
)
CREATE TABLE #tblemail2
(
--ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED, --Is an IDENTITY column on real table
server varchar(20),
account varchar(20),
[receivedate] datetime,
[subject] varchar(200)
)
--SET IDENTITY_INSERT #tblemail ON
insert into #tblemail1 values('srv-one','Claims Mail','2/14/2012 14:45',';N8> Salem Imagine & Learn Childcare et al; Salem Insured');
insert into #tblemail2 values('srv-one','Claims Mail','2/16/2012 12:37','LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no ');
insert into #tblemail1 values('srv-two','Claims Mail','2/14/2012 13:22',';N8> Salem Imagine & Learn Childcare et al; Salem Insured: Ralphaele Timpani');
insert into #tblemail2 values('srv-two','Claims Mail','2/14/2012 14:35','LN8> Claim Trabulsi/Jatala Inc. t/a JTL Pharmacy; our file no WJR');
insert into #tblemail1 values('srv-two','Show Record Mail','2/14/2012 14:35','t/a JTL Pharmacy; our file no WJR');
--SET IDENTITY_INSERT #tblemail off
select * from #tblemail1
select * from #tblemail2
we want to find records in either table where the first five characters in the subject column are not the same.
using this dataset, we should see the record with 'Show Record Mail' as the account. Hmm, with this query tho, Show record mail will show as 'claims mail' but that is fine. I would be looking for the receivedate and subject line anyway
SELECT 'srv-one' as server,'Claims Mail' as account, kc.received, kc.subject
FROM #tblemail1 kc, #tblemail2 bc
WHERE left(bc.subject,5) <>left(kc.subject,5)
UNION SELECT 'srv-two' as server,'Claims Mail' as account, bc.receivedate, bc.subject
FROM #tblemail1 kc, #tblemail2 bc
WHERE left(kc.subject,5) <>left(bc.subject,5);
February 23, 2012 at 2:39 pm
working code.
select T1.* from #tblEmail1 T1
INNER JOIN (select min(subject) as ID from (select * from #tblemail1
UNION select * from #tblemail2) X
GROUP BY LEFT(Subject,5)
HAVING COUNT(*) = 1) Y ON T1.subject = Y.ID
UNION ALL
select T2.* from #tblEmail2 T2
INNER JOIN (select min(subject) as ID from (select * from #tblemail1
UNION select * from #tblemail2) X
GROUP BY LEFT(Subject,5)
HAVING COUNT(*) = 1) Y ON T2.subject = Y.ID
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply