| Author |
Message |
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
   Topic: Joining tables
Posted: 29 Nov 2011 at 5:54am |
I have a report that is pulling from a informix system and I need to pull from two tables but they don't have a common field to join. One table does have a field that is equal to two fields combined in table two. So for example {table1_field1} = {table2_field1}+{table2_field2}. I have tried not joining the tables but I get no results. I have also tried {table1_field1}[1 to9] = {table2_field1} and using the left ,9 to pick just the first 9 out of table 1 that matches but I still get nothing. I also notice if I look in the SQL Query in Crstal it does not show the [1 to 9] or left ,9 in the statement.
Thanks in advance
KevV
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Nov 2011 at 8:27am |
if you cannot use a view or stored proc to do your join you can use a crystal command object to convert the data from table 2 into one string (field A + field B) and join on that.
You also might consider that there is a third table that you could use for joining the two together.
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 29 Nov 2011 at 10:43am |
I have never used the add command before so I am not quite sure on using it. I tried to add a command but I keep getting a rowset or syntax error. What would thew expression look like. I tried {table1.field1} + {table1.field2}. I tired {table1.field1} + {table1.field2} = {table2.field1}.
Thanks
KevV
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Nov 2011 at 10:58am |
you would write it using SQL
example:
SELECT table_1.field1, table_2.field1, table_1.field2, etc.
FROM table_1 INNER JOIN
table_2 ON table_1.field1 = table_2.field1 + table_2.field2
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 29 Nov 2011 at 11:40am |
I still get ther rowset error. Here is the formula I have in the command box
SELECT pickheader.pick_listorder, pickdetail.pick_idid, pickheader.picklistpickseq,
FROM pickheader INNER JOIN
pickdetail ON pickdetail.pick_idid = pickheader.pick_listorder + pickheader.picklistpickseq.
Here is a SQL statement we run in the informix system that works but we then take our results and export them to a txt file and import them into an excell pivot table.
from pickdetail d,pickheader h
where d.date_picked[1,8] = '20111030'
and h.pick_listorder||picklistpickseq = d.pick_idid.
I just put the date select statement into the select expert.
Thanks
KevV
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 29 Nov 2011 at 11:51am |
my version was assuming that these were strings/text.
if you have numeric or two different data types you would need to cast them into the same datatype to join.
is this the case for you?
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 29 Nov 2011 at 11:53am |
|
They are all string fields. Including the date_picked
Edited by KevV - 29 Nov 2011 at 12:02pm
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Nov 2011 at 4:04am |
Not a sql expert at all so not sure why this is not working. here it is a little cleaned up so you could try again.
SELECT h.pick_listorder, d.pick_idid, h.picklistpickseq
FROM pickheader as h INNER JOIN
pickdetail as d ON d.pick_idid = h.pick_listorder + h.picklistpickseq
WHERE (LEFT(d.date_picked, 8) = '20111030')
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 30 Nov 2011 at 5:39am |
I am not real familiar with SQL but my boss is but he knows nothing about Crystal so we are kind of working off each other. I got the error when I tried that satement but when I entered a little more of the SQL statement the error went away. The SQL I used was:
select distinct(d.to_cont) pallet
,max(d.date_picked[9,12]) time
from pickdetail d,pickheader h
where h.pick_listorder||picklistpickseq = d.pick_idid
group by 1
order by 2
and I get two fields in the command table. A pallet and a time field. Since the link is in the SQL do I need to link them in the Database Expert. I seem to be getting more records than I should.
Thanks
KevV
Edited by KevV - 30 Nov 2011 at 5:40am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 30 Nov 2011 at 7:55am |
if you can write your command to get all of the fields and records you want then no additional linking is needed. The command is just another source of data. If you add the command and the two tables into the report all 3 have to be linked.
If you can explain to your boss that a command is essentially the same as a sql view or stored procedure maybe they can write it in a fashion that gets all of the fields you need.
is that making sense?
|
|
IP Logged |
|
|
|
