Jump to content
Changes to the Jaspersoft community edition download ×

SQL-using where ok but left outer join wrong


2005 IR Help

Recommended Posts

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

Link to comment
Share on other sites

  • Replies 0
  • Created
  • Last Reply

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×
×
  • Create New...