Inherited Formula in Wrong Syntax 2

[lkerr]

I inherited a formula from my work predecessor that is nothing but problematic. I don't write or understand basic syntax, but that's what its written in, and it has a myriad of problems. Here it is:

Select case({vInsertionMaster.JobPaperGroupName}),
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=3 then 1 else 0 end )as janInsertionCount ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=4 then 1 else 0 end )as febInsertionCount ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=5 then 1 else 0 end )as MarInsertionCount ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=6 then 1 else 0 end )as AprInsertionCount ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=7 then 1 else 0 end )as MayInsertionCount ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=8 then 1 else 0 end )as JunInsertionCount ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=3 then {vInsertionMaster.InsertionAgencyGrossAmount} else 0 end )as janAgencyGross ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=4 then {vInsertionMaster.InsertionAgencyGrossAmount} else 0 end )as febAgencyGross ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=5 then {vInsertionMaster.InsertionAgencyGrossAmount} else 0 end )as MarAgencyGross ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=6 then {vInsertionMaster.InsertionAgencyGrossAmount} else 0 end )as AprAgencyGross ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=7 then {vInsertionMaster.InsertionAgencyGrossAmount} else 0 end )as MayAgencyGross ,
sum(case when DatePart(m,{vInsertionMaster.InsertionDate})=8 then {vInsertionMaster.InsertionAgencyGrossAmount} else 0 end )as JunAgencyGross from insertions,JobPapers where {vInsertionMaster.InsertionClientID}=16 and {vInsertionMaster.JobPaperID}={vInsertionMaster.InsertionJobPaperID} and {vInsertionMaster.InsertionDate}>='03/01/05' and {vInsertionMaster.InsertionDate}<='12/31/05' group by {vInsertionMaster.JobPaperGroupName} order by {vInsertionMaster.JobPaperGroupName}

The formula is in an accounting report that is (I believe) supposed to be figuring the sum of the Gross for each month. However, I'm not entirely sure, because it has never done so. :0 I am using CR9 on a SQL server, if that helps.

Let me know if I've left out any vital information. What do I need to do to correct the syntax errors here?

Thanks in advance!

LK

 

[vidru]

Where is this text (a formula, SQL Expression field, Command, other)?

It's almost a valid SQL statement, but in it's current state, I don't think anything would be able to interpret it.

If it's supposed to be a Command, then the problems I see are:
1) The first line should be:
"Select vInsertionMaster.JobPaperGroupName, "
2) Get rid of all of the french braces ({}).
3) The JobPapers table is in the FROM clause, but there is no JOIN enforced in the WHERE clause. I think that this part of the WHERE clause...
[tt]vInsertionMaster.JobPaperID=vInsertionMaster.InsertionJobPaperID[/tt]
... was meant to be the join between the two tables, likely this:
[tt]JobPapers.JobPaperID=vInsertionMaster.InsertionJobPaperID[/tt]

It parses ok for me in Query Analyzer once I make those changes, so syntactically, it's a valid SQL statement. As to whether it's sound logically, I've no idea.

-dave

 

[lkerr]

He wrote it as a formula {@MonthlySum}. Where should I move it to to make it valid?

LK

 

[rosemaryl]

Dear Ikerr,

You cannot make this a valid formula. This is some kind of sql statement, maybe where he intended to create a view ... It looks to me like he pasted something he/she intended to retain as documentation or reference of something.

A crystal formula cannot contain a select, from, where clause, and Group by clause.

This also cannot be a valid SQL Expression because it selects more then 1 record.

I would give up on making this particular formula valid and move ahead to the problem of what you need a formula to do to make the report meet its specifications.

Best regards,
ro



Rosemary Lieberman
rosemary-at-microflo.com,

http://www.microflo.com

Microflo provides expert consulting on MagicTSD and Crystal Reports.

You will get answers more quickly if you read this before posting: faq149-3762

 

[lkerr]

I have written a couple of formulas to do what I think this formula was attempting (or supposed to do). I will just delete this. Thank you all for your help. I have been messing with this for almost a week (no idea it was a SQL statement - I'm by no means a programmer.

Thank you both so much!