Join columns in my select

[jaburke]

Hi,
I'm creating a Crystal report and the join columns are showing up in my select. I don't want them there. I am not pulling them into my details, or anywhere else on the report. How can I get them out of my select without using a command for the SQL? I don't remember old versions of CR doing this. I'm using CR2008. Thanks!!

 

[Turkbear]

Hi,
If they are only in the FROM part of the select then
how else can CR 'tell' the database what to join on ?



To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jaburke]

The joins are in the where condition. When I write the query using SQL, I don't have to specify the join columns in the select.

 

[Turkbear]

Hi,
Can you post the 2 selects? I have never seen a join select statement without a "Where table1.field = Table2.field" type syntax.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[jaburke]

The join info is in the where....for example


My query:
select p.part_number, o.order_type
from parts_tb p
, orders_tb o
where o.item_num = p.item_num

Query generated by CR:
select o.item_num, p.item_num, p.part_number, o.order_type
from parts_tb p
, orders_tb o
where o.item_num = p.item_num

I don't want the CR query to have item_num in the select.

 

[lbass]

Why does it matter if they are there? I do sometimes remove them in commands so they only appear in the 'From' clause, but I'm not sure it makes a performance difference, since the join requires the field to be used in creating the dataset. I do think you should be using joins instead of the Where clause though--my guess is that this does improve efficiency.

-LB

 

[jaburke]

I don't want them in the select because I am getting 70 records - the same one duplicated 70 times because one of the join columns contains 70 different values. I really only want one record without showing that column.

Example:
Query from CR shows the same record with 70 different item_nums. If I remove item_num from the select, I get one distinct record. It's not needed for the report, just in the join.

I prefer to use the functionality of CR, but if I can't get them out of the select, I'll have to use a command. Thanks!!!

 

[lbass]

And does this happen if you check database->"select distinct records"?

-LB

 

[jaburke]

Yes..it only behaves correctly when I remove those columns from the select statement (via sql).

 

[lbass]

i just tested this and I think the issue is because you are not joining the tables in the from clause. Instead of adding the links in your selection formula, add them by using the database expert to link the tables.

-LB

 

[jaburke]

I created the links through the database expert, not the selection formula.

 

[lbass]

I haven't seen that kind of query resulting from database links. Must be due to your particular datasource.

In the linking expert, did you enforce the joins or not? You might want to see whether that changes what is returned in the select statement.

-LB

 

[jaburke]

I didn't enforce the joins, but I just went and tried it, and it is giving me the same results. It is driving me crazy. My datasource is an Oracle 10g database. Nothing out of the ordinary.

Okay I just realized that I have a statement in the select expert that says where item_num < 900. If I remove that, it removes the item_num from the select. When I put it back, it comes back. I can write SQL without it in the select and it works. I only want to filter on it, I don't want to return the column in the results!!!

Thanks for taking so much time to look at this. I may just have to resort to a command.

 

[Turkbear]

Hi,
Are you using the native 'Oracle Server' connection method or an ODBC/ADO type? Does your Oracle client version match the database version?





To Paraphrase:"The Help you get is proportional to the Help you give.."