Query not recognizing my criteria

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query not recognizing my criteria

Author

Topic

werhardt
Constraint Violating Yak Guru

270 Posts

Posted - 2010-05-17 : 10:56:18

Hi,

I have this simple query and it seems like it is not regonizing what I want to take out. My one criteria is clm_sppo > 0.00. Once I run the view, it is still showing 0.00 the one column. Not sure why this is happening. Please help!

--AS
SELECT Distinct
--clms_sku,
--clms_line,
--clm_prod,
clm.clm_id1,
clm.clm_lnet,
clm.clm_h22a as MMOClaimNumber,
mem_altid,
clm.clm_rcvd,
CLM_DOUT,
clm_wkpct,
clm.clm_1a,
clm.clm_12a,
clm.clm_12b,
clm.clm_55d,
clm.clm_clir,
clm.clm_65a,
Case
When clm_65a = 'MEDICAL MUTUAL OF OHIO CHAININ' Then 'MEDICAL MUTUAL OF OHIO'
End AS [Client Name FULL],

clm.clm_medb2,
clm.clm_tchg,
clm.clm_base,
clm.clm_stades,

clm_meda4 =
Case
When clm_meda4 = 0.00 Then clm_tchg
else clm_meda4
End,

clm_prod =
CASE
WHEN CLMS_sku = 'NEGO' THEN 'NEG'
ELSE clm_prod
End,
--clm.clm_nego,
"clm_nego" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE clm_nego
END,
--clm.clm_sppo,

"clm_sppo" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE clm_sppo
END,
clip.clip_prd,
clm_1e,
clm.CLM_ATT1 as Note,
"Clm_Att1",

CLM_H30 =
Case
When CLM_H30 = 0.00 Then cast(clm_sppo as decimal (12,2))
else clm_H30
End,

CASE
WHEN clm_att1 = 'NG' AND clm_h30 = 0 THEN CONVERT(DECIMAL(12, 2), ROUND(clm_sppo * clio_fee04 / 100.0, 2))
WHEN clm_att1 = 'NG' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * clio_fee04 / 100.0, 2))
WHEN clm_att1 = 'NA' THEN '0.00'
WHEN clm_att1 = 'AF' AND clm_h30 = 0 THEN CONVERT(DECIMAL(12, 2), ROUND(clm_sppo * clio_fee04 / 100.0, 2))
WHEN clm_att1 = 'AF' AND CLM_dout <='02/29/2008' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * impact.dbo.uf_CalculateAccessFeeByClaimID(clm.clm_id1) / 100.0, 2))*.65
WHEN clm_att1 = 'AF' AND CLM_dout>='03/01/2008' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * impact.dbo.uf_CalculateAccessFeeByClaimID(clm.clm_id1) / 100.0, 2))
ELSE NULL
END AS AccessFeeFinal,

impact.dbo.uf_CalculateAccessFeeByClaimID(clm.clm_id1) as "ACCESSFEEIMPACT",

--clio_fee04 as "ACCESSFEEIMPACT",
/**CASE
WHEN clm_att1 = 'NG' AND clm_h30 = 0 THEN CONVERT(DECIMAL(12, 2), ROUND(clm_sppo * clio_fee04 / 100.0, 2))
WHEN clm_att1 = 'NG' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * clio_fee04 / 100.0, 2))
WHEN clm_att1 = 'NA' THEN '0.00'
WHEN clm_att1 = 'AF' AND clm_h30 = 0 THEN CONVERT(DECIMAL(12, 2), ROUND(clm_sppo * clio_fee04 / 100.0, 2))
--WHEN clm_att1 = 'AF' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * clio_fee04 / 100.0, 2))
WHEN clm_att1 = 'AF' AND CLM_dout <='02/29/2008' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * clio_fee04 / 100.0, 2))*.65
WHEN clm_att1 = 'AF' AND CLM_dout>='03/01/2008' THEN CONVERT(DECIMAL(12, 2), ROUND(clm_H30 * clio_fee04 / 100.0, 2))
ELSE NULL
END AS AccessFeeFinal,**/

CLM_ATT2,
CLM_ATT3,

"(clm_sppo/CLM_TCHG) * 100" =
CASE
WHEN CLM_ATT1 = 'NA' THEN 0.00
WHEN CLM_ATT2 = 'NA' THEN 0.00
WHEN CLM_ATT3 = 'NA' THEN 0.00
WHEN CLM_ATT4 = 'NA' THEN 0.00
ELSE (clm_sppo/CLM_TCHG) * 100
END,

--clio.Clio_tem as MAS90#,
clio_wrk1 as MAS90#,

CLIO_WRK3 as MAS90AF,

CLM_5 as 'TIN'

FROM impact.dbo.clm clm INNER JOIN
impact.dbo.cli cli ON clm.clm_clir = cli.cli_id1 INNER JOIN
impact.dbo.clip clip ON cli.cli_id1 = clip.clip_id1 INNER JOIN
impact.dbo.clio clio ON cli.cli_id1 = clio.clio_id1
INNER JOIN impact.dbo.clms clms ON clms.clms_id = clm.clm_id1
left join impact.dbo.mem mem ON clm.clm_38 = mem.mem_id1

WHERE

(CLM_ATT1 = 'NG') and
clm_clir = '00002504'and
(clm_adjto = '' or
clm_adjto = NULL or
clm_adjto is NULL)
AND CLM_TCHG > 0. and clm_sppo > '0.00' and (clio.clio_type = 'AC')and
(clio_wrk1 <> '' or
clio_wrk1 <> NULL or
clio_wrk1 is not NULL)

Order by clm_sppo

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-17 : 11:40:31

you dont need ' over 0.00
this is enough

clm_sppo > 0.00

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources