Cast months to single digits? 2

[RustyAfro]

I need to make "ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM IN ('7,8,9')" dynamic. I am messing with code and so far no luck (see below). I am thinking I will have to use an IF statement to determine if the number is below 10, then use logic to remove the 0 using a RIGHT() command of some sort. I do not know how to do this in Oracle SQL ;(. If you can help I would really appreciate it

ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM  IN (

TO_Char(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -2),'MM'),

TO_Char(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -1),'MM'),

TO_Char(TRUNC(SYSDATE,'MM'),'MM')
)

 

[BJCooperIT]

Try:

select TO_Char(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -2),'fmMM') from dual;

^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[RustyAfro]

Thank you BJCooper, I'll try it out now

What does fmMM do? Are there others like it for other purposes?

 

[RustyAfro]

I get this error:

ORA-12801: error signaled in parallel query server P109 :ORA-01821: date format not recognized :-12801

I'm thinking of casting both sides to integers, maybe that will do it (not sure). If you have any ideas why that error comes up..?

 

[SantaMufasa]

Rusty,

Unless you post the code that throws the errors, we must rely on our "Rusty Crystal Balls", which are not showing very clear pictures right now.

BTW, Barb's (BJ's) code, "...'fmMM'..." toggles the suppression of leading spaces in date-format masks. One of my Oracle colleagues once suggested that it stands for "Freakin' Magic".

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RustyAfro]

Hi SantaMufasa,

Sorry that wasn't clear, I was responding to BJCooper. I tried his code which came out to:

ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM  IN (TO_Char(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -2),'fmMM')

If this is only suppressing leading spaces, I don't think it will take care of the leading 0 (ie, 07).

I am trying this below right now which is casting both sides to an integer. I am not sure what this is going to do or work though:

CAST(ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM AS Integer) IN  
(
  CAST(TO_Char(ADD_MONTHS(SYSDATE, -2),'MM') AS Integer)                                               
)

 

[Turkbear]

Hi,
Don't know about your error but the fmMM mask will eliminate the leading Zero:

Here is the result when run today 9/27/2005):

SQL> select TO_Char(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -2),'fmMM') from dual;

TO
--
7

No leading 0...





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

I'll bet that with all the "casting" that you are doing, you are a either a fly fisherman or an angler, right? <grin> Oracle does some pretty remarkable default re-casting without your needing to ask for it. So, if you can show us exactly what you are trying to do, Barb ('BJCooperIT', a lovely lady rather than a "he") and I can probably offer some code that may or may not need to use CAST.

And again, please post a copy-and-paste of the full SQL statement you were trying to use plus the error message it threw.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RustyAfro]

You guys/gals are sure nicer then the SQL Server guys/gals are (they are nice too ;0)

Not sure why this doesn't work, get error (ORA-12801: error signaled in parallel query server P109 :ORA-01821: date format not recognized :-12801) using: "ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM IN (TO_Char(ADD_MONTHS(TRUNC(SYSDATE,'MM'), -2),'fmMM')", but I just found out that RERATE_MTH_NUM is actually a char(2) even though it holds numbers, maybe that could be why?

I tried this below and it worked (7,8,9):

CAST(ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM AS Integer) IN  (
    CAST(TO_Char(ADD_MONTHS(SYSDATE, -2),'MM') AS Integer),
    CAST(TO_Char(ADD_MONTHS(SYSDATE, -1),'MM') AS Integer),
    CAST(TO_Char(SYSDATE, 'MM') AS Integer)
    )

Here is the whole thing:

SELECT
  ODW.EMPLOYER_GROUPS.CLIENT_ID,
  ODW.EMPLOYER_GROUPS.GROUP_ID,
  ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM,
  ODW.EMPLOYER_GROUPS.LAST_RERATE_DATE,
  ODW.EMPLOYER_GROUPS.GROUP_STAT,
  ODW.CLIENT_MASTER.CLIENT_NAME,
  ODW.PRD656_LINE_BUSNS_SUB_CAT_CODE.LOB_SUB_SHORT_DESC,
  ODW.PRD652_PRODUCT_CLASSIFY_CODE.PRD_CLS_SHORT_DESC,
  ODW.CLIENT_MASTER.CNCL_EFF_DATE,
  count(distinct(ODW.ELIGIBILITY.PERSON_ID||ODW.ELIGIBILITY.SUBSCRIBER_ID)),
  ODW.EMPLOYER_GROUPS.GROUP_STAT_EFF_DATE,
  ODW.EMPLOYER_GROUPS.RMC_CODE,
  ODW.EMPLOYER_GROUPS.ACCT_EXEC_FULL_NAME,
  ODW.EMPLOYER_GROUPS.ACCT_MGR_FULL_NAME,
  ODW.CLIENT_MASTER.MKTG_REGION_CODE,
  ODW.EMPLOYER_GROUPS.BILL_DAY_LOW
