Comparing columns contents between 2 table...

any idea on the syntax for such a querie? all my attempts to compare an item # column in 2 seperate tables keep coming up an incorrect syntax...(I want to compare one column in one table with a column in another table).
Thanks in advance:rolleyes:I'm not sure on what you are asking?

SELECT a.*
FROM TBL a, TBL b
WHERE a.col = b.col

or

SELECT a.*
FROM TBL a
WHERE NOT EXISTS
(
SELECT *
FROM TBL b
WHERE a.col = b.col
)

??|||sorry I did not respond to your reply:

Thanks for the info - also the first one works for me...

SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname

simple really :)

Again thanks... Now if I can only get it to ignore NULL and empty cells...|||SELECT *
FROM tableA, tableB
WHERE tableA.a_fieldname = tableB.b_fieldname
AND tableB.b_fieldname is not null
and LEN (tableB.b_fieldname) <> 0

--you can also put in tableA.a_fieldname as well if you so desire
-- tell me if that sorts out your problem|||thanks works fine :)

got to get it onto my production server and run now... Thanks,

Compare Dates and fail job if no match

This is probably a problem with a pretty simple solution but i can't find the right control/data flow item to handle it

Scenario.

I determine the database date for my source data for a set of ETL jobs via a piece of SQL - this gets passed to a master package variable which is subsequently to be used as the "Load date" of the resulting child package ETL routines. However I only want the packages to run if the LoadDate has either not been run before or is the next one in the DW sequence.

To check for this, In my data warehouse I also have a table called Import_Registry where the date of each upload is stored at the end of the daily ETL routines. So I can obtain the potential NextUpload date via this bit of SQL script.

SELECT DATEADD(day, 1, MAX(Upload_Date)) AS NextUpload FROM Import_Registry

Problem. I need to compare these two dates (the source db date & the DW next upload date) to see if they match. If they do match, then I run all the ETL packages using the date. If they do not match, say for example if the source database date is less than the "NextUpload" date I want to exit the routines "gracefully" and log the failure.

How do I get this working - can't seem to get my head around how I can compare the 2 dates ?

Derived column transformation has DateDiff function.

or

Script Component, you can write your own function in vb.net

|||Use the precedence constraints in the control flow. Use Execute SQL tasks to get your dates, and then hook them up to a sequence container which houses your data flow(s). The precedence constraint can perform your date comparison.|||

Hey Phil,

Thanks for that... you just confirmed what I had gradually worked out for myself!!

My solution was.....

I created 2 Execute SQL tasks. One to get the source database date, the other to get the "next date" due for loading into the DW and joined them to the first execute package task. But, between the final Execut SQL task & the package task I created a precedence "expression" constraint as follows

@.[User::MasterPackage_vLoaddate]== @.[User::NextLoadDate]

which were the 2 variables assigned from the Execute SQL tasks. On success, the remaining packages run but on failure the job fails.

It works great, so thanks for confirming that....

|||I appreciate that you tried this on your own and got it to work. I also appreciate that you posted your solution for others to use.

Glad it worked out for you!

Thanks,
Phil

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?
Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>
|||Yes, it would be much better to do as you describe.
David Portas
SQL Server MVP
|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>|||Yes, it would be much better to do as you describe.
David Portas
SQL Server MVP
--|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?

Comma delisted items

I have an application that stores user profiles. One of the profile fields
allows for comma-separated items. Each item, when presented in a browser,
will allow for the user to click on and search the database for other users
with the same item in their profile.
Now, would it be better if instead of storing the items in one field as
"AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
UserID Item
1111 AAAA
1112 CCCC
1113 BBBB
1114 AAAA
1115 DDDD
What are your thoughts?Hi
See Anith's example
SELECT IDENTITY(INT) "n" INTO Numbers
FROM sysobjects s1
CROSS JOIN sysobjects s2
GO
DECLARE @.Ids VARCHAR(200)
SET @.Ids = '5,33,229,1,22'
SELECT SUBSTRING(@.Ids, n, CHARINDEX(',', @.Ids + ',', n) - n)
from numbers where substring(','+@.Ids,n,1)=','
AND n < LEN(@.Ids) + 1
GO
drop table Numbers
"Shabam" <blislecp@.hotmail.com> wrote in message
news:TO6dnaZLK6YNJ_bcRVn-iA@.adelphia.com...
> I have an application that stores user profiles. One of the profile
fields
> allows for comma-separated items. Each item, when presented in a browser,
> will allow for the user to click on and search the database for other
users
> with the same item in their profile.
> Now, would it be better if instead of storing the items in one field as
> "AAAA,BBBB,CCCC,DDDD", I store them in a separate table like this?
> UserID Item
> 1111 AAAA
> 1112 CCCC
> 1113 BBBB
> 1114 AAAA
> 1115 DDDD
> What are your thoughts?
>
>
>|||Yes, it would be much better to do as you describe.
--
David Portas
SQL Server MVP
--|||> Yes, it would be much better to do as you describe.
I was told that searches would be much slower as a result, since it would
require a join between 2 tables. Basically right now, there's a USER table
and all of a user's profile data is stored there. A few of the fields are
comma-delisted ones. The programmer is saying that by moving them to
separate tables, that it would slow down searches due to having to use join
statements.
What are your thoughts on this?