×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Inner Join not working

Inner Join not working

Inner Join not working

(OP)
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...............

RE: Inner Join not working

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!

RE: Inner Join not working

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

What is the exact ODBC error?

RE: Inner Join not working

(OP)
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!

RE: Inner Join not working

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

RE: Inner Join not working

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close