question on outer join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

question on outer join

Author

Topic

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-05 : 14:47:32

could any of you help me in correcting the syntax of the below join

Regards,
aak.


select * from 
fx_v fx1 RIGHT OUTER JOIN ON (fx1.fx_close = @in_fx_close AND
fx1.fxrate_currency = CASE WHEN @v_agree_type = 'L' THEN UPPER(@v_pledge_leh_unsec_curr)
WHEN @v_agree_type = 'C' THEN UPPER(@v_pledge_cp_unsec_curr)
ELSE(CASE WHEN SIGN(@v_pre_mrgn_val) = -1 THEN UPPER(@v_pledge_leh_unsec_curr)ELSE  UPPER(@v_pledge_cp_unsec_curr)
END) END)

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-05 : 14:59:38

This is the sample piece of code in oracle which I am trying to convert it to sqlserver

select *
FROM fxrates_v fx1
WHERE fx1.fx_close(+) = in_fx_close AND
fx1.fxrate_currency (+) = decode (v_agree_type, 'L', upper(v_pledge_leh_unsec_curr) ,
'C', upper(v_pledge_cp_unsec_curr),
decode(sign(v_pre_mrgn_val), -1, upper(v_pledge_leh_unsec_curr),
upper(v_pledge_cp_unsec_curr)))
/

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-05 : 15:25:28

Here's a shot in the dark. I'd wait a bit after I post, once I figure it out Peso or Visakh come right behind me and do something sensical!

Jim


select * 
fromfx_v fx1 
WHERE fx1.fx_close = @in_fx_close 
AND 
   (  
       (  
          ( @v_agree_type = 'L' or SIGN(@v_pre_mrgn_val) = -1)  and fx1.fxrate_currency = @v_pledge_leh_unsec_curr  
        )
    or 
       ( 
          (@v_agree_type = 'C' or SIGN(@v_pre_mrgn_val) <> -1 ) and fx1.fxrate_currency = @v_pledge_cp_unsec_curr
       )
   )

Everyday I learn something that somebody else already knew

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-05 : 16:08:49

any idea of how to implement the below.. in oracle it is easy we have dual there

create table tab( a int)
insert into tab values(1)
select * from tab RIGHT OUTER JOIN select '' on ( a = 1)

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-05 : 16:13:00

Thanks Jimf
however what about the right outer join, and no worries for the decode logic..I am worried about how to join the table ( Right outer join) when there is no other table, it only in parameter

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2010-03-05 : 17:01:11

What are you trying to do with that join? It only makes sense to join to a table. Do you just want this?

select * from tab where a = 1

Jim

Everyday I learn something that somebody else already knew

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-06 : 01:45:25

quote:
Originally posted by aakcse

Thanks Jimf
however what about the right outer join, and no worries for the decode logic..I am worried about how to join the table ( Right outer join) when there is no other table, it only in parameter

what do you mean by right outer join without a table? then wats the need of join here at all?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-06 : 14:03:17

Hi Visakh,

Actually I am converting a code from oracle to sqlserver, where in I came across,the join is using (+) i,e non ansi join, with out any table, joining with in paramter value in procedure

Regards,

aakcse
Aged Yak Warrior

570 Posts

Posted - 2010-03-06 : 15:07:24

what do you mean by right outer join without a table? then wats the need of join here at all?

-- their is an join with the in parameter, I think it will select all the records for the inparameter value where there is no matching for the table.

like


select ta.col1,tb.col from tableA ta,tableB tb
where ta.col(+) = in_parameter_value
and ta.id = tb.id

was trying to convert the above to sqlserver, the (+) on left hand side means right outer join in oracle.

Regards,
aak

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-07 : 02:21:00

who told in above case you dont have a table to join with. here's the equivalent sql server statement


select ta.col1,tb.col 
from tableA ta
right outer join tableB tb
on ta.id = tb.id
and ta.col = in_parameter_value

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources