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?
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?
Talk To Other Members
RE: Select/Command issues
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.e,
from atable RIGHT JOIN sometable on RIGHT('0000'+ISNULL(atable.FIELDNAME,''),4) = sometable.fieldname
RE: Select/Command issues
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.
RE: Select/Command issues
RE: Select/Command issues
RE: Select/Command issues
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