Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations bkrike on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select/Command issues

Status
Not open for further replies.
doc1000101

doc1000101

IS-IT--Management
Dec 14, 2016
9
US
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?
 
Sort by date Sort by votes
Upvote 0 Downvote
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
 
Upvote 0 Downvote
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.
 
Upvote 0 Downvote
Pervasive. I believe the same commands/structure as MS SQL
 
Upvote 0 Downvote
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
  [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
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

K
  • Locked
  • Question
We're using command in crystal report
Replies
1
Views
577
hilfy
hilfy
JustMe24
  • Locked
  • Question
How to select from 2 rows in a table
Replies
1
Views
490
SoftwareRT
SoftwareRT
VHDN
  • Locked
  • Question
Using Special File in Command
Replies
5
Views
524
hilfy
hilfy
Bloke152
  • Locked
  • Question
More excel export issues!
Replies
2
Views
282
kray4660
kray4660
mohebkasem
  • Locked
  • Question
Using Case in a crystal reports command parameter.
Replies
1
Views
452
kray4660
kray4660

Part and Inventory Search

Sponsor

Back
Top