insert records using parameters

have you tried?!:

with p as (select 2015 yr from dual )
insert into date_cal(year_no,month_no,date_cal_name,check_el)
select p.yr,2,'02 - 2015',0;
                                  

Select allOpen in new window

Whereas I doubt that inserts work with WITH statements...

ok
do you have any other solution

in oracle, the syntax is like this:

insert into date_cal
(year_no,month_no,date_cal_name,check_el)
with p as (select 2015 yr from dual )
select p.yr,2,'02 - 2015',0 
from p; 

Select allOpen in new window

surrano
it is perfect
only one thing
the format of
to_char(m,'00')||' - '||to_char(y,'0000')

it comes like this :
 01 -  2015
instead of

12 - 2014

What month and year values are you wanting to store in the table?

To insert all 12 months of a year try this...


insert into date_cal(year_no,month_no,date_cal_name,check_el)
with p as (select 2015 yr from dual )
select yr, level, to_char(level,'fm00') || ' - ' || to_char(yr),0 from p connect by level <= 12;

>>To insert all 12 months of a year try this...

Sort of the reason for my question.  If they just wanted the 12 months of any given year you can likely do this with a view and not have to physically create a table.

it comes like this :
 01 -  2015
instead of
12 - 2014

In your original example:

01 - 2015  where year = 2015 and month= 01

 the date_cal actually contains the year_no and month_no of the same line. So would you like to have something one month before?

...
      values (y, m, 
              to_char(to_date(to_char(m,'00')||to_char(y,'0000'),'mmyyyy')-1,'mm - yyyy'), 
              0);
...

Select allOpen in new window

note that I used date - 1 to convert from first day of "this" month to last day of "prev" month so if you use date -2 (or even date -28) it will be the same effect. Two months ago would be something like date - 32, three months date - 63 four months date - 94 etc.

I'm assuming you want a table so you can later update the 0 in check_el to other values over time.

If you did want to create a view it might look something like this...

This will create 50 years of monthly values starting with January 2000

CREATE OR REPLACE VIEW test_view
AS
    WITH p
         AS (    SELECT ADD_MONTHS(DATE '2000-01-01', LEVEL - 1) d
                   FROM DUAL
             CONNECT BY LEVEL <= 12 * 50)
    SELECT TO_NUMBER(TO_CHAR(d, 'yyyy')) year_no,
           TO_NUMBER(TO_CHAR(d, 'mm')) month_no,
           TO_CHAR(d, 'mm - yyyy') date_cal_name,
           0 check_el
      FROM p;

In this case though I have to wonder what you would need a fixed 0 for in the view.

>>i want to write a procedure to fill data of all months & years to it <<
What do you consider to be ALL months and years? That could cover a pretty long span of time (e.g. 4000 BC to current year or beyond).

I'm assuming "ALL" really means "enough that I don't have to worry about running out'

Use same idea as the view and the insert I posted above

insert into date_cal(year_no,month_no,date_cal_name,check_el)
WITH p
         AS (    SELECT ADD_MONTHS(DATE '2000-01-01', LEVEL - 1) d
                   FROM DUAL
             CONNECT BY LEVEL <= 12 * 50)
    SELECT TO_NUMBER(TO_CHAR(d, 'yyyy')) year_no,
           TO_NUMBER(TO_CHAR(d, 'mm')) month_no,
           TO_CHAR(d, 'mm - yyyy') date_cal_name,
           0 check_el
      FROM p;

if you want more than 2000-2049,  simply change the 2000-01-01 to whatever minimum date you want and change 12 * 50 to 12 * xxxx where xxxx is however many years you need

surrano did it already
he answered my question
the value returned are correct
only the space , i want no space before the month number , and less space before year number
that is all

but while i'm watching comments of other experts , here i would like to have your opinion
is my logic is the best one , or better to use 2 tables  , one for months and the others for years
the use of table date_cal , is :
i have trans_table contain field name : period_no
to start new period , for example year 2014 and month = 1
i update the record contain that values in date_cal setting field check_el to 1 and all others to zero
here i have one function name : curr_period return value of date_cal_name where check_el=1
which should be here in my example : '01 - 2014'
back to my table : trans_table , here for each transaction , i update period_no to be the value of the function curr_period
then when i would like to retireve any report , i use to set creteria for the transaction as same value of : curr_period

>>only the space , i want no space before the month number , and less space before year number that is all

Then just remove it from the string concatenation.

Change:  || ' - ' ||
To: || '-' ||

>>your opinion is my logic is the best one

I still don't understand what months and years belong in your table.

I'm also not following the curr_period, trans_table description and the actual requirement.  Maybe another Expert will.

Change:  || ' - ' ||
To: || '-' ||

i'm speaking about one space becomes left side like this
insteda of :
01 - 2014

 01 -  2014

note the difference between them

I still don't understand what months and years belong in your table.

I'm also not following the curr_period, trans_table description and the actual requirement.  Maybe another Expert will.

yes ok , i may not explaining well
forget about all
i will start from new
i have trans_table having those fields :
trans_ID
trans_amount
invoice_date

ok , now each set of transaction should be related of specific period
for example , after closing january , user cannot add , edit or delete any records
but when opening febrauray , he can do any thing till closing the transactions of february
note : invoice_date is not necessary same of required period
for example : in jan 2014 we may record invoice_date related to 2013
any new idea ?

>>note the difference between them

Make the spaces however you want.  It is just simple string manipulation.

>>yes ok , i may not explaining well

Even with the new information, I'm not understanding.

With the current design what purpose does date_cal have?

it just contain all periods names
every month we will set check mark to the current period
so , in any time , current period will be the returned value of records checked beside

So why can't you just add the check to see if the date in is in the 'current' period.  If so, it can be edited.  If not, it cannot be.

