rx 5.7 problem with table joins

Rhythmyx Technical Discussion
1

hi all

when i try to preview a piece of content i get “the PO_CT_FREETEXT.REVISIONID backend column could not be found in the left side result set. The sorted result set join cannot be performed”.

I have a content type freetext (table PO_CT_FREETEXT) which originally in the assembler had 3 tables - PO_CT_FREETEXT, RXS_CT_SHARED and CONTENTSTATUS.

I needed to add some more fields to the CE which come from a shared table (PO_SHARED_METADATA) - so i dragged this table into the assembler on the workbench. I now get the above error. The table area in the workbench does not really give me any clues as to what is wrong - i can’t see the sql that the joins on these tables is producing so don’t really know why the joins are wrong and why the content won’t preview.

anybody have any clues as to where i can get more info on this error to enable me to include my new shared fields.

thanks
michelle

2

Hi Michelle

If you turn on debugging one of the options is the SQL statement Rhythmyx produces.

You should be able to see in the .trace file where the SQL is going wrong

You could also re-apply the joins in the table view.

Cheers
James

3

Thanks james

i traced everything as wasn’t sure what i needed. the .trace file returned 3 sql statements all of which run directly in the database.

The first selects the fields from PO_CT_FREETEXT and CONTENTSTATUS.
The second and third are identical and select the shared field from RXS_CT_SHARED.

There does not seem to be any sql submitted for the new fields i added to the mapper which come from the new table i added PO_SHARED_METADATA.

regards
michelle

4

Hi michelle

In the table view I would delete all but the contentstatus table and manually add all the tables again creating the joins where necessary.

Cheers
James

5

Thanks James

i removed all the tables except contentstatus and dragged the other three back on.

error now is contentstatus.contentid back end column could not be found in the left side result set. the sorted result set join cannot be performed.

when you say add the joins as necessary - its not clear to me what joins are necessary. does rx not add the necessary joins when you drag the relevant tables onto the data tank.

thanks
michelle

6

Hi Michelle

Rhythmyx will try and create the joins but essentially you need to join the following tables

CONTENTSTATUS.CONTENTID with YOURCONTENTTYPE.CONTENTID

RXS_CT_SHARED.CONTENTID with YOURCONTENTTYPE.CONTENTID
RXS_CT_SHARED.REVISIONID with YOURCONTENTTYPE.REVISIONID

If needed:

RXS_CT_SHARED.CONTENTID with ANYOTHERASSOCIATEDCONTENTTABLE.CONTENTID
RXS_CT_SHARED.REVISIONID with ANYOTHERASSOCIATEDCONTENTTABLE.REVISIONID

Then in your selector have the following values:

YOURCONTENTTYPE.CONTENTID = PSXSingleHTMLParam/sys_contentid
YOURCONTENTTYPE.REVISIONID = PSXSingleHTMLParam/sys_revision

Hope this helps

Cheers
James