	[x] Posted via EE Mobile
	Search, ask, and monitor your questions on the go with EE Mobile. Visit Experts Exchange from your mobile device and never be out of touch again.

Question

	[x] Attachment Details

[x]

The Solution Rating System

With so many solutions, how can you tell which solutions are most likely to help you and which ones are not? To provide you with a tool to use, we rate our solutions based on various elements that most accurately determine if a solution is a quality solution. To explain what factors affect the solution rating, here are the elements we take into consideration when formulating our solution rating.

The Grade of the Solution
The Zone Rank of the Expert Providing the Solution
The Number of Author and Expert Comments
The Number of Experts Contributing
The Feedback of the Community

Your Input Matters
Because of the way the system is set up, the most important variable in this equation is you. As a member of Experts Exchange, you are able to cast your vote on the quality of the solutions in regard to how complete, accurate, helpful and easy to understand each solution is. When you provide your feedback, each rating is adjusted accordingly. So, if you see a solution that has a poor rating that you think is a good solution, let us know by rating it. As you do, the rating will be adjusted and will become more accurate for other members of our site.

If you have any suggestions that you would like to make for our rating system, please ask a question in the Suggestions Zone of Community Support.

Thank you!

9.0

Sql and pl/sql create, insert, drop and merge tables 3 tables. My insert statement will not work

Asked by shizue in SQL Query Syntax, PL / SQL

I have written simple sql code before and have created, dropped and tables. But I am unfamiliar with the way this code is written. I created 3 tables (temp_fis, temp_sa, temp_merged). But I was unable to use the insert statement listed below. I dont understand the first select statement. I think its asking me to use the oracle internal id and give it an alisa name id then I'm not sure what a*. means. I understand the from clause stating retreive these rows and rename the a ??? I think. Can you help me figure out how to run this code.

Get your answer NOW

         
           
             1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
99:
100:
101:
102:
103:
104:
105:
106:
107:
108:
109:
110:
111:
112:
113:
114:
115:
116:
117:
118:
119:
120:
121:
122:
123:
124:
125:
126:
127:
128:
129:
130:
131:
132:
133:
134:
135:
136:
137:
138:
139:
140:
141:
142:
143:
144:
145:
146:
147:
148:
149:
150:
151:
152:
153:
154:
155:
156:
157:
158:

           
           
             )--drop table temp_fis
--/
truncate table temp_fis
/
--create table temp_fis as
insert into temp_fis
(select rownum id, a.* from
(
SELECT aptc.description fis_payment_type, sp.service_provider_number fis_provider_id,
  TRIM(UPPER(sp.service_provider_name)) fis_provider_name, apl.person_id fis_person_id,
  TRIM(UPPER(pn.first_name)) fis_person_fname, TRIM(UPPER(pn.last_name)) fis_person_lname,
  pix.person_id sa_person_id, TRIM(UPPER(pn.first_name))||' '||TRIM(UPPER(pn.last_name)) fis_person_name,
  SUM(apl.line_amount) fis_amount, apl.add_date fis_rec_cre_dt
FROM ap_pl_line@fprd apl, person_names@factprd pn, ap_person_id_xref@cfsprd pix,
 service_providers@fprd sp, ap_pl_type_ctl@fprd aptc
WHERE apl.person_id = pn.person_id
  AND pn.primary_name_flag = 'Y'
  AND apl.person_id = pix.fis_person_id(+)
  AND apl.service_provider_id = sp.service_provider_id
  AND apl.pl_type_id = aptc.payroll_type_id
  AND apl.pl_type_id = 1   /* 1 for FCM  */
  AND apl.pl_expense_id IS NULL
  AND TRUNC(apl.from_date) >= TO_DATE('&begin_date', 'MM/DD/YYYY')
  AND TRUNC(apl.TO_DATE) <= TO_DATE('&end_date', 'MM/DD/YYYY')
GROUP BY aptc.description, sp.service_provider_number, TRIM(UPPER(sp.service_provider_name)),
  apl.person_id, pix.person_id, TRIM(UPPER(pn.first_name)), TRIM(UPPER(pn.last_name)),
  TRIM(UPPER(pn.first_name))||' '||TRIM(UPPER(pn.last_name)), apl.add_date
ORDER BY aptc.description, TRIM(UPPER(pn.first_name))||' '||TRIM(UPPER(pn.last_name))
) a)
/
commit
/
/*
drop table temp_merged
/
create table temp_merged as
(select
    f.id,
    f.fis_payment_type,
    f.fis_provider_id,
    f.fis_provider_name,
    f.fis_person_id,
    f.fis_person_fname,
    f.fis_person_lname,
    f.sac_person_id,
    f.fis_person_name,
    f.fis_amount,
    f.fis_rec_cre_dt,
    s.id s_id,
    s.sa_payment_type,
    s.sa_provider_id,
    s.sa_provider_name,
    s.sa_person_fname,
    s.sa_person_lname,
    s.sa_person_id s_sa_person_id,
    s.sa_person_name,
    s.sa_amount,
    s.sa_rec_cre_dt
from temp_fis_fcm f, temp_sa_fcm s
where f.id = s.id
and rownum < 1)
/
*/
truncate table temp_merged
/
insert into temp_merged
(select
    id,
    fis_payment_type,
    fis_provider_id,
    fis_provider_name,
    fis_person_id,
    fis_person_fname,
    fis_person_lname,
    sa_person_id,
    fis_person_name,
    fis_amount,
    fis_rec_cre_dt,
    null s_id,
    null sa_payment_type,
    null sa_provider_id,
    null sa_provider_name,
    null sa_person_fname,
    null sa_person_lname,
    null s_sa_person_id,
    null sa_person_name,
    null sa_amount,
    null sa_rec_cre_dt
 from temp_fis)
