June 9, 2009 at 1:42 am
hello friedns,
not actually the formatting issue but its purly tsql issueee.
i have one table when i exec select * from tab
i get the output as below
DATASET DB OBJ
DTS ACCTABLE1
DTS ACCTABLE2
DTS PROTABLE3
DTS PROTABLE4
but my req is that i want output as below
DATASET DB1OBJ1 DB2OBJ2
DTS ACCTABLE1PROTABLE3
DTS ACCTABLE2PROTABLE4
SO IF ANY ONE GIVE ME ANY IDEA CLUE ANY CODE PLEASE LET ME KNOW AS THE EARLISET.......
Thanks in advance....
Mithun Gite
June 9, 2009 at 2:15 am
You must define a business rule to join tab with itself. That rule could be dataset = dataset and db db, but this still doesn't do the trick. With this little information this is all I can tell you.
If you find the right business rule, I can help you with the syntax, but I can't help you with the rule itself.
Regards
Gianluca
-- Gianluca Sartori
June 9, 2009 at 2:54 am
hi,Gianluca
thanks for ur reply...
see here dataset name will remain same for all.
database name can be diffrent and each database can have tables...
so this is the kind of rule ....now can you help me in writing query for it..
thx
Mithun
June 9, 2009 at 3:06 am
"each database has tables" can't be translated into a query predicate, it must be something related to your columns.
If I try this I get duplicates for each table:
DECLARE @tab TABLE (
DATASET char(3),
DB char(3),
OBJ char(6)
)
INSERT INTO @tab VALUES('DTS', 'ACC', 'TABLE1')
INSERT INTO @tab VALUES('DTS', 'ACC', 'TABLE2')
INSERT INTO @tab VALUES('DTS', 'PRO', 'TABLE3')
INSERT INTO @tab VALUES('DTS', 'PRO', 'TABLE4')
SELECT A.DATASET, A.DB AS DB1, A.OBJ AS OBJ1,
B.DB AS DB2, B.OBJ AS OBJ2
FROM @tab AS A
INNER JOIN @tab AS B
ON A.DATASET = B.DATASET
AND A.DB B.DB
This is what I get:
DATASET DB1 OBJ1 DB2 OBJ2
------- ---- ------ ---- ------
DTS ACC TABLE1 PRO TABLE3
DTS ACC TABLE1 PRO TABLE4
DTS ACC TABLE2 PRO TABLE3
DTS ACC TABLE2 PRO TABLE4
DTS PRO TABLE3 ACC TABLE1
DTS PRO TABLE3 ACC TABLE2
DTS PRO TABLE4 ACC TABLE1
DTS PRO TABLE4 ACC TABLE2
What defines that TABLE1 can't be joined with TABLE4?
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply