May 4, 2010 at 3:55 pm
I'm trying to run a query again multiply db and this is what I have so far:
Select t1.ACTNUMBR_1, t1.ACTNUMBR_4, t1.ACTINDX,t1.CREATDDT,
t2.ACTNUMBR_1 AS [106 ACCT], t2.ACTNUMBR_4 AS [106 ACCT], t2.ACTINDX AS [106 INDEX], t2.CREATDDT AS [106 CREATE DATE]
from C1.dbo.AN00100 t1
INNER JOIN
C2.dbo.AN00100 t2
on t1.ACTINDX = t2.ACTINDX
WHERE (t1.CREATDDT > CONVERT(DATETIME, '2009-12-31 00:00:00', 102))
It is returning a 0 results. If I take out the where clause then I get a result. 1st Question what is wrong with this statement? 2nd Question is how can I get all the results from the two db within the 4 columns without have both db table colums displayed.
May 4, 2010 at 4:07 pm
The query itself looks good, assuming you're either asking for data including '2009-12-31' (e.g. '2009-12-31 09:10:00') or your t1.CREATDDT column is already normalized (= not to include any time portion)
What row do you get as a result when removing the WHERE clause?
I'd expect it's a row with a date before or equal to '2009-12-31 00:00:00' for column t1.CREATDDT...
Regarding your 2nd question:
I don't understand how you want to get all result without displaying it... If you want to hold the result set in a temp table you could use the SELECT INTO statement. Not sure if that's what you're asking for. :unsure:
May 5, 2010 at 8:55 am
kbnyny (5/4/2010)
2nd Question is how can I get all the results from the two db within the 4 columns without have both db table colums displayed.
Either you need to give column's name or use "*" (for all columns ).
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
May 5, 2010 at 3:02 pm
When removing the Where clause this is what I get. The column heading all on one row.
ACTNUMBR_1 ACTNUMBR_4ACTINDXCREATDDT
103 6140002909612/28/2006
103 999900239141/17/2002
106 ACCT 106 ACCT 106 INDEX 106 CREATE DATE
106 410120 29096 11/29/2007
106 572000 23914 4/5/2007
I would like the data to be
ACTNUMBR_1 ACTNUMBR_4ACTINDXCREATDDT
103 6140002909612/28/2006
103 999900239141/17/2002
106 410120 29096 11/29/2007
106 572000239144/5/2007
I get no data when there where clause is being used.
Thank you.
May 5, 2010 at 3:30 pm
kbnyny (5/5/2010)
When removing the Where clause this is what I get. The column heading all on one row.ACTNUMBR_1 ACTNUMBR_4ACTINDXCREATDDT
103 6140002909612/28/2006
103 999900239141/17/2002
106 ACCT 106 ACCT 106 INDEX 106 CREATE DATE
106 410120 29096 11/29/2007
106 572000 23914 4/5/2007
I would like the data to be
ACTNUMBR_1 ACTNUMBR_4ACTINDXCREATDDT
103 6140002909612/28/2006
103 999900239141/17/2002
106 410120 29096 11/29/2007
106 572000239144/5/2007
I get no data when there where clause is being used.
Thank you.
Your Where condition eliminates all those rows since the value of CREATDDT before the date you specified in your WHERE condition:
WHERE (t1.CREATDDT > CONVERT(DATETIME, '2009-12-31 00:00:00', 102))
As long as you elminate dates before December 31st 2009, you won't be able to see the rows you'd like to see (you're off by several years...)
May 5, 2010 at 3:44 pm
why is this statement not working:
select t1.ACTNUMBR_1, t1.ACTNUMBR_4, t1.ACTINDX, t1.CREATDDT
from C1.dbo.AN00100 t1 union all
select t2.ACTNUMBR_1, t2.ACTNUMBR_4, t2.ACTINDX, t2.CREATDDT
from C2.dbo.AN00100 t2 union all
select t3.ACTNUMBR_1, t3.ACTNUMBR_4, t3.ACTINDX, t3.CREATDDT
from C3.dbo.AN00100 t3
WHERE (t1.CREATDDT > '12/31/2009') or (t2.CREATDDT > '12/31/2009') or (t3.CREATDDT > '12/31/2009')
I only need to return the records within all the dbs with a date > 12/31/2009. I know something is missing just can figure it out.
thank you,
May 5, 2010 at 3:57 pm
kbnyny (5/5/2010)
why is this statement not working:select t1.ACTNUMBR_1, t1.ACTNUMBR_4, t1.ACTINDX, t1.CREATDDT
from C1.dbo.AN00100 t1 union all
select t2.ACTNUMBR_1, t2.ACTNUMBR_4, t2.ACTINDX, t2.CREATDDT
from C2.dbo.AN00100 t2 union all
select t3.ACTNUMBR_1, t3.ACTNUMBR_4, t3.ACTINDX, t3.CREATDDT
from C3.dbo.AN00100 t3
WHERE (t1.CREATDDT > '12/31/2009') or (t2.CREATDDT > '12/31/2009') or (t3.CREATDDT > '12/31/2009')
I only need to return the records within all the dbs with a date > 12/31/2009. I know something is missing just can figure it out.
thank you,
Move your WHERE condition to each of the SELECT statements (and change the way you define a date value while you're at it... otherwise you might run into an issue if someone modifies the values of @@DATEFORMAT...):
select t1.ACTNUMBR_1, t1.ACTNUMBR_4, t1.ACTINDX, t1.CREATDDT
from C1.dbo.AN00100 t1
WHERE (t1.CREATDDT > '20091231')
union all
select t2.ACTNUMBR_1, t2.ACTNUMBR_4, t2.ACTINDX, t2.CREATDDT
from C2.dbo.AN00100 t2
WHERE (t2.CREATDDT > '20091231')
union all
select t3.ACTNUMBR_1, t3.ACTNUMBR_4, t3.ACTINDX, t3.CREATDDT
from C3.dbo.AN00100 t3
WHERE (t3.CREATDDT > '20091231')
May 6, 2010 at 9:49 am
Thank you very much. It is working like a charm.
May 6, 2010 at 11:27 am
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply