Solved

insert records using parameters

Posted on 2014-01-22
35
344 Views
Last Modified: 2014-01-26
i have one table name : date_cal
year_no   number
month_no   number
date_cal_name  varchar2
check_el  number(1)
i want to write a procedure to fill data of all months & years to it
filed date_cal  will save year & month like this
01 - 2015  where year = 2015 and month= 01
check_el will be 0 always

i started to use the below code

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

Open in new window


but it gives me an error that missing select keyword
0
Comment
Question by:NiceMan331
  • 15
  • 12
  • 3
  • +4
35 Comments
 
LVL 13

Expert Comment

by:Alexander Eßer [Alex140181]
ID: 39799557
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;
                                  

Open in new window


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

Author Comment

by:NiceMan331
ID: 39799570
ok
do you have any other solution
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 39799624
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; 

Open in new window

0
 
LVL 8

Accepted Solution

by:
Surrano earned 500 total points
ID: 39799675
This fills the table from Jan 1 AD till Dec 2015 AD. Months are padded to 2 digits and years to 4 digits in date_cal_name field.

declare
  y number(4);
  m number(2);
begin
  for y in 1 .. 2015 loop
    for m in 1 .. 12 loop
      insert into date_cal (year_no, month_no, date_cal_name, check_el)
      values (y, m, to_char(m,'00')||' - '||to_char(y,'0000'), 0);
    end loop;
    commit;
  end loop;
end;
/

Open in new window

0
 

Author Comment

by:NiceMan331
ID: 39799731
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39799761
What month and year values are you wanting to store in the table?
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39799819
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;
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39799851
>>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.
0
 
LVL 8

Expert Comment

by:Surrano
ID: 39799875
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);
...

Open 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.
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39799940
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.
0
 
LVL 31

Expert Comment

by:awking00
ID: 39800643
>>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).
0
 
LVL 73

Expert Comment

by:sdstuber
ID: 39800690
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
0
 

Author Comment

by:NiceMan331
ID: 39800971
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39801017
>>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.
0
 

Author Comment

by:NiceMan331
ID: 39801097
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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39801113
>>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?
0
 

Author Comment

by:NiceMan331
ID: 39801159
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
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39801179
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.
0
 

Author Comment

by:NiceMan331
ID: 39801216
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39801237
So the date_cal table is supposed to hold 'closed periods'?
0
 

Author Comment

by:NiceMan331
ID: 39801286
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
0
 

Author Comment

by:NiceMan331
ID: 39801290
this table will take default value of : 28-02-2014

sorry , it should be :

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

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39801426
>>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.
0
 

Author Comment

by:NiceMan331
ID: 39802108
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
0
 

Author Comment

by:NiceMan331
ID: 39804341
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39804614
>>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:
http://www.experts-exchange.com/Database/Oracle/Q_28333549.html
0
 

Author Comment

by:NiceMan331
ID: 39807561
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39807589
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.
0
 

Author Comment

by:NiceMan331
ID: 39807627
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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39807733
>>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.
0
 

Author Comment

by:NiceMan331
ID: 39808264
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 ,
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39809394
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.
0
 

Author Comment

by:NiceMan331
ID: 39809740
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 ?
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39810694
>>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.
0
 

Author Comment

by:NiceMan331
ID: 39811188
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
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows information on the Oracle Data Dictionary, starting with the Oracle documentation, explaining the different types of Data Dictionary views available by group and permissions as well as giving examples on how to retrieve data from th…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now