Select/Command issues

[doc1000101]

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?

 

[hilfy]

Please see my blog post about how to work with commands in Crystal:

https://blogs.sap.com/2015/04/01/best-practices-when-using-commands-with-crystal-reports/

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

http://www.decisionfirst.com

 

[kray4660]

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

 

[doc1000101]

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

Kray - It made perfect sense in my head ;-)
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.

 

[kray4660]

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

 

[doc1000101]

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

 

[hilfy]

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:

Select
  [i]<all of the fields that you need for your report>[/i]
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
  [i]<whatever conditions you need - DO NOT use the Select expert, filter in your command instead>[/i]

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

http://www.decisionfirst.com