I'm struggling with a Case statement. The problem I has is with doing
>= I can use any value in there, but need to check if it's greater or
equal to 1. I'm sure I'm missing something but can't figure out what.
I've put the line below in case anyone has any suggestions. I've
limited it to the offending line, but can add more if needed.
This version works
------
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,
This version fails
------
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,
Thanks in advance.
Ryanryanofford@.hotmail.com (Ryan) wrote in news:7802b79d.0311190628.73e93bb0
@.posting.google.com:
> I'm struggling with a Case statement. The problem I has is with doing
>>= I can use any value in there, but need to check if it's greater or
>>equal to 1. I'm sure I'm missing something but can't figure out what.
> I've put the line below in case anyone has any suggestions. I've
> limited it to the offending line, but can add more if needed.
> This version works
> ------
> CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
> THEN SUM(3*m.Premium/100) - (Introducer *
> (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> Bond2,
> This version fails
> ------
> CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>>=1 THEN SUM(3*m.Premium/100) - (Introducer *
> (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> Bond2,
From BOL:
Syntax
Simple CASE function:
CASE input_expression
WHEN when_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END
Searched CASE function:
CASE
WHEN Boolean_expression THEN result_expression
[...n]
[
ELSE else_result_expression
]
END
Don't try to mix the two. Use the second form, i.e.
... CASE WHEN m.Sacrifice>=1 THEN ...
HTH|||What error are you getting?
You are mixing scalar and aggregate values, which is fine if you are
grouping the data appropriately but erroneous otherwise.
An error message would help us to help you...
HTH
Steve
=======================================
Everyone here speaks SQL; some are more fluent, others less. When
describing your SQL object (table, etc.), do so in the language that we
all understand - SQL, not English. It makes it easier to understand
your issue and makes it more likely that you will get the assistance
that you are asking for.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Hi Ryan,
CASE actually has 2 flavors. Those tricky microsofties. I think you
want the second flavor. - Louis
a) CASE variable WHEN literal value THEN this expression ...
b) CASE WHEN expression THEN this expression...|||The error message is.
Server: Msg 170, Level 15, State 1, Line 3
Line 3: Incorrect syntax near '>'.
The full version of the query is as follows (for those who want to
read it).
The only parts that cause me problems is using the greater than or
equal to expression. Each offending line bar the first problem is
commented out, but it's the same problem on each. At least I'm
consistent :-)
SELECT
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Rebate WHEN
NULL THEN 0 ELSE SUM(3*m.Premium/100) END END AS Bond1,
CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond2,
-- CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Rebate WHEN
>=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond3,
-- CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Introducer
WHEN >=1 THEN SUM(3*m.Premium/100) - (Introducer *
(SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
Bond4,
0 AS Bond2,
0 AS Bond3,
0 AS Bond4,
CASE m.Freq WHEN '' THEN m.Gross ELSE 0 END AS ISA1,
-- CASE SUM(m.Gross * 60) WHEN (SUM(m.Gross * 60) >= 360) THEN 360
ELSE 0 END AS ISA2,
0 AS ISA2,
0 AS ISA3, /*Empty*/
0 AS ISA4, /*Empty*/
CASE m.Freq WHEN '' THEN m.Gross ELSE 0 END AS AccSick1,
-- CASE m.PaymentMethod WHEN 'L' THEN CASE SUM(Gross*12) WHEN
(Gross*12) = 360 THEN 360 ELSE m.Gross END ELSE 0 END AS AccSick2,
0 AS AccSick2,
CASE m.PaymentMethod WHEN 'L' THEN 0 ELSE SUM(Gross-Payaway)*100 END
AS AccSick3,
0 AS AccSick4, /*Empty*/
SUM(m.Gross-m.Payaway) * 100 AS General1,
0 AS General2, /*Empty*/
0 AS General3, /*Empty*/
0 AS General4, /*Empty*/
CASE m.PaymentMethod WHEN 'L' THEN SUM(m.Gross*36) ELSE 0 END AS
Pensions1,
CASE m.PaymentMethod WHEN 'L' THEN 0 ELSE SUM((m.Gross - m.Payaway)
* 100) END AS Pensions2,
0 AS Pensions3, /*Empty*/
0 AS Pensions4, /*Empty*/
m.YearNo,
m.PeriodNo,
m.Weekno,
m.Auditref,
m.AuditReflocation,
m.Adjustaudit,
m.Element,
m.Elementlocation,
m.Elementtype,
m.AssignDate,
m.EarnComm,
m.Introducer,
m.Gross,
m.Provision,
m.Rebate,
m.RetBranch,
m.Sacrifice,
m.Payaway,
m.Premium,
m.MyCredit,
m.Credit,
m.freq,
m.Costcode,
m.Product_group,
m.TransStatus,
m.Splinter_status,
m.Provider_ref,
m.Brand_ref,
m.Source_ref,
m.Source_desc,
m.Client_ref,
m.client,
m.Provider,
m.Holding_ref,
m.ProdDesc,
m.Prodcode,
m.Class,
m.Classdesc,
m.ConHierLevRef1,
m.ConHierLevDes1,
m.ConHierLevRef2,
m.ConHierLevDes2,
m.ConHierLevRef3,
m.ConHierLevDes3,
m.ConHierLevRef4,
m.ConHierLevDes4,
m.RetHierLevRef1,
m.RetHierLevDes1,
m.RetHierLevRef2,
m.RetHierLevDes2,
m.RetHierLevRef3,
m.RetHierLevDes3,
m.RetHierLevRef4,
m.RetHierLevDes4,
m.Analysis_1_reference,
m.analysis_2_reference,
m.StreamType,
m.Start_date,
m.Leave_date,
m.Pia_adv_comp_achieve,
m.Effectivedate,
m.Statusatcreate,
m.Policy_contract_number,
m.Commencementdate,
m.User_ID,
m.Date_time,
m.Estimated_initial_comm_amt,
m.Admin_Status,
m.Admin_Status_Date,
m.Campaign_ref,
m.Campaign,
m.ManualJournalRef,
m.Notes,
m.UserID,
m.JournalDate,
m.FormulaApplied,
m.UpliftApplied,
m.Date,
m.Retainer,
m.CommInitialPeriod,
m.PaymentMethod
FROM
CBFA_MISData_Local m
WHERE
m.AssignDate = 20031031 AND
m.Element = 183519 AND
m.ConHierLevRef1 = 6093138 AND
m.Gross = 625.0
GROUP BY
m.YearNo,
m.PeriodNo,
m.Weekno,
m.Auditref,
m.AuditReflocation,
m.Adjustaudit,
m.Element,
m.Elementlocation,
m.Elementtype,
m.AssignDate,
m.EarnComm,
m.Introducer,
m.Gross,
m.Provision,
m.Rebate,
m.RetBranch,
m.Sacrifice,
m.Payaway,
m.Premium,
m.MyCredit,
m.Credit,
m.freq,
m.Costcode,
m.Product_group,
m.TransStatus,
m.Splinter_status,
m.Provider_ref,
m.Brand_ref,
m.Source_ref,
m.Source_desc,
m.Client_ref,
m.client,
m.Provider,
m.Holding_ref,
m.ProdDesc,
m.Prodcode,
m.Class,
m.Classdesc,
m.ConHierLevRef1,
m.ConHierLevDes1,
m.ConHierLevRef2,
m.ConHierLevDes2,
m.ConHierLevRef3,
m.ConHierLevDes3,
m.ConHierLevRef4,
m.ConHierLevDes4,
m.RetHierLevRef1,
m.RetHierLevDes1,
m.RetHierLevRef2,
m.RetHierLevDes2,
m.RetHierLevRef3,
m.RetHierLevDes3,
m.RetHierLevRef4,
m.RetHierLevDes4,
m.Analysis_1_reference,
m.analysis_2_reference,
m.StreamType,
m.Start_date,
m.Leave_date,
m.Pia_adv_comp_achieve,
m.Effectivedate,
m.Statusatcreate,
m.Policy_contract_number,
m.Commencementdate,
m.User_ID,
m.Date_time,
m.Estimated_initial_comm_amt,
m.Admin_Status,
m.Admin_Status_Date,
m.Campaign_ref,
m.Campaign,
m.ManualJournalRef,
m.Notes,
m.UserID,
m.JournalDate,
m.FormulaApplied,
m.UpliftApplied,
m.Date,
m.Retainer,
m.CommInitialPeriod,
m.PaymentMethod
-------
Stephen Hendricks <happy@.londonfg.com> wrote in message news:<3fbba41e$0$202$75868355@.news.frii.net>...
> What error are you getting?
> You are mixing scalar and aggregate values, which is fine if you are
> grouping the data appropriately but erroneous otherwise.
> An error message would help us to help you...
> HTH
> Steve
> =======================================
> Everyone here speaks SQL; some are more fluent, others less. When
> describing your SQL object (table, etc.), do so in the language that we
> all understand - SQL, not English. It makes it easier to understand
> your issue and makes it more likely that you will get the assistance
> that you are asking for.
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!|||Ah ha ! That does it. Knew it was something simple. Thanks for your help !
Chris Cheney <cjc1@.nospam%ucs.cam.ac.uk%no%spam%please> wrote in message news:<Xns9438ACB8BFE0Ccjc1nospamucscamacuk@.131.111.8.69>...
> ryanofford@.hotmail.com (Ryan) wrote in news:7802b79d.0311190628.73e93bb0
> @.posting.google.com:
> > I'm struggling with a Case statement. The problem I has is with doing
> >>= I can use any value in there, but need to check if it's greater or
> >>equal to 1. I'm sure I'm missing something but can't figure out what.
> > I've put the line below in case anyone has any suggestions. I've
> > limited it to the offending line, but can add more if needed.
> > This version works
> > ------
> > CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN 0
> > THEN SUM(3*m.Premium/100) - (Introducer *
> > (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> > Bond2,
> > This version fails
> > ------
> > CASE m.Splinter_Status WHEN 'SUR' THEN 0 ELSE CASE m.Sacrifice WHEN
> >>=1 THEN SUM(3*m.Premium/100) - (Introducer *
> > (SUM(3*m.Premium)/100)/m.Estimated_Initial_Comm_Amt) ELSE 0 END END AS
> > Bond2,
> From BOL:
> Syntax
> Simple CASE function:
> CASE input_expression
> WHEN when_expression THEN result_expression
> [...n]
> [
> ELSE else_result_expression
> ]
> END
> Searched CASE function:
> CASE
> WHEN Boolean_expression THEN result_expression
> [...n]
> [
> ELSE else_result_expression
> ]
> END
> Don't try to mix the two. Use the second form, i.e.
> ... CASE WHEN m.Sacrifice>=1 THEN ...
> HTH
