SQL Query and Linking Expert

[kchaudhry]

I have five main tables.

1. Participant_Move_in_Trans
2. Participant
3. Archive_Participant_Move_in_Trans
4. Measure
5. Formula

I have linked the tables using the primary and foreign keys. When I run the report I have a field called {Participant_Move_in_Trans.Trans_Category} which contains data LOCAL, INBOUND & OUTBOUND, this field only shows LOCAL for all the records.

I went into SQL show query and found the following:

SELECT DISTINCT
participant."name_last", participant."name_first", participant."id",
Participant_Move_in_Trans."Project", Participant_Move_in_Trans."Zone", Participant_Move_in_Trans."Sub_Project_Code", Participant_Move_in_Trans."Monthly_Rate", Participant_Move_in_Trans."Building", Participant_Move_in_Trans."Apartment", Participant_Move_in_Trans."Orig_Move_in_Date", Participant_Move_in_Trans."Reservation_Date", Participant_Move_in_Trans."Commission_Qualifying_Date", Participant_Move_in_Trans."Length_of_Stay_Days", Participant_Move_in_Trans."Tenant_Last_Name", Participant_Move_in_Trans."Corp_Name", Participant_Move_in_Trans."Term", Participant_Move_in_Trans."Lease_Target_Rate", Participant_Move_in_Trans."Trans_Category", Participant_Move_in_Trans."Tier_Elig_Count", Participant_Move_in_Trans."Tier_Level", Participant_Move_in_Trans."Prod_Com_Local", Participant_Move_in_Trans."Prod_Com_Outbound", Participant_Move_in_Trans."Prod_Com_Inbound", Participant_Move_in_Trans."Override_Price_Variation", Participant_Move_in_Trans."Override_Com_Individual", Participant_Move_in_Trans."Min_Individual_Amt", Participant_Move_in_Trans."Other_Com_Type", Participant_Move_in_Trans."Other_Com_Individual_Amt", Participant_Move_in_Trans."Intl_Com_Amt", Participant_Move_in_Trans."Six_Mo_Contract_Com_Amt", Participant_Move_in_Trans."Total_Com", Participant_Move_in_Trans."Total_Pool_Com"
FROM
{ oj "Oakwood"."dbo"."participant" participant INNER JOIN "Oakwood"."dbo"."Participant_Move_in_Trans" Participant_Move_in_Trans ON
participant."id" = Participant_Move_in_Trans."Participant_Id"}
WHERE
Participant_Move_in_Trans."Trans_Category" = 'Local'
ORDER BY
participant."id" ASC


I removed the where portion of the query and ran the reports again. This time the field show LOCAL & INBOUND but the OUTBOUND is still missing.

Can someone please tell me how can I fix this, or what am I doing wrong?

Thanks,

Kchaudhry

 

[synapsevampire]

Note that the join in the query shows only 2 tables being used (FROM line).

Select Database->Visual Linking Expert->Right click the link and select Options->SQL Join Type->Left Outer and see if that helps.

It's difficult to guess the relationships between tables, and Crystal won't honor tables in the design if they aren't used in the report other than by joining them in the li9nking expert.

You'll get better assistance by stating technical information (version of CR and database), supply example data, and expected results.

-k

 

[kchaudhry]

synapsevampire, thanks for your reply. I tried the soultion you suggested with the left outer link and that didnt work.

I am using CR 8.5 and SQL2000, the only data in the {Participant_Move_in_Trans.Trans_Category} field is LOCAL, INBOUND & OUTBOUND. Normally this field(trans_category) will show one of the three values.

I can see that the lower part of the query includes

WHERE
Participant_Move_in_Trans."Trans_Category" = 'Local'

I am not sure how this was added to the query.

Please let me know if you need any more information.

Thanks

 

[synapsevampire]

It's likely added using the Report->Edit Selection Formula->Record

Don't modify the SQL directly.

You have to understand my post in it's entirety, the SQL you supplied is for only 2 tables, not 5.

I think that my solution might stand, but you need to remove the record selection formula and RESET the SQL in the same window as you edited it.

-k