Post reply

SQL code

  • October 14, 2011 at 9:02 am

    #249414

    Hi everyone,

    I am actually working on oracle and mssql. I need to decode the oracle script to mssql. But i am struck at one point. one of my oracle code has

    select table1.column4,table2.column5 from table1,table2

    where table1.column1=table2.columns1(+)

    and table2.column3(+)=02.

    i tried by scripting as table2.column=02 but there is variation in result set. I am able to write the script when it is between two columns but i am not able to resolve if it is constant just like table2.column3(+)=02.

    Can anyone please suggest the way to write this code in mssql.

    Thanks

  • October 14, 2011 at 9:04 am

    #1394691

    98% of the people here have never used Oracle. I personally have no idea what this syntaxe does.

    Can you show us a little sample data and the required output?

    We'll get you on your way.

  • October 14, 2011 at 9:07 am

    #1394695

    the (+) syntax was used to show how to do a LEFT OUTER JOIN.

    both Oracle 10+ and SQL can use the newer more understandable syntax like this, so you can use the same code in both palces in the future

    SELECT table1.column4,

    table2.column5

    FROM table1

    LEFT OUTER JOIN table2

    ON table1.column1 = table2.columns1

    AND table2.column3 = 02.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2011 at 9:09 am

    #1394698

    And here's one of the top 2% just hanging out here :-D.

  • October 14, 2011 at 9:45 am

    #1394727

    Thanks for the reply,

    But actually i tried this way also and its not working.

    The thing is table2.column3=02 is the condition applied after performing join where as table2.column3(+)=02 is the condition being performed while joining itself which means the join is performed based on this condition also. So there comes the variation. if table2.column3(+)=02 gives 6000 records then table2.column3=02 gives 0. Its a huge variation.

    Any mor suggestions?

  • October 14, 2011 at 9:47 am

    #1394729

    What code did you try?

    If you do left join on AND = 2 it should do exactly what you need.

  • October 14, 2011 at 9:50 am

    #1394733

    it might be that the second column in the join (being set to a constant) is not correct;

    maybe this?

    SELECT table1.column4,

    table2.column5

    FROM table1

    LEFT OUTER JOIN table2

    ON table1.column1 = table2.columns1

    WHERE table2.column3 = 2

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 14, 2011 at 9:54 am

    #1394736

    Lowell (10/14/2011)

    it might be that the second column in the join (being set to a constant) is not correct;

    maybe this?

    SELECT table1.column4,

    table2.column5

    FROM table1

    LEFT OUTER JOIN table2

    ON table1.column1 = table2.columns1

    WHERE table2.column3 = 2

    AFAIK this turns a left join into an inner join (since all nulls will not return true in that check)

  • October 14, 2011 at 10:03 am

    #1394741

    ok.

    thanks for your replies and will try again

  • October 14, 2011 at 10:32 am

    #1394769

    Awesome thats perfect. Thankyou very much foryour help.

    SELECT table1.column4,

    table2.column5

    FROM table1

    LEFT OUTER JOIN table2

    ON table1.column1 = table2.columns1

    and table2.column3=2 works perfect

  • October 14, 2011 at 10:39 am

    #1394772

    HTH & thanks for the feedback!

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply