Crsytal Reports - Dataset - 2 Fields same name

[Former Member]

Hello,

I am having trouble displaying data from MySQL in Crystal Reports.

I have 3 tables:

tblproperty: ref

tblpropert_room: fk_property_ref, fk_lookup_rootyp_id & description

tbllookup_user: id & decription

tblproperty_room;description = description about each room ie lovely view from the window

tbllookup_user; description = description text for the room name ie bathroom

I can call them using an MySql statement:

SELECT a.no_name, a.address_line1, a.address_line2, a.address_line3,a.address_line4, a.town, a.county, a.postcode, a.display_price, a.general_desc, a.ref, a.viewing_text, a.directions, a.services, b.dimensions_ft, b.dimensions_m, b.description, b.fk_property_ref, b.fk_lookup_rootyp_id, c.description FROM tblproperty a, tblproperty_room_details b, tbllookup_user c WHERE a.ref = b.fk_property_ref AND c.id = b.fk_lookup_rootyp_id AND a.ref= "pra50191"

THE PROBLEM IS HERE......

This data gets passed to a dataset which is then used to design the crystal report. But in a dataset you cannot name a field twice (description; in tblproperty_room_details and tbllookup_user

I have created two datasets, displayed the records, but the problem is that the datasets will not link.

I have tried merging AND linking via the wizard (GUI) - but results in the report adds a room description to each type of room ie bathroom several times. It does not link. This is the problem.

If we could add 2 fields with the same name within a dataset it would work fine as I would state id=fk_lookup_id & ref=fk_property_ref

But I am stuck and unsure of what to do, so any help would be great???

thanks in advance

[Former Member · Accepted solution]

The problem I am having is that I cannot use the same fields name in the same dataset so would be unable to call within the same string as the names have to be the same (from the DB to the Dataset)

[Former Member · Accepted solution]

Yes, I know.... If you look closely at the statement I posted, I made 2 changes in your SQL statement, giving both description fields an alias so they have different names.

b.description as description_B   and   c.description as description_C

[Former Member]

Thanks Deb it worked - sorry about the mistake !

[Former Member]

No problem... I frequently have to copy what I am trying to read here into a document in order to figure it out.

Glad it worked for you...

Debi

[Former Member]

I am in no way a SQL expert, but try something like

SELECT a.no_name, a.address_line1, a.address_line2, a.address_line3,a.address_line4, a.town, a.county, a.postcode, a.display_price, a.general_desc, a.ref, a.viewing_text, a.directions, a.services, b.dimensions_ft, b.dimensions_m, b.description as description_B, b.fk_property_ref, b.fk_lookup_rootyp_id, c.description as description_C FROM tblproperty a, tblproperty_room_details b, tbllookup_user c WHERE a.ref = b.fk_property_ref AND c.id = b.fk_lookup_rootyp_id AND a.ref= "pra50191"

I have renamed several fields with the AS (Thanks Jason)

Debi

[former_member11696]

As Debi pointed out you need to alias common field names, CR won't let you do it either unless you fully qualify the name by adding the tables directly in the report. Even then CR may alias it for you.

Other option is to alias the field name in your DS in the app but Debi's suggestion is the best way to do it.

Thanks Debi

Don

PS - Debi check[ this out|http://www.sdn.sap.com/irj/scn/weblogs?blog=/pub/wlg/25980] [original link is broken] [original link is broken] [original link is broken];.... Look in the Crystal Reports and Xcelcius ( yellow box thingy ) Jason Kudo....

[Former Member]

Thanks Don, and congrats Jason and everyone else that made the Leaders list..