Same complex query returns different results????

[gwoman]

Hey there ....

I have a rather complex query that shows all objects that have been off for more than 180 days ago from Now(). I cut and pasted the SQL into a new query and only changed the 180 to 90. So my results would be objects from my first query + the Objects that have been off more than 90 days ago from Now(). When I run the 90 day query my results include 2 objects that display as more that 180 days from Now() ... but those 2 Objects do NOT appear in my 180 days query.
Does anyone see something I may have missed???
I would be extremely gratefule for any possible insight ... thanks!
gwoman

SELECT
DWRPTG.CMPL_DMN.PRDU_NME,
DWRPTG.CMPL_DMN.IN_SVC_INDC_EFTV_DTTM,
DWRPTG.CMPL_DMN.CMPL_NME,
DWRPTG.CMPL_DMN.PRIM_PURP_TYPE_CDE,
DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_OPNL_STAT_ON_INDC,
DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_OPNL_STAT_EFTV_DTTM,
DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_OFF_CNDN_RSN_TYPE_CDE,
DWRPTG.CMPL_DMN.IN_SVC_INDC,
DWRPTG.DDB_BLM_FLAG.BLM_SI_EXP_DTE,
DWRPTG.DDB_BLM_FLAG.BLM_TA_EXP_DTE,
DWRPTG.CMPL_DMN.CMPL_STATE_TYPE_CDE,
Case When DWRPTG.CMPL_DMN.PRIM_PURP_TYPE_CDE != 'OBSN' Then DWRPTG.CMPL_DMN.CMPL_STATE_EFTV_DTTM
Else
(Case When DWRPTG.CMPL_DMN.PRIM_PURP_EFTV_DTTM > DWRPTG.CMPL_DMN.IN_SVC_INDC_EFTV_DTTM
Then DWRPTG.CMPL_DMN.PRIM_PURP_EFTV_DTTM Else DWRPTG.CMPL_DMN.IN_SVC_INDC_EFTV_DTTM End ) End
as Cmpl_State_Purp_EFTV_DTTM ,
CURR_STAT.Max_STAT_DATE
FROM
DWRPTG.DDB_BLM_FLAG,
(SELECT
cs.CMPL_FAC_ID
,Max(cs.CURR_STAT_CHANGE_DATE) AS Max_STAT_DATE
,Max(cs.CURR_STAT) AS MaxOfCURR_STAT
,'OBSN' as Not_Type
FROM
DWRPTG.CURR_CMPL_OPNL_STAT cs
,(SELECT Max(CURR_STAT_CHANGE_DATE) AS Max_STAT_DATE, CMPL_FAC_ID
FROM DWRPTG.CURR_CMPL_OPNL_STAT
GROUP BY CMPL_FAC_ID) d
Where
cs.CURR_STAT_CHANGE_DATE = d.Max_STAT_DATE
AND cs.CMPL_FAC_ID = d.CMPL_FAC_ID
GROUP BY
cs.CMPL_FAC_ID) CURR_STAT,
DWRPTG.CMPL_DMN,
DWRPTG.CMPL_OPNL_STAT_DMN
WHERE
( CURR_STAT.CMPL_FAC_ID = DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_FAC_ID(+) )
AND ( DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_OPNL_STAT_TERM_DTTM Is Null )
AND ( DWRPTG.DDB_BLM_FLAG.CMPL_ID (+)= DWRPTG.CMPL_DMN.CMPL_FAC_ID )
AND ( DWRPTG.CMPL_DMN.CMPL_FAC_ID = CURR_STAT.CMPL_FAC_ID(+)
And DWRPTG.CMPL_DMN.PRIM_PURP_TYPE_CDE <> CURR_STAT.Not_Type(+) )
AND (
DWRPTG.CMPL_DMN.ACTV_INDC = 'Y'
AND DWRPTG.DDB_BLM_FLAG.BLM_FLG = 'Y'
AND DWRPTG.CMPL_DMN.PRDU_NME NOT IN ('Beta Unit')
AND DWRPTG.CMPL_DMN.CMPL_STATE_TYPE_CDE = 'OPNL'
AND (DWRPTG.DDB_BLM_FLAG.BLM_SI_EXP_DTE <= TRUNC(sysdate,'DDD')
OR DWRPTG.DDB_BLM_FLAG.BLM_SI_EXP_DTE IS NULL )
AND (DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_OPNL_STAT_ON_INDC = 'N'
AND CURR_STAT.Max_STAT_DATE <= TRUNC(sysdate - 90,'DDD')
OR DWRPTG.CMPL_OPNL_STAT_DMN.CMPL_OPNL_STAT_ON_INDC IS NULL
AND DWRPTG.CMPL_DMN.PRIM_PURP_TYPE_CDE != 'OBSN'
AND DWRPTG.CMPL_DMN.CMPL_STATE_EFTV_DTTM <= (Now()- 90)
OR DWRPTG.CMPL_DMN.PRIM_PURP_TYPE_CDE = 'OBSN'
AND DWRPTG.CMPL_DMN.IN_SVC_INDC_EFTV_DTTM <= (Now() - 90)
AND DWRPTG.CMPL_DMN.IN_SVC_INDC = 'N')
AND DWRPTG.CMPL_DMN.CMPL_FAC_ID NOT IN (SELECT
DWRPTG.CMPL_DMN.CMPL_FAC_ID
FROM
VOLRPTG.DTE_DMN,
DWRPTG.CMPL_DMN,
DWRPTG.CMPL_DLY_FACT
WHERE
( DWRPTG.CMPL_DMN.CMPL_DMN_KEY=DWRPTG.CMPL_DLY_FACT.CMPL_DMN_KEY )
AND ( VOLRPTG.DTE_DMN.FULL_DTE=DWRPTG.CMPL_DLY_FACT.EFTV_DTTM )
AND (
TRUNC(VOLRPTG.DTE_DMN.FULL_DTE,'DDD') > (Now() - 90)
AND DWRPTG.CMPL_DLY_FACT.INJ_UPTM_SECS_QTY > 0
)
)
)
ORDER BY
1


-- OUt
Thanks!

 

[traingamer]

WHERE
  ( DWRPTG.CMPL_DMN.CMPL_DMN_KEY=DWRPTG.CMPL_DLY_FACT.CMPL_DMN_KEY  )
  AND  ( VOLRPTG.DTE_DMN.FULL_DTE=DWRPTG.CMPL_DLY_FACT.EFTV_DTTM  )
  AND  (
  TRUNC(VOLRPTG.DTE_DMN.FULL_DTE,'DDD') [COLOR=red] > [/color] (Now() - 90)
  AND  DWRPTG.CMPL_DLY_FACT.INJ_UPTM_SECS_QTY  >  0
  )
)
  )
ORDER BY
  1

traingamer