counter to date- if possible, syntax help 1

[ironyx]

I am trying to do a counter in an insert statment, where the counter will be turned into a month, then if the month is greater than 09, put it in year 2004, if <= 09, then put it as year 2005. Oh, and just for fun, to make it that last day of that particular month using LAST_DAY. Does this sound plausible? I am not a sql programmer but gave it a stab, if someone could look at the bloody mess and maybe just give me guidance (and don't laugh) then I would greatly appreciate it. Here goes :
BEGIN
INSERT INTO STG_AIRSPEED_PLN_METRICS_COMPL (
RECORD_NB,
TW_PLAN_CD,
SITE_CD,
MONTH_END_DT,
AS_OF_DT)
VALUES (
RECORD_NB_SEQ.nextval,
TW.TW_PLAN_CD,
TW.Site_cd,
(BEGIN
FOR M_counter in 1..12
LOOP
IF (M_Counter > 9)
THEN
LAST_DAY(TO_DATE(TO_CHAR(M_counter 01 2004, 'M DD YYYY') 'MM-DD-YYYY'))
ELSE
LAST_DAY(TO_DATE(TO_CHAR(M_counter 01 2005, 'M DD YYYY') 'MM-DD-YYYY'));
END LOOP,
SYSDATE);
END;

Thanks for any help, time and knowledge!
Va

 

[ironyx]

And now thinking about it, the way I have the loop, it will just loop through all the months for one record in that field rather than looping through each record 12 times and assigning the value of month for each one, so I will have to change the loop to be for each record coming out of the DB. I'll keep trying!

Thanks for any help!
Va

 

[SantaMufasa]

Va,

I'm certain we can help you, but we must understand clearly what you want. If your code actually worked (which it won't in its current incarnation), Oracle would store the equivalent of "31-DEC-2004 00:00:00", since your loop runs unconditionally from "1..12".

So, I need to know upon what do you really wish to base the value of MONTH_END_DT? Don't try to code it, just explain in English the algorithm you want to properly populate MONTH_END_DT. For example, your algorithm explanation could be, "For each INSERTed row, assign to MONTH_END_DT the LAST_DAY of month of the AS_OF_DT."

If the above were a true explanation of your algorithm, you would not need a loop...you could just use the following code:

BEGIN
   INSERT INTO STG_AIRSPEED_PLN_METRICS_COMPL (
     RECORD_NB, 
     TW_PLAN_CD,
     SITE_CD,
     MONTH_END_DT,
     AS_OF_DT)
   VALUES (
     RECORD_NB_SEQ.nextval, 
     TW.TW_PLAN_CD,
     TW.Site_cd,
     [b]LAST_DAY(sysdate)[/b],  
     SYSDATE);
END;

If your algorithm is different from my presumption, please advise me the true algorithm. Also note, if my presumption is correct, you don't even need to store MONTH_END_DT in your table...just use LAST_DAY(AS_OF_DT) whenever you need to display the MONTH_END_DT.

Let us know if this helps to resolve your need.

Mufasa
(aka Dave of Sandy, Utah, USA @ 23:25 (03Oct04) UTC (aka "GMT" and "Zulu"), 16:25 (03Oct04) Mountain Time)

 

[ironyx]

Okay, for every record pulled out of my TW table, I need it to loop through that record 12 times and for each one assign the month end dt of a month. So record one would end up being 12 records with a month end dt of January - December.
Is that a better explanation? I appreciate you taking the time to help me! Do I need to do a cursor, and even if you just explain a little, I might be able to figure it out... although I guess you wouldn't think that from my code. ;-)

Thanks Mufasa!
Va

 

[SantaMufasa]

Aah, now I understand. I would adjust your original code to read:

DECLARE
  HOLD_DATE DATE;
BEGIN
  FOR m_counter IN 1..12 LOOP
   IF (M_Counter > 9) THEN
     HOLD_DATE:=LAST_DAY(TO_DATE(M_counter||'2004','MMYYYY'));
   ELSE
     HOLD_DATE:=LAST_DAY(TO_DATE('0'||M_counter||'2005','MMYYYY'));
   END IF;
   INSERT INTO STG_AIRSPEED_PLN_METRICS_COMPL (
     RECORD_NB, 
     TW_PLAN_CD,
     SITE_CD,
     MONTH_END_DT,
     AS_OF_DT)
   VALUES (
     RECORD_NB_SEQ.nextval, 
     TW.TW_PLAN_CD,
     TW.Site_cd,
     HOLD_DATE,
     SYSDATE);
  END LOOP;
END;

Let us know if this is what you wanted.

 

[ironyx]

I get an error:

The following error has occurred:

ORA-06550: line 19, column 34:
PL/SQL: ORA-00984: column not allowed here
ORA-06550: line 10, column 4:
PL/SQL: SQL Statement ignored

I checked data types, I truncated the table, the field size is the same, is there anything else I should be looking for?

 

[SantaMufasa]

Va,

I presumed (perhaps incorrectly) that you would modify your code to ensure the accessibility of the rest of your data values. Amongst the values that are missing with your existing code are:

     RECORD_NB_SEQ.nextval, 
     TW.TW_PLAN_CD,
     TW.Site_cd

To obtain a value from "RECORD_NB_SEQ.nextval" you must:

SELECT RECORD_NB_SEQ.nextval into <some_variable> from dual;

Then, the values of "TW.TW_PLAN_CD" and "TW.Site_cd" are undefined. Are those values coming from a table? If so, you need to SELECT them either via a CURSOR or "SELECT...INTO...".

If you have error messages, it is a good practice to post a copy-and-paste of the code and its error messages.

Let us know,

Mufasa
(aka Dave of Sandy, Utah, USA @ 16:59 (04Oct04) UTC (aka "GMT" and "Zulu"), 09:59 (04Oct04) Mountain Time)

 

[ironyx]

Sorry, I am pretty much coming at this with about no experience of PL/SQL except for one cursor, so I'm afraid assumptions are lost on me, but I think I can go from here. Thanks for all of the help!