/
commit
/
declare
v_person_id number;
v_lname varchar2(35);
v_fname varchar2(35);
v_amount number;
v_exists number;
v_processed boolean;
cursor get_m_pmt is
select * from temp_merged
--where fis_person_id = 0102001090507007
order by sa_person_id, fis_amount desc;
cursor get_s_pmt (p_person_id number, p_lname varchar2, p_fname varchar2, p_amount number) is
select * from temp_sa
where (sac_person_id = p_person_id
       or (sa_person_lname = p_lname and sac_person_fname = p_fname))
  and (sa_amount = nvl(p_amount,sa_amount));
s get_s_pmt%rowtype;
begin
  for m in get_m_pmt loop
    v_processed := false;
    for s in get_s_pmt (m.sa_person_id,m.fis_person_lname,m.fis_person_fname,m.fis_amount) loop
      select count(1) into v_exists from temp_merged t where t.s_id = s.id;
      if v_exists = 0 then
        v_processed := true;
--        dbms_output.put_line('upd 1, id: '||m.id);
        update temp_merged set
          s_id = s.id,
          sa_payment_type = s.sa_payment_type,
          sa_provider_id = s.sa_provider_id,
          sa_provider_name = s.sa_provider_name,
          sa_person_fname = s.sa_person_fname,
          sa_person_lname = s.sa_person_lname,
          s_sa_person_id = s.sa_person_id,
          sa_person_name = s.sa_person_name,
          sa_amount = s.sa_amount,
          sa_rec_cre_dt = s.sa_rec_cre_dt
        where id = m.id;
        exit;
      end if;
    end loop;
    if not v_processed then
      for s in get_s_pmt (m.sa_person_id,m.fis_person_lname,m.fis_person_fname,null) loop
        select count(1) into v_exists from temp_merged t where t.s_id = s.id;
        if v_exists = 0 then
--          dbms_output.put_line('upd 2, id: '||m.id);
          update temp_merged set
          s_id = s.id,
          sa_payment_type = s.sa_payment_type,
          sa_provider_id = s.sa_provider_id,
          sa_provider_name = s.sa_provider_name,
          sa_person_fname = s.sa_person_fname,
          sa_person_lname = s.sa_person_lname,
          s_sa_person_id = s.sa_person_id,
          sa_person_name = s.sa_person_name,
          sa_amount = s.sac_amount,
          sa_rec_cre_dt = s.sa_rec_cre_dt
          where id = m.id;
          exit;
        end if;
      end loop;
    end if;
    if mod(m.id,100) = 0 then commit; end if;
  end loop;
  commit;
end;
/

           
         

20091111-EE-VQP-89 - Hierarchy / EE_QW_3_20080625

Hall of Fame

1. angelIII987,351
2. aneeshatti...751,342
3. matthews...575,487
4. rrjegan17410,769
5. capricorn1406,214
6. mwvisa1393,008
7. ralmada336,541
8. Sharath_...326,005
9. BrandonG...272,108
10. reb73229,974
11. chapmandew221,436
12. peter57r201,096
13. mark_wills177,032
14. boag2000158,848
15. pcelba155,279
16. sdstuber140,116
17. TextReport134,666
18. acperkins132,452
19. DanielWilson126,383
20. DatabaseMX122,464
21. pivar119,972
22. mrjoltcola116,984
23. LSMCons...114,570
24. RiteshShah114,551
25. lwadwell114,353

1. angelIII3,014,791
2. capricorn11,252,886
3. matthews...1,240,223
4. aneeshatti...1,162,945
5. mwvisa1828,426
6. chapmandew710,108
7. mark_wills694,816
8. BrandonG...557,946
9. peter57r494,820
10. ee_rlee460,453
11. rrjegan17410,769
12. sdstuber398,017
13. DatabaseMX392,015
14. Sharath_...365,805
15. dqmq346,705
16. ralmada338,541
17. GRayL330,160
18. DanielWilson324,456
19. boag2000320,161
20. harfang298,054
21. LSMCons...279,439
22. jimhorn238,445
23. reb73229,974
24. acperkins227,845
25. imitchie220,374

Sql and pl/sql create, insert, drop and merge tables 3 tables. My insert statement will not work

Asked by shizue in SQL Query Syntax, PL / SQL

About this solution