Inner Join not working

[Thech701storm]

I'm trying to design a report from activitym1 table where activity.type = 'SOMETHING' and I need some fields from the probsummarym1 table. SO I do an INNER JOIN and try it !

ERROR ODBC always I tried LEFT and RIGHT JOINs and didn't work either. I tried CrystalDesigner to automatically design the sql request and didnot work either.

THe generated request looks like this :

SELECT
activitym1.number, activitym1.type, activitym1.datestamp, activitym1.operator, activitym1.description,
probsummarym1.brief.description
FROM
{ oj activitym1 activitym1 INNER JOIN probsummarym1 probsummarym1 ON activitym1.number = probsummarym1.number }
WHERE
activitym1.type = 'S-Modification de priorité'
ORDER BY
activitym1.operator ASC

SHOULD WORK but doesnot. So no Crystal report possible.

Anybody can help ????

On top, ERROR ODBC as a message is not helping much!

THanks for any help possible...............

 

[machteld]

Hi, there is a document on supportsite about Crystal an trouble with making sc-reports (including erros and possible solutions).
I am not too good at it (only admin, do not make reports), but know a lot of people had simular problems. Second idea; try searching/asking on sc-resources.net, al lot of guys with Crystal/odbc knowledge there! Was the error only 'Odbc error' or more? if so, post complete message on site there also... If anyone knows overthere, I am sure they will help you out!

Good luck!

 

[BexConsulting]

If you remove the probsummary part of the query does the query work fine?

What is the exact ODBC error?

 

[Thech701storm]

Yes it works fine without the probsummary part....

Then again if that part isn't there..there is no JOIN !!!

I tried defining a virtual join in SC between the 2 tables
using the 'number' field as the joining field. Stopping the SC database and restarting it didn't help!

Other question, if I look at the Join from problem to contact there is a field in the problemsummary table called 'contact.name.vj' (just after the 'contact.name' field)
What is this field for ? How do we define it ?

The error is only 'ERROR ODBC', no more info available!

 

[williamwhite]

Hi,

Reply on your first question: Did you check whether both field in your environment are from the same type. Look for it in the sql mappings?

Second.

If you have a link it will look for the simalar name on the form.
So if you need one for the contact.name search to get the data to your record it is don by contact.name on the form.
But if you also want to do a search for instance for a virtual join on that persons name to the asset table the contact.name is allready taken in the link. Then you create an aliasname. Go to the dbdict double click on the field you need an extra copy of and choose create alias. For the second search in the link You will use this alias. For every extra create a new alias. For instance for a virtual join on department. The naming convention used is to put vj in the field name.

Mind you these are not really fieldnames. so you cannot put data in it, nor use to connect to the file (Just to link out) there just other names for the same object.

gr Willie

 

[robwilson1973]

Ah, the whole point to this is that it SHOULDNT work.

You are treating this like a relational database and P4 isnt relational.

You can :

1) if you are mapped out to oracle look straight into the oracle database using an oracle odbc (pro: quick and its now relational con: some mapped to blob / clobs etc. in oracle will come back as gibberish

2) create a relationship using erddef and joindef functions in SC (pro: makes the dbdicts relational con: lots of testing needed as could impact performance)

3) I think this is the one for you. As you are only doing the select on one dbdict add a subreport on to pull the data in from the other dbdict. I think activity will have to be the base report here. Just remember one report one dbdict. But you can put reports on as subreports thereby mimicing having more than one dbdict on a report

Any more questions please ask

Thanks
Rob