sql statement

[hal3003]

hi,
have following situation:

1st table has the informations about my order positions
2nd table has all my item informations, like base.item-unit
3rd table has the infos about how the sales unit and the standard item unit are connected to eachother, like 1 meter is 100 cm and so on.
Table 3 has an index item, base-item-unit, other unit, conversion factor. There can be 2 possible situations.
the 1st one would have a conversion factor for the specific item, and the second on would be global and would have an empty field item.

Now if in my orderposition the unit is meter and the price is 200 I have to find what the price would be for me item-table unit which would be 200/100 if my item unit would be 1 cm.

That means, that I have a select like follows:

select table3.conversion_factor
from table3
where
table3.item = table1.item and
table3.basic_unit = table2.unit and
table3.unit = table1.unit
selectdo
do whatso ever
selectempty
select table3.conversion_factor
from table3
where
table3.item = "" and
table3.basic_unit = table2.unit and
table3.unit = table1.unit

How could I do something like this in Crystal???
Any help would be very appreciated.

Many Thanks in advance
Hal

 

[Naith]

Hal,

Each select is a different report. You can't handle sub-queries in Crystal in a single SQL statement, so you replace each sub-query with a subreport.

In your case, it appears that you have 2 subreports in the main report. If this adversely affects your performance, you might want to consider putting this SQL into a stored procedure, and reporting off that instead. It's quicker.

Naith

 

[hal3003]

Naith,

thanx a lot it really sounds good, the problem is, as I`m just starting with crystal I don`t have the slightest idea how to do it, can you advice?
I thought I would have to build some kind of sql statement in a formula field or in a sql expression field.
the help file is really no help as far as its up to me.

but wouldn`t I have the same problem, 2 fields from 2 tables which should point to the record I need from the third table? how can I just use those two as variables in my subreport and give then the right field back to my primary report?

thanx
Hal

 

[Naith]

Well, both those options are available to you, Hal. You can write your SQL as normal, and use that as your report's datasource.

Or you can write the subreport(s), which is made up of your subselect, place it in the main report, and pass the values back to the main report using shared variables.

But first, before we decide on the best course of action, let me take a look at what you're trying to do. I'm aware you went to great lengths to describe your situation in your prior posts, but I'm afraid to say that your situation isn't 100% clear to me.

To coin a well known film, explain this to me like I'm a six year old. Give me an example of data as it currently stands in the database illustrating where the possibilities for null values needs to be accounted for, combined with how you expect the report to reflect the data.

Thanks,

Naith

 

[hal3003]

Naith,

thanks for trying to help, I appreciate
the situation:
1. Table (order informations): ordernumber, positionnumber, item, sales-unit, price quantity and so on
2. table (item standard informations): item, description basic-unit, standard price and so on.
3. table (convert dimensions - conversion between different units) : item, basic-unit, other-unit, conversion-factor

To find a record on the 3rd table I have to 1st. check if there is a record for item(table 1), base unit(table 2), sales unit(table 1)
If so then everything is OK.
If not I have to check if there is a record for
item ="" empty and basic-unit(table2), sales unit(table 1)

if I find somethin, I just have to get the conversion factor and use it to find my price on the basic unit

hope I explained it better, sorry for my bad english and
many thanx
Hal

 

[Naith]

Uh-huh, I get that. And this is definitely possible with subreports. I'm just trying to exhaust other possibilities before commiting you to subreports, as they tend to have a detrimental processing hit on your report.

Is it not possible to simply use a equi/equal join between Table 1 and 2, and link either of these to Table 3 with a left outer join? This would mean that you aren't doing 2 passes testing for record availability in that table all the time.

If that's a possibility, then that's the preferred way forward. If not, then you would set up your main report to handle the SQL for there being a matching record in all tables. Your subreport you set up to handle the other SQL query. Place the subreport in your main report, and use shared variables to pass the values from your subreport to your main report.

Your helpfiles are exhaustive, but can be a little daunting if you don't know where to start. I would check out 'shared variables' in F1 and the subreport expert.

If you get any problems with the subreport, don't hesitate to pop back and go into it. But, like I say, if you can accomplish this simply by addressing the joins between your tables, then that gets the thumbs up.

Good luck,

Naith

 

[hal3003]

Naith,

tried the subreports out. now I have 2 subreports one for searching with item value being filled from table 1 and the second subrecord with an empty item value, in case there is no special conversion for the item. but I really need the second subreport only if I don´t have found a record through the first subreport. But anyway, my problem now is:

1. I need to have the value in a shared variable, so I can use it for all the other calculations, at the moment my shared variable is allways 0

2. I still believe that the best solution would be a formula field which uses a sql select statement as we talked about before. That would be the fastest and more logical step. I cann´t imagine that crystal can´t handle it?

Thanx and happy thanksgiving
George

 

[Naith]

Querying directly against the database, option 2 isn't an option at all. The only way you can achieve this is to write your sql, including your subquery beforehand, and have Crystal query the SQL instead of the database. You can also adopt that approach with a stored procedure. Both those techniques would be quicker than the subreports.

With regard to the subreports you've already set up, when you say your shared variable is returning 0, you need to confirm

[ul][li]Where your shared variable is first getting it's value assigned.[/li]
[li]Where are you then asking for the shared variable value to be displayed? It has to be after the shared variable first gets it's value assigned.[/li]
[li]Ensure that you are using WhilePrintingRecords at the beginning of each formula using the shared variable.[/li][/ul]

Good luck, and I'm British, by the way. Thanksgiving is the anniversary of when you kicked us out of the country.

Naith

 

[hal3003]

Naith,

thanks for the infos,
my subreports are functioning now but I allways have to print them, if I supress, then I get the 0 in my shared variable. So this is not the solution I need so badly

I am sure your suggestion writing my sql statement is the best solution, as I havent done before will have to try and error a lot. the only thing is Im geting under time pressure so I probably just write a baan program and make a report of my own, that will take me much less.

By the way you should try stuffed turky and muffins on thanksgiving, delicious, specialy without kicking anybody. (is suppsoed to be funny)

regards
Hal

 

[Naith]

You're absolutely correct about the subreports. They do need to be open (i.e. not suppressed or hidden) in order to process. The trick is to size the suppress all the sections in the subreport, and size the subreport itself as small as possible in the main report, so that it appears invisible when the report is executed.

Naith