You know when periods start and end so you should be able to derive the current period with some sysdate math.

i'm worry if user post transaction_date out of range of the specific month
for example : if we close january 2014 , users will start posting transactions of the next month ,
posting date is not  exactly start at feb 01 , and not exactly ends on feb 28th
it will start only when user complete posting of jan , checking it , then  january transactions will close , and user is now ready to post transactions of feb
but he should post ony feb trans , he should not post mar or jan

So the date_cal table is supposed to hold 'closed periods'?

yes
look , just now i think , no need for date_cal
i should add one field in trans_table
will name it : trans_period date
when close transaction of january , this table will take default value of : 28-02-2014
then when closing feb , the default value of transaction will be : 31-03-2014
it is ok for me ,
but here , how clould the closing button will work
i mean , how to change the default value from month to month ?
do i need a function ? or parameter ? i don't know

this table will take default value of : 28-02-2014

sorry , it should be :

 this field will take default value of : 28-02-2014

>>i should add one field in trans_table will name it : trans_period date

From what you provided it doesn't appear that you care 'when' a record closes just if it has closed or not.

If all you care about is if the period is closed or not I still don't seen the need for it being a date.  A simple Y/N or 1/0 flag should work.

When the 'period' closes just update all rows where the transaction date is less than the current period and the flag column is null/0/N/whatever and set the flag column to a Y.

Maybe call the column: has_closed char(1)

A lot less space used and less coding in the app.

If all you care about is if the period is closed or not I still don't seen the need for it being a date.  A simple Y/N or 1/0 flag should work.

i already have flag field for closing status
but i'm concern also about the set of transactions of current financial period
i really need that field to refer to the financial period

any idea about how to use curr_period as global variable in the database
i will set current period value to it , as : 31-01-2014

>>but i'm concern also about the set of transactions of current financial period

I'm not following.  If the "flag field for closing status" isn't set then the records can be modified for the current period and the previous period?

The flag tells you the two possible states:  You can modify or you cannot modify.  I don't understand the need for what period it is.

>>any idea about how to use curr_period as global variable in the database

This exact question was asked recently.  Here is that thread and proposed solutions:
https://www.experts-exchange.com/questions/28333549/is-there-a-way-i-define-constant-globally-in-oracle.html

The flag tells you the two possible states:  You can modify or you cannot modify.  

this issue i solved already , i have no problem about it

I don't understand the need for what period it is.

it has many puposes , one of it , we have to reconsile the transaction monthly basis together with bank statement
second : our financial statements are monthly basis , every month each similiar set of transactions has to bee recorded and reported in one monthly summary
that table here in our example, handling one type of transactions, which should be reported
in monthly summary
so , in jabuary should submit that much of transactions , in february too , and so on
ok , now you may ask me why not use date field and report between date range ?
answer , this exactly what i'm going to do by adding this field , but the different here
i will fix the value of the date to avoid posting mistakes due to the huge amount of monthly records

If you say you need to track the current period then you may.  From the very little I understand of your requirements in the scope of this question, I'm just not convinced.

I really cannot learn your specific system and requirements well enough to say for sure.

I really cannot learn your specific system and requirements well enough to say for sure.

what i have to do to make more explanation
i really would like to close my issue

>>i really would like to close my issue

I thought you mentioned above that you had a solution that was working?

I was just trying to get around the use of another table.  You then mentioned a 'constant' might work for you and I provided a link that shows how to do that.

The only other thing I might be able to offer is avoiding even the use of the constant.

>>what i have to do to make more explanation

I really don't think you can.  To know for sure if removing the constant would work, I would need to be almost as familiar with your system as you are.  Unfortunately I don't believe this is possible.

i understood you
but i really would like to learn much here , not only solving
if you never mind , i would like to know the right , adding more knowledge to mine
so , please don't leave me till i know what is your logic here ,

I won't leave but there comes a time where a question cannot evolve any more.

The last thing I have to offer is below.  If it doesn't help then I'm out of ideas.

Most businesses run on a known business cycle.  At any given date, you automatically know what 'period' you are in.  You don't need to store a value to know this.

From what I understand about this question, all you really care about is not being able to modify any rows from a 'closed' period.  I don't see where the 'current' period matters.  All you care about is if any new data is in an 'open' period.

The flag in the table should tell you if a record is safe to be updated or deleted.  Then all you need to worry about is inserts.  All this might be able to be handled with a database trigger.

Now, if you don't close on a specific date each year, all this falls apart and you may need some hard-coded value but not for ALL periods, just a last_closed_period value.  Then the triggers would definitely work.

ok , i agree with you
but let me do more summary
i have 2 dates fields
one for invoice date
and the other is for payment date ( or transaction date )
if they filled probably , i done
but now all my concern is : my users always do mistakes , if they post one date correctly , they will post another one wrongly
so , i will fix the payment date , and user will not touch it
he will only concern about the invoice date
here may be setting default value of payment date as last day of the related month could work
to make it more flexible , i'm thinking to have combo box , contain all months , user select one value as a related month
i think this is best for me now , but here do i need table for the combo box ?

>>i think this is best for me now , but here do i need table for the combo box ?

This is implementation and seems to be out of scope of this question.

As I see this question you are just looking for a way to decide if a record can be modified based on if the period has closed out or not.

As I have mentioned several times:  I do not understand where a list of values, in a combo box or not, will help you with this.   If you cannot dynamically decide if the previous period has been closed then you should only need one value stored somewhere (a constant or in a table).  Then all you should need to do is check if the record being processed is in a closed period or not.

I'm not in the accounting field and likely will never be able to understand the requirement to the level you do.

at this point , i have to stop here and accept the answer of surrano because he answer my starting question
i will open another question regarding setting value of one field