SQL-using where ok but left outer join wrong

[2005 IR Help]

By: Primo Chen - primochen

SQL-using where ok but left outer join wrong

2004-08-15 22:38

if I use "where" , $P{} as paramenter is ok

if I use "left outer join" , $P{} as paramenter,

I got null

 

I replace $P!{} as $P{} and the value add " ' "

then it works again

 

DataBase is oracle

 

Anyone know what happened?

 

How to solve it if I want to use $P{} and "left outer join" (value don't need to add " ' ")

 

thanks for your answer..........

 

-------------------------------------

SELECT

DB.TBSBAL01.DISPLISTNO AS DISPLISTNO,

DB.TBSBAL01.TRANSWAY AS TRANSWAY,

DB.TBSTAAA.TRANSCOMPID AS TRANSCOMPID,

DB.TBGP10.ID AS ID,

DB.TBGP10.CHNNAME AS CHNNAME,

DB.TBGP10.BILLADDR AS BILLADDR,

DB.TBSBAL06.RECNAME AS RECNAME,

DB.TBSBAL06.RECADDR AS RECADDR,

DB.TBSBAL01.NOTE AS NOTE

FROM

(DB.TBSBAL01 left outer JOIN DB.TBSBAL10 on DB.TBSBAL01.DISPLISTNO = DB.TBSBAL10.DISPLISTNO) left outer JOIN DB.TBSTAAA on ( DB.TBSBAL10.COMPID = DB.TBSTAAA.COMPID AND

DB.TBSBAL10.CARRIERNO = DB.TBSTAAA.TRANSCOMPID),

DB.TBSBAL06,

DB.TBGP10

WHERE

(

(

DB.TBSBAL06.COMPID = DB.TBSBAL01.COMPID AND

DB.TBSBAL01.CUSTNO = DB.TBSBAL06.CUSTNO AND

DB.TBSBAL01.RECORDERNO = DB.TBSBAL06.RECORDERNO AND

DB.TBGP10.COMPID = DB.TBSBAL01.COMPID AND

DB.TBGP10.ID = DB.TBSBAL01.CUSTNO

)

AND

(( DB.TBSBAL01.COMPID = $P!{COMPID} )

AND ( DB.TBSBAL01.DISPLISTNO = $P!{DISPLISTNO} ))

)

ORDER BY

DISPLISTNO

 

--------------------------------------------

 

SELECT

DB.TBSBAL01.DISPLISTNO AS DISPLISTNO,

DB.TBSBAL01.TRANSWAY AS TRANSWAY,

DB.TBSTAAA.TRANSCOMPID AS TRANSCOMPID,

DB.TBGP10.ID AS ID,

DB.TBGP10.CHNNAME AS CHNNAME,

DB.TBGP10.BILLADDR AS BILLADDR,

DB.TBSBAL06.RECNAME AS RECNAME,

DB.TBSBAL06.RECADDR AS RECADDR,

DB.TBSBAL01.NOTE AS NOTE

FROM

DB.TBSBAL01,

DB.TBSBAL10,

DB.TBSBAL06,

DB.TBSTAAA,

DB.TBGP10

WHERE

(

(

DB.TBSBAL06.COMPID = DB.TBSBAL01.COMPID AND

DB.TBSBAL01.CUSTNO = DB.TBSBAL06.CUSTNO AND

DB.TBSBAL01.RECORDERNO = DB.TBSBAL06.RECORDERNO AND

DB.TBSBAL01.COMPID = DB.TBSBAL10.COMPID AND

DB.TBSBAL10.COMPID = DB.TBSTAAA.COMPID AND

DB.TBSBAL10.CARRIERNO = DB.TBSTAAA.TRANSCOMPID AND

DB.TBGP10.COMPID = DB.TBSBAL01.COMPID AND

DB.TBGP10.ID = DB.TBSBAL01.CUSTNO

)

AND

(( DB.TBSBAL01.COMPID = $P{COMPID} )

AND ( DB.TBSBAL01.DISPLISTNO like $P{DISPLISTNO} ))

)

ORDER BY

DISPLISTNO