Need Help

  • 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

  • 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

  • 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

  • "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