×
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

Select/Command issues

Select/Command issues

Select/Command issues

(OP)
Ive got 3 tables I need in this report and one of the fields I need to link in 2 of the tables are different

table1 = 001
table2 = 0010

I have tried creating a command that uses RIGHT('0000'+ISNULL(FIELDNAME,''),4) to add a leading 0 to table1. But when I try to link only inner and left joins are available - which pulls no data. I believe I need a right outer here for this to work. When I browse the data it is pulling in as 4 digits, so that part seems to work okay

Any ideas on what to try?

RE: Select/Command issues

Please see my blog post about how to work with commands in Crystal: https://blogs.sap.com/2015/04/01/best-practices-wh...

You should use a SINGLE command to pull all of the data for your report instead of linking a command with tables.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

RE: Select/Command issues

I am a little confused. You say that you made a command that uses RIGHT('0000'+ISNULL(FIELDNAME,''),4), but then you say only inner and left joins are available. If you are using a command, I do not understand how you cannot use your own joins

i.e,
from atable RIGHT JOIN sometable on RIGHT('0000'+ISNULL(atable.FIELDNAME,''),4) = sometable.fieldname

RE: Select/Command issues

(OP)
hilfy - Thanks, but I didnt see any helpful information there.

Kray - It made perfect sense in my head winky smile
Yes you are correct, I was talking about the links tab, not manually creating the join in the command. I've tried in the command and had no luck at this point. Here is an example of what I have tried:
select PO,RIGHT('0000'+ISNULL(POLINE,''),4),NOTES from GAB_4163_PO_NOTES right outer join V_PO_LINES on PO_NOTES.POLINE = v_po_lines.record_no

Basiclly I need to select * from V_job_header, select * from V_PO_Lines and select * from PO_Notes and join on po_notes.poline = v_po_lines.record_no while adding a leading 0 to the po_notes.poline

I'm pretty sure Im just not figuring out the proper way to format the sql statement.

RE: Select/Command issues

What SQL are you using (i.e., MS-SQL, Oracle, etc.)?

RE: Select/Command issues

(OP)
Pervasive. I believe the same commands/structure as MS SQL

RE: Select/Command issues

I see a couple of things:

1. Your example has one trailing 0 but you're adding leading 0's to the number. I suggest changing the formula to this:

LEFT(IsNull(POLINE, '') + '0000', 4)

In order to get the join to work properly in the command, you'll need to join on the formula instead of the field. So, your command might look something like this:

CODE

Select
  <all of the fields that you need for your report>
from V_job_header as hdr
  left join V_PO_Lines as lines
    on hdr.PO = lines.PO
  left join PO_Notes notes
    on lines.record_no = LEFT(ISNULL(notes.POLINE, '') + '0000', 4)
where
  <whatever conditions you need - DO NOT use the Select expert, filter in your command instead> 

This single command should pull all of the data you need for the report - DO NOT join tables and commands together in the Database Expert.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
www.decisionfirst.com

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