Report Design: Joining tables

Print Page | Close Window

Joining tables

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=15043
Printed Date: 29 Apr 2024 at 9:40am

Topic: Joining tables

Posted By: KevV
Subject: Joining tables
Date 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

Replies:

Posted By: DBlank
Date 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.

Posted By: KevV
Date 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

Posted By: DBlank
Date 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

Posted By: KevV
Date 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

Posted By: DBlank
Date 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?

Posted By: KevV
Date Posted: 29 Nov 2011 at 11:53am

They are all string fields. Including the date_picked

Posted By: DBlank
Date 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')

Posted By: KevV
Date 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

Posted By: DBlank
Date 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?

Posted By: KevV
Date Posted: 01 Dec 2011 at 3:53am

I think I got it. The only thing I am not sure is I have a formula that uses a field in one of the tables that uses a parameter for a date written as:

{?Date} ={pickdetail.date_picked}[1 to 8]

Is there a way to put this in the SQL or how would I make the date_picked a field in the command table.

Thanks

KevV

Posted By: DBlank
Date Posted: 01 Dec 2011 at 4:25am

the command itself can have a parameter.

You create it on the right hand side of the command screen

name it date

set the prompt text

value type=string

leave default value as null

update or add in your where clause to the command and include the param.

make sure to put single quotes around it.

something like

where left({d.date_picked},8) = '{?date}'

does that help?

Posted By: KevV
Date Posted: 01 Dec 2011 at 4:54am

That was a huge help. I did have to make a change in the statement though. With the one above I got the rowset error so I changed it to :

where d.date_picked[1,8] = '{?date}'

and it worked perfectly. Thanks for all the help. One more thing I am hoping you can help me with is I am doing some counts and I have a fomula where if a number is between two numbers it is rounded like:

if mailto:%7b@Minutes - {@Minutes } in "0" to "15" then "15" else
if mailto:%7b@Minutes - {@Minutes } in "16" to "30" then "30"

I then need to count all of the 15's and 30"s and show the total of each in the header or footer. I can do a Running total and count for each and it counts in the detail section but if i put it in the footer or header it shows 0.

Thanks KevV

Posted By: DBlank
Date Posted: 01 Dec 2011 at 5:01am

Running totals (or variable formual counts) only show the amount that it has processed up to the location that you place it in the report (like whileprintingrecords). Insert Sumaary function (or its equivalent in a formula) are created in an early enough pass to display the result in a header.

If you do not have duplicate data rows you can create 2 formulas to do your display.

//15min_count

if mailto:%7b@Minutes - {@Minutes } in "0" to "15" then 1

//30min_count
if mailto:%7b@Minutes - {@Minutes } in "16" to "30" then 1

now you can dop a SUM on each of these at any level of the report

Posted By: KevV
Date Posted: 01 Dec 2011 at 5:20am

Awesome Thanks again for the help

KevV

Print Page | Close Window