String Conversion

[kchaudhry]

I am using CR 8.5 with SQL server 2000. I have a formula which inlculdes a String field and a Number field. Here is what I have so far.

if
{Participant_Move_in_Trans.Trans_Category} = "Local"
and
{Participant_Move_in_Trans.Tier_Level} = 1.00
and
{Participant_Move_in_Trans.Prod_Com_Elig} = "Y"
and
CStr({plan_manager.set_year}) = Left ({Participant_Move_in_Trans.Commission_Qualifying_Date}, 4)
And
CStr({plan_manager.set_period_num}) = Mid ({Participant_Move_in_Trans.Commission_Qualifying_Date},5,2)
then
1
else
0

{Participant_Move_in_Trans.Commission_Qualifying_Date} is a string field with data like "20030801"
{plan_manager.set_year} is a number field with data like "2003"
{plan_manager.set_year} is a number field with data like "8"


My problem is that I am not sure how to convert a number into a string field, not even sure if this is required. When I put the sum of this formula in my group summary it gives me 0.

Can anyone tell me how can I fix this?

Thanks in advance.




Kchaudhry

 

[lbass]

So the formula 1 should have returned "08" as one of its records, right? If you put the field on the report by itself (not in a formula) do you get multiple values?

-LB

 

[kchaudhry]

LB,
I placed formula 1 in the group footer, but yes it should have returned "08". Here is the output if I put the fields on the report without formulas.

{Participant_Move_in_Trans.Commission_Qualifying_Date}
Output : 20030831

{plan_manager.set_period_num}
Output : 0

{plan_manager.set_year}
Output :0

In these last two fields when I go to browse field data I get "8" and "2003" respectively.

Kchaudhry

 

[lbass]

It doesn't look like the plan manager fields are returning data. How do you have the two tables linked?

-LB

 

[kchaudhry]

The linking is as follows:

{Participant} Linked to {Plan_Manager} via the "keyfield" using left outer join.



Kchaudhry

 

[lbass]

I'm confused about whether you have shown us your current SQL statement, copied from Database->"Show SQL Query". Is it possible that you have built into the query itself some clauses that are inconsistent with the formula you are trying to write? Please copy it here.

Also, if you have a left join from Participant to Plan Manager, it implies that there may be some nulls in the Plan Manager table. I think you need to lay out the fields in the detail section to see if you get any 8's for Period Number, etc.

And what is the "keyfield" from each table?

-LB

 

[kchaudhry]

LB,
Here is my SQL Query.
SELECT DISTINCT
participant."name_last", participant."name_first", participant."id", participant."position",
formula."formula", formula."compensation_total",
Participant_Move_in_Trans."Project", Participant_Move_in_Trans."Zone", Participant_Move_in_Trans."Apartment_Type", 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."Six_Mo_Lease_Indicator", Participant_Move_in_Trans."Term", Participant_Move_in_Trans."International_Indicator", Participant_Move_in_Trans."Trans_Type", 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_Elig", 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",
compensation."compensation_total",
plan_manager."set_period_num", plan_manager."set_period_des", plan_manager."set_year",
LocalYTD."LOC_TRANS_CNT",
InboundYTD."IN_TRANS_CNT",
OutboundYTD."OUT_TRANS_CNT",
InternationalYTD."INTERNATIONAL_TRANS_CNT",
ShortTermYTD."SHT_TRM_TRANS_CNT",
SixMonthYTD."IN_TRANS_CNT",
UnfurnishedYTD."INTERNATIONAL_TRANS_CNT",
WholesaleYTD."INTERNATIONAL_TRANS_CNT"
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&quot
INNER JOIN "Oakwood"."dbo"."compensation" compensation ON
participant."id" = compensation."id&quot
INNER JOIN "Oakwood"."dbo"."formula" formula ON
participant."id" = formula."id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."plan_manager" plan_manager ON
participant."keyfield" = plan_manager."keyfield&quot
LEFT OUTER JOIN "Oakwood"."dbo"."InboundYTD" InboundYTD ON
Participant_Move_in_Trans."Participant_Id" = InboundYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."OutboundYTD" OutboundYTD ON
Participant_Move_in_Trans."Participant_Id" = OutboundYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."InternationalYTD" InternationalYTD ON
Participant_Move_in_Trans."Participant_Id" = InternationalYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."ShortTermYTD" ShortTermYTD ON
Participant_Move_in_Trans."Participant_Id" = ShortTermYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."SixMonthYTD" SixMonthYTD ON
Participant_Move_in_Trans."Participant_Id" = SixMonthYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."UnfurnishedYTD" UnfurnishedYTD ON
Participant_Move_in_Trans."Participant_Id" = UnfurnishedYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."WholesaleYTD" WholesaleYTD ON
Participant_Move_in_Trans."Participant_Id" = WholesaleYTD."Participant_Id&quot
LEFT OUTER JOIN "Oakwood"."dbo"."LocalYTD" LocalYTD ON
Participant_Move_in_Trans."Participant_Id" = LocalYTD."Participant_Id"}
WHERE
(participant."position" = 'OAKWOOD AC' OR
participant."position" = 'OAKWOOD AE' OR
participant."position" = 'OCH AC' OR
participant."position" = 'OCH AE')
ORDER BY
participant."id" ASC,
Participant_Move_in_Trans."Reservation_Date" ASC,
Participant_Move_in_Trans."Orig_Move_in_Date" ASC,
Participant_Move_in_Trans."Project" ASC,
Participant_Move_in_Trans."Sub_Project_Code" ASC,
Participant_Move_in_Trans."Building" ASC,
Participant_Move_in_Trans."Apartment" ASC

The "Keyfield" is a system field and is of "char" type. I tried to use an equal join from {Particiapnt} to {Plan_Manager} and it gave me no data in the report. Once I change it to left outer join it starts working.
I tried to put the fields (preiod_year) in the details section and they still give me zeros.

I am really grateful for all your time and consideration.

Kchaudhry

 

[lbass]

Have you tried verifying the database? (Database->verify database). I don't think I can be of any more help--I just don't have the kind of expertise to take this further. I hope someone else will jump in to contribute. Good luck...

-LB

 

[kchaudhry]

Lbass,

Thanks for all your help. I will try to do some more research and see if I can come up with any other options.

Kchaudhry