Home » SQL & PL/SQL » SQL & PL/SQL » insert into a table using view (oracle 10g)
insert into a table using view [message #405970] Mon, 01 June 2009 03:59 Go to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
select * from emrscreenimmunlkup where group_id=0;
SCREEN_IMMUN_ID        RECORD_TYPE GENDER                 DESCRIPTION                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          STARTING_AGE_OPERATOR                                                                                                                                                                                                                                           GROUP_ID               MASTER_REFERENCE_ID    
---------------------- ----------- ---------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------- ---------------------- 
1                      S           1                      Prostrate Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     =                                                                                                                                                                                                                                                               0                                             
2                      S           1                      Colorectal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           1                                                                                                                                                                                                                                                               0                                             
3                      S           1                      Heart Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
4                      S           1                      Diabetes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             =                                                                                                                                                                                                                                                               0                                             
5                      S           1                      Ear Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           1                                                                                                                                                                                                                                                               0                                             
6                      S           1                      Eye Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
7                      I           1                      Tetanus Diphtheria Booster                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
8                      I           1                      Pneumococcal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
9                      I           1                      Influenza                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            =                                                                                                                                                                                                                                                               0                                             
10                     S           2                      Thyroid                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                              =                                                                                                                                                                                                                                                               0                                             
11                     S           2                      Heart Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
12                     S           2                      Bone Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          =                                                                                                                                                                                                                                                               0                                             
13                     S           2                      Diabetes                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                             =                                                                                                                                                                                                                                                               0                                             
14                     S           2                      Reproductive Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  =                                                                                                                                                                                                                                                               0                                             
15                     S           2                      Reproductive Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  =                                                                                                                                                                                                                                                               0                                             
16                     S           2                      Reproductive Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  =                                                                                                                                                                                                                                                               0                                             
17                     S           2                      Reproductive Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  =                                                                                                                                                                                                                                                               0                                             
18                     S           2                      Reproductive Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  >                                                                                                                                                                                                                                                               0                                             
19                     S           2                      Ear Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
20                     S           2                      Eye Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
21                     I           2                      Tetanus Diphtheria Booster                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
22                     I           2                      Pneumococcal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
23                     I           2                      Influenza                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            =                                                                                                                                                                                                                                                               0                                             
24                     I           2                      HPV                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  =                                                                                                                                                                                                                                                               0                                             
25                     I           2                      Meningococcal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
26                     I           0                      Hepatitis B 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 =                                                                                                                                                                                                                                                               0                                             
27                     I           0                      Hepatitis B 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 =                                                                                                                                                                                                                                                               0                                             
28                     I           0                      Hepatitis B 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 =                                                                                                                                                                                                                                                               0                                             
29                     I           0                      Hepatitis B 4th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 =                                                                                                                                                                                                                                                               0                                             
30                     I           0                      Rotavirus 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   =                                                                                                                                                                                                                                                               0                                             
31                     I           0                      Rotavirus 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   =                                                                                                                                                                                                                                                               0                                             
32                     I           0                      Haemophilus Influenzae Type B 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                               =                                                                                                                                                                                                                                                               0                                             
33                     I           0                      Pneumococcal 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                =                                                                                                                                                                                                                                                               0                                             
34                     I           0                      Influenza                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            =                                                                                                                                                                                                                                                               0                                             
35                     I           0                      MMR 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
36                     I           0                      MMR 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
37                     I           0                      Varicella Vaccine 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
38                     I           0                      Varicella Vaccine 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           =                                                                                                                                                                                                                                                               0                                             
39                     I           0                      Hepatitis A 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 =                                                                                                                                                                                                                                                               0                                             
40                     I           0                      Hepatitis A 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 >                                                                                                                                                                                                                                                               0                                             
41                     I           0                      Meningococcal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
42                     I           0                      Tdap                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 =                                                                                                                                                                                                                                                               0                                             
43                     I           0                      HPV 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
44                     I           0                      HPV 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
45                     I           0                      HPV 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
46                     I           0                      HPV 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
47                     I           0                      HPV 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
48                     I           0                      HPV 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
49                     I           0                      DTaP 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
50                     I           0                      DTaP 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
51                     I           0                      DTaP 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
52                     I           0                      DTaP 4th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
53                     I           0                      DTaP 5th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
54                     I           0                      Haemophilus Influenzae Type B 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                               =                                                                                                                                                                                                                                                               0                                             
55                     I           0                      Haemophilus Influenzae Type B 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                               =                                                                                                                                                                                                                                                               0                                             
56                     I           0                      Haemophilus Influenzae Type B 4th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                               =                                                                                                                                                                                                                                                               0                                             
57                     I           0                      Haemophilus Influenzae Type B 5th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                               =                                                                                                                                                                                                                                                               0                                             
58                     I           0                      Pneumococcal 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                =                                                                                                                                                                                                                                                               0                                             
59                     I           0                      Pneumococcal 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                =                                                                                                                                                                                                                                                               0                                             
60                     I           0                      Pneumococcal 4th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                =                                                                                                                                                                                                                                                               0                                             
61                     I           0                      Pneumococcal 5th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                =                                                                                                                                                                                                                                                               0                                             
62                     I           0                      Meningococcal                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
63                     S           2                      Breast Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
64                     S           2                      Breast Health                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        =                                                                                                                                                                                                                                                               0                                             
65                     I           0                      IPV 1st Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         1                                                                                                                                                                                                                                                               0                                             
66                     I           0                      IPV 2nd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         1                                                                                                                                                                                                                                                               0                                             
67                     I           0                      IPV 3rd Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
68                     I           0                      IPV 4th Dose                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         =                                                                                                                                                                                                                                                               0                                             
68 rows selected

NOW I HAVE CREATED A VIEW


  CREATE OR REPLACE FORCE VIEW "ABC"."V1" ("SCREEN_IMMUN_ID", "GROUP_ID", "MASTER_REFERENCE_ID") AS 
  with emrscreenimmunlkup as (select level+1500  screen_immun_id
                    ,0 group_id
                   ,to_number(null) master_reference_id 
             from dual connect by level <= 68)
select (screen_immun_id -1500)  screen_immun_id
      ,group_id
      ,master_reference_id
from   emrscreenimmunlkup
union all
select (s1.screen_immun_id - 1500) + (s2.screen_immun_id - 1500)*68 
      , s2.screen_immun_id
       ,(s1.screen_immun_id - 1500)
from    emrscreenimmunlkup s1, emrscreenimmunlkup s2
order by 1;

SELECT * FROM V1;

SCREEN_IMMUN_ID        GROUP_ID               MASTER_REFERENCE_ID    
---------------------- ---------------------- ---------------------- 
1                      0                                             
2                      0                                             
3                      0                                             
4                      0                                             
5                      0                                             
6                      0                                             
7                      0                                             
8                      0                                             
9                      0                                             
10                     0                                             
11                     0                                             
12                     0                                             
13                     0                                             
14                     0                                             
15                     0                                             
16                     0                                             
17                     0                                             
18                     0                                             
19                     0                                             
20                     0                                             
21                     0                                             
22                     0                                             
23                     0                                             
24                     0                                             
25                     0                                             
26                     0                                             
27                     0                                             
28                     0                                             
29                     0                                             
30                     0                                             
31                     0                                             
32                     0                                             
33                     0                                             
34                     0                                             
35                     0                                             
36                     0                                             
37                     0                                             
38                     0                                             
39                     0                                             
40                     0                                             
41                     0                                             
42                     0                                             
43                     0                                             
44                     0                                             
45                     0                                             
46                     0                                             
47                     0                                             
48                     0                                             
49                     0                                             
50                     0                                             
51                     0                                             
52                     0                                             
53                     0                                             
54                     0                                             
55                     0                                             
56                     0                                             
57                     0                                             
58                     0                                             
59                     0                                             
60                     0                                             
61                     0                                             
62                     0                                             
63                     0                                             
64                     0                                             
65                     0                                             
66                     0                                             
67                     0                                             
68                     0                                             
69                     1501                   1                      
70                     1501                   2                      
71                     1501                   3                      
72                     1501                   4                      
73                     1501                   5                      
74                     1501                   6                      
75                     1501                   7                      
76                     1501                   8                      
77                     1501                   9                      
78                     1501                   10                     
79                     1501                   11                     
80                     1501                   12                     
81                     1501                   13                     
82                     1501                   14                     
83                     1501                   15                     
84                     1501                   16                     
85                     1501                   17                     
86                     1501                   18                     
87                     1501                   19                     
88                     1501                   20                     
89                     1501                   21                     
90                     1501                   22                     
91                     1501                   23                     
92                     1501                   24                     
93                     1501                   25                     
94                     1501                   26                     
95                     1501                   27                     
96                     1501                   28                     
97                     1501                   29                     
98                     1501                   30                     
99                     1501                   31                     
100                    1501                   32                     
101                    1501                   33                     
102                    1501                   34                     
103                    1501                   35                     
104                    1501                   36                     
105                    1501                   37                     
106                    1501                   38                     
107                    1501                   39                     
108                    1501                   40                     
109                    1501                   41                     
110                    1501                   42                     
111                    1501                   43                     
112                    1501                   44                     
113                    1501                   45                     
114                    1501                   46                     
115                    1501                   47                     
116                    1501                   48                     
117                    1501                   49                     
118                    1501                   50                     

NOW HOW CAN I INSERT THIS VALUES INTO THE TABLE EMRSCREENIMMUNLKUP BASE ON V1

INSERT INTO EMRSCREENIMMUNLKUP(

       

Re: insert into a table using view [message #405971 is a reply to message #405970] Mon, 01 June 2009 04:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
You table has a lot more columns that your view provides.

If you insert data from that view, then you're going to need to get the extra columns from somewhere.
You can either select these columns from the S1 or S2 tables in the view, or join another table to the view.

If you don't want to overwrite the data currently in the table, then I think you want to drop the first half of that view - you already have rows with a screen_immun_id 1-68
Re: insert into a table using view [message #405973 is a reply to message #405971] Mon, 01 June 2009 04:20 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
yeah want intially all my records which were the existing ones.

and later on i want to insert from the view into the table.

but using this i am getting negative values.

Re: insert into a table using view [message #405974 is a reply to message #405973] Mon, 01 June 2009 04:24 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on your previous threads, I know I'm going to regret this.

What do you mean 'geting negative values'

The view you've posted isn't generating negative values, and won't as long as there are no screen_immun_ids in the table below 1500.

Remember - that query I created for you was the solution to a very specific and limited question that I said at the time almost certainly wasn't the solution you were looking for.

If it turns out that you weren't telling us everything (which looks to be the case) don't be suprised if it doesn't work as planned.
Re: insert into a table using view [message #405975 is a reply to message #405974] Mon, 01 June 2009 04:27 Go to previous messageGo to next message
rajasekhar857
Messages: 500
Registered: December 2008
Senior Member
create or replace view v1 as with emrscreenimmunlkup as (select level+1500  screen_immum_id,
RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,STARTING_AGE_VALUE,
                                STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,AGE_END_LIMIT,
				AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,REPEAT_CONDITION,0 GROUP_ID,
				STATUS,CDC_CODE,SCREEN_COMMENTS,to_number(null) MASTER_REFERENCE_ID
				from dual connect by level <= 68)

select (screen_immun_id -1500)  screen_immun_id,RECORD_TYPE,GENDER,SUB_GENDER,DESCRIPTION,STARTING_AGE_OPERATOR,STARTING_AGE_VALUE,
                                STARTING_AGE_UNITS,FREQUENCY,STARTING_AGE_LIMIT_RANGE,STARTING_AGE_LIMIT_UNITS,AGE_END_LIMIT,
				AGE_END_LIMIT_UNITS,REPEAT,REPEAT_AGE_VALUE,REPEAT_AGE_UNITS,FREQUENCY_LIMIT,REPEAT_CONDITION,GROUP_ID,
				STATUS,CDC_CODE,SCREEN_COMMENTS,MASTER_REFERENCE_ID
        FROM emrscreenimmunlkup
union all
select (s1.screen_immun_id - 1500) + (s2.screen_immun_id - 1500)*68 ,
       s1.RECORD_TYPE,s1.GENDER,s1.SUB_GENDER,s1.DESCRIPTION,s1.STARTING_AGE_OPERATOR,s1.STARTING_AGE_VALUE,
                                s1.STARTING_AGE_UNITS,s1.FREQUENCY,s1.STARTING_AGE_LIMIT_RANGE,s1.STARTING_AGE_LIMIT_UNITS,s1.AGE_END_LIMIT,
				s1.AGE_END_LIMIT_UNITS,s1.REPEAT,s1.REPEAT_AGE_VALUE,s1.REPEAT_AGE_UNITS,s1.FREQUENCY_LIMIT,s1.REPEAT_CONDITION,
        s2.screen_immun_id,
				s1.STATUS,s1.CDC_CODE,s1.SCREEN_COMMENTS,s1.screen_immun_id - 1500 
       from    emrscreenimmunlkup s1, emrscreenimmunlkup s2 order by 1;



can i write like this

but getting exception too as

Error at Command Line:5 Column:20
Error report:
SQL Error: ORA-00904: "SCREEN_COMMENTS": invalid identifier
00904. 00000 - "%s: invalid identifier"
Re: insert into a table using view [message #405983 is a reply to message #405975] Mon, 01 June 2009 06:19 Go to previous messageGo to next message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
Quote:
"SCREEN_COMMENTS": invalid identifier


desc dual

Name                           Null     Type
------------------------------ -------- -----
DUMMY                                   VARCHAR2(1)

Re: insert into a table using view [message #405988 is a reply to message #405975] Mon, 01 June 2009 06:26 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Based on the Select * query you showed us at the top of this thread, there is no SCREEN_COMMENTS column in the table emrscreenimmunlkup
Re: insert into a table using view [message #405989 is a reply to message #405988] Mon, 01 June 2009 06:35 Go to previous message
pablolee
Messages: 2834
Registered: May 2007
Location: Scotland
Senior Member
JRowbottom wrote on Mon, 01 June 2009 12:26
Based on the Select * query you showed us at the top of this thread, there is no SCREEN_COMMENTS column in the table emrscreenimmunlkup

Nor in the DUAL table, which is where he is selecting it from in line 5
Previous Topic: How to find the trace user activity like logs in pl/sql ?
Next Topic: Where can subqueries be used?
Goto Forum:
  


Current Time: Sun Dec 04 20:29:53 CST 2016

Total time taken to generate the page: 0.06739 seconds