FROM
  ODW.ELIGIBILITY,
  ODW.EMPLOYER_GROUPS,
  ODW.CLIENT_MASTER,
  ODW.PRD650_PRODUCT_CODE,
  ODW.PRD656_LINE_BUSNS_SUB_CAT_CODE,
  ODW.PRD652_PRODUCT_CLASSIFY_CODE
WHERE
  ( ODW.EMPLOYER_GROUPS.HOST_SYSTEM_ID(+)=ODW.ELIGIBILITY.HOST_SYSTEM_ID  )
  AND  ( ODW.EMPLOYER_GROUPS.GROUP_ID(+)=ODW.ELIGIBILITY.GROUP_ID  )
  AND  ( ODW.CLIENT_MASTER.CLIENT_ID(+)=ODW.EMPLOYER_GROUPS.CLIENT_ID  )
  AND  ( ODW.CLIENT_MASTER.HOST_SYSTEM_ID(+)=ODW.EMPLOYER_GROUPS.HOST_SYSTEM_ID  )
  AND  ( ODW.PRD650_PRODUCT_CODE.PRODUCT_CLASSIFICATION=ODW.PRD652_PRODUCT_CLASSIFY_CODE.PRODUCT_CLASSIFICATION  )
  AND  ( ODW.PRD650_PRODUCT_CODE.LINE_OF_BUSINESS_SUB_CAT=ODW.PRD656_LINE_BUSNS_SUB_CAT_CODE.LINE_OF_BUSINESS_SUB_CAT  )
  AND  ( ODW.ELIGIBILITY.PRODUCT_CODE=ODW.PRD650_PRODUCT_CODE.PRODUCT_CODE(+)  )
  AND  (
  ODW.ELIGIBILITY.BUSINESS_UNIT_ID  =  'HNCA'
  AND  ODW.EMPLOYER_GROUPS.GROUP_STAT  =  'A'
  AND  ODW.PRD650_PRODUCT_CODE.LINE_OF_BUSINESS_SUB_CAT  IN  ('01', '04')
  AND  CAST(ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM AS Integer) IN  (
    CAST(TO_Char(ADD_MONTHS(SYSDATE, -2),'MM') AS Integer),
    CAST(TO_Char(ADD_MONTHS(SYSDATE, -1),'MM') AS Integer),
    CAST(TO_Char(SYSDATE, 'MM') AS Integer)
    )
  )
GROUP BY
  ODW.EMPLOYER_GROUPS.CLIENT_ID, 
  ODW.EMPLOYER_GROUPS.GROUP_ID, 
  ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM, 
  ODW.EMPLOYER_GROUPS.LAST_RERATE_DATE, 
  ODW.EMPLOYER_GROUPS.GROUP_STAT, 
  ODW.CLIENT_MASTER.CLIENT_NAME, 
  ODW.PRD656_LINE_BUSNS_SUB_CAT_CODE.LOB_SUB_SHORT_DESC, 
  ODW.PRD652_PRODUCT_CLASSIFY_CODE.PRD_CLS_SHORT_DESC, 
  ODW.CLIENT_MASTER.CNCL_EFF_DATE, 
  ODW.EMPLOYER_GROUPS.GROUP_STAT_EFF_DATE, 
  ODW.EMPLOYER_GROUPS.RMC_CODE, 
  ODW.EMPLOYER_GROUPS.ACCT_EXEC_FULL_NAME, 
  ODW.EMPLOYER_GROUPS.ACCT_MGR_FULL_NAME, 
  ODW.CLIENT_MASTER.MKTG_REGION_CODE, 
  ODW.EMPLOYER_GROUPS.BILL_DAY_LOW

 

[SantaMufasa]

Rusty,

Here is code that you can try instead. This code does not need and CASTing. To ensure that all of the values will be correct, algebraic comparisons (versus character comparisons), I am using invocations of TO_NUMBER fuctions:

...AND   TO_NUMBER(ODW.EMPLOYER_GROUPS.RERATE_MTH_NUM) BETWEEN
         TO_NUMBER(TO_Char(ADD_MONTHS(SYSDATE, -2),'MM')) AND
         TO_NUMBER(TO_Char(SYSDATE, 'MM'))...

Try this (just for fun) and let us know the results.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[SantaMufasa]

I hate it when I see a mistake after I have hit [Submit Post]. As you probably noticed, my post should have read, "This code does not need any CASTing," instead of "...and CASTing." Sorry.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[RustyAfro]

That does work, thank you!