Hi there,

Hopefully someone can help me out with my typed list based on the following snippet of SQL

WHERE  "AMSOBJ"."PM_PER_DIEM_DAY"."PM_PER_DIEM_DAY_ID"(+)=
"AMSOBJ"."PM_PER_DIEM_HEARING"."PM_PER_DIEM_DAY_ID" 
       AND ( "PM_PER_DIEM_DAY"."PANEL_MEMBER_CLAIM_ID" = :PANEL_MEMBER_CLAIM_ID1)

The above is the code that is generated. What I want/need is for the "(+)" to be with the "AND" and not with the equal sign. This is not returning all the entries I need. I have set my typed list to ObeyWeakRelations = TRUE but it isn't working.

Any idea what I'm doing wrong?

Thank you very much,

Carlo.

carlor wrote:

Hi there,

Hopefully someone can help me out with my typed list based on the following snippet of SQL

WHERE  "AMSOBJ"."PM_PER_DIEM_DAY"."PM_PER_DIEM_DAY_ID"(+)=
"AMSOBJ"."PM_PER_DIEM_HEARING"."PM_PER_DIEM_DAY_ID" 
       AND ( "PM_PER_DIEM_DAY"."PANEL_MEMBER_CLAIM_ID" = :PANEL_MEMBER_CLAIM_ID1)

The above is the code that is generated. What I want/need is for the "(+)" to be with the "AND" and not with the equal sign. This is not returning all the entries I need. I have set my typed list to ObeyWeakRelations = TRUE but it isn't working.

You mean with "AMSOBJ"."PM_PER_DIEM_HEARING"."PM_PER_DIEM_DAY_ID" ? As the AND predicate is a filter, not a part of the join clause.

The type of the PM_PER_DIEM_DAY - PM_PER_DIEM_HEARING relation, is that 1:n ?

Sorry. I guess I could have been more clear.

The generated code is as above. The code I need is

WHERE "AMSOBJ"."PM_PER_DIEM_DAY"."PM_PER_DIEM_DAY_ID"=
"AMSOBJ"."PM_PER_DIEM_HEARING"."PM_PER_DIEM_DAY_ID" 
     (+)AND ( "PM_PER_DIEM_DAY"."PANEL_MEMBER_CLAIM_ID" = :PANEL_MEMBER_CLAIM_ID1)

Notice that the "(+)" is now beside the AND. When I execute this code in Toad, I get what I need. All the entries in PM_PER_DIEM_DAY with the claimID even if there isn't a corresponding entry in PM_PER_DIEM_HEARING.

As to your second question, LLBLGen shows:

Selected used relation PM_PER_DIEM_HEARING - PM_PER_DIEM_DAY (m:1)

I hope this makes it clearer. I must appologize, though, as I'm not a database guy.

Thanks,

Carlo.

RTFM!!

Well, I found a solution... just like the manual says. I changed the order in which I added the entities and now it works.

Sorry for wasting your time,

Carlo.

carlor wrote:

Sorry. I guess I could have been more clear.

The generated code is as above. The code I need is

WHERE "AMSOBJ"."PM_PER_DIEM_DAY"."PM_PER_DIEM_DAY_ID"=
"AMSOBJ"."PM_PER_DIEM_HEARING"."PM_PER_DIEM_DAY_ID" 
     (+)AND ( "PM_PER_DIEM_DAY"."PANEL_MEMBER_CLAIM_ID" = :PANEL_MEMBER_CLAIM_ID1)

Notice that the "(+)" is now beside the AND. When I execute this code in Toad, I get what I need. All the entries in PM_PER_DIEM_DAY with the claimID even if there isn't a corresponding entry in PM_PER_DIEM_HEARING.

As to your second question, LLBLGen shows:

Selected used relation PM_PER_DIEM_HEARING - PM_PER_DIEM_DAY (m:1)

I hope this makes it clearer. I must appologize, though, as I'm not a database guy.

A LEFT JOIN B, will result in FROM A, B WHERE A.foo = B.foo(+)

So your assumption is correct and the generated code is wrong. I'd like to know which runtime library version you're using. The routine generating the SQL has been reworked in september 2004, and later versions might fix your problem (as there was an issue with weakrelations and right/left joins. You effectively need a right join, which older code couldn't do)

You can retrieve the version of the runtime lib as: string version = RuntimeLibraryVersion.Version; string build = RuntimeLibraryVersion.Build;

Version should be 1.0.2004.1 and build something later than 09242004

As a workaround, you can re-add the entities to the typed list, but in a different order: first PM_PER_DIEM_DAY and then PM_PER_DIEM_HEARING. This will give you an 1:n relation being used which should work ok. (but of course the code should also handle your setup)

carlor wrote:

RTFM!!

Well, I found a solution... just like the manual says. I changed the order in which I added the entities and now it works.

Sorry for wasting your time, Carlo.

No you haven't wasted my time, as it should work no matter what . So please check for me the version of your runtime library and if that's 1.0.2004.1 and a build later than 09242004 the code contains a bug and it should be fixed

Hi there,

I am running Version 1.0.2004.1 and the dates on the libraries are 09-24-2004. I am looking into getting the latest version but in the meantime, I have changed the order of the entities which works.

Thanks,

Carlo.

carlor wrote:

Hi there,

I am running Version 1.0.2004.1 and the dates on the libraries are 09-24-2004. I am looking into getting the latest version but in the meantime, I have changed the order of the entities which works.

Ok, thanks. I'll check if this is indeed a bug in the latest code.

The relation is weak (otherwise no suffix would have been added), and as it is an m:1 relation, it will add the (+) to the PK side fields. PM_PER_DIEM_DAY is the PK side, so the (+) is added to the pk field, to get the RIGHT JOIN: PM_PER_DIEM_DAY RIGHT JOIN PM_PER_DIEM_HEARING. ->

WHERE "AMSOBJ"."PM_PER_DIEM_DAY"."PM_PER_DIEM_DAY_ID"(+)= "AMSOBJ"."PM_PER_DIEM_HEARING"."PM_PER_DIEM_DAY_ID"

which is the query you saw.

As the FK side can be nullable, joining towards the FK (PK RIGHT JOIN FK) give more rows (all PM_PER_DIEM_HEARING rows) and eventually null's for PM_PER_DIEM_DAY where PM_PER_DIEM_HEARING has a null FK.

So by changing the relation into an 1:n, you take the PK side as starting point and want to join towards that.

As this is too limited, JoinHints were added but typed lists don't support these yet (the upgrade currently in development will let you specify these joinhints), so you can just state what the join type shold be and it should work.

So no bug, but undesired results of the functionality (A feature! ).