Home » SQL & PL/SQL » SQL & PL/SQL » Finding the source of the Company
| Finding the source of the Company [message #648643] |
Sat, 27 February 2016 16:22  |
 |
hypesslearner
Messages: 15
Registered: July 2015
|
Junior Member |
|
|
Hello all,
I have one source table and two mapping tables from which I need to derive the columns "Original_Requester",
"Know_How" in Output_Table based on the below functionality
For each Work_Type Column4 in Source_Table
1.Value in the first column "Project_Given_By" in every record to be checked against
value in the second column "Project_done_By" of other records after I get the
corresponding mapping from Company_Secure_Mapping_Table.
2.If Value is found then I need to make Original_Requester of Column4 in Output_Table as
Project_Given_By from that record where matching is done .
Also I need to make Know_How of Column5 as Expert
3.If Value is not found then it needs to go to Department_Mapping_Table and get the
value in the Client column based on the value in the DEPT Column3, and make the Original_Requester of Column4 in
Output_Table as corresponding value in Client Column.
Then I need to check whether this Original_Requestor is same as Project_Given_By:
If same then make Know_How of Column5 as Fresher ,
If not same then make Know_How of Column5 as Expert.
Checking record for comparision to be done always from the top of the Source_Table and should exclude the record which is in execution at that instance.
Only records whose Work_Type is same should be used for comparision
Source_Table,Department_Mapping_Table,Company_Secure_Mapping_Table
keeps on growing with new values and I should not hardcode any values
Each record from Source_Table to be duplicated since I am converting two columns (Effort in hours,Effort in dollars) into two rows
I got the below code however I could not proceed further . please help.
[u]Source_Table[/u]
row0 Column1 Column2 Column3 Column4 Column5 Column6
row0 Project_Given_By Project_done_By DEPT Work_Type Effort in hours Effort in dollars
row1 CompanyA SECURE-A RnD Testing 10 100
row2 CompanyA SECURE-X RnD Testing 20 200
row3 CompanyB SECURE-X RnD Testing 30 300
row4 CompanyA SECURE-A RnD Integration 10 100
row5 CompanyB SECURE-A RnD Testing 40 400
row6 CompanyA SECURE-X RnD Integration 20 200
row7 CompanyC SECURE-C FnL Testing 50 500
[u]Department_Mapping_Table [/u]
DEPT Client
RnD InHouse
FnL CompanyC
[u]Company_Secure_Mapping_Table [/u]
SECURE-A CompanyA
SECURE-B CompanyB
SECURE-C CompanyC
SECURE-X CONTRACTOR
[u]Output_Table[/u]
row0 Column1 Column2 Column3 Column4 Column5 Column6 Column7
row0 Project_Given_By Project_done_By Work_Type Original_Requester Know_How EFFORT_TYPE NUMBER
row1 CompanyA SECURE-A Testing CompanyB Expert Effort in hours 10
row2 CompanyA SECURE-A Testing CompanyB Expert Effort in dollars 100
row3 CompanyA SECURE-X Testing CompanyA Expert Effort in hours 20
row4 CompanyA SECURE-X Testing CompanyA Expert Effort in dollars 200
row5 CompanyB SECURE-X Testing InHouse Expert Effort in hours 30
row6 CompanyB SECURE-X Testing InHouse Expert Effort in dollars 300
row7 CompanyA SECURE-A Integration InHouse Expert Effort in hours 10
row8 CompanyA SECURE-A Integration InHouse Expert Effort in dollars 100
row9 CompanyB SECURE-A Testing InHouse Expert Effort in hours 40
row10 CompanyB SECURE-A Testing InHouse Expert Effort in dollars 400
row11 CompanyA SECURE-X Integration CompanyA Expert Effort in hours 20
row12 CompanyA SECURE-X Integration CompanyA Expert Effort in dollars 200
row13 CompanyC SECURE-C Testing CompanyC Fresher Effort in hours 50
row14 CompanyC SECURE-C Testing CompanyC Fresher Effort in dollars 500
[u]Code that I have tried[/u]
select *
from (select x.Project_Given_By,x.Project_done_By,x.Work_Type,
Original_Requester,
x.Effort_in_hours,x.Effort_in_dollars
from source_table x
left outer join
source_table y
on x.Work_Type = y.Work_Type
and x.Project_Given_By = y.Project_done_By
left outer join
Department_Mapping_Table l
on x.dept = l.dept
)
unpivot (value for Effort_Type in (Effort_in_hours as 'Effort in hours',Effort_in_dollars as 'Effort in dollars'))
[Updated on: Sat, 27 February 2016 16:23] Report message to a moderator |
|
|
|
|
|
| Re: Finding the source of the Company [message #648646 is a reply to message #648644] |
Sun, 28 February 2016 02:57  |
|
hypesslearner
Messages: 15
Registered: July 2015
|
Junior Member |
|
|
Hi,
Please find create table and insert statements for source table and mapping tables
"CREATE TABLE ""Department_Mapping_Table""
(
""DEPT"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Client"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
)"
Insert into Department_Mapping_Table (DEPT, Client) values ('RnD', 'InHouse');
Insert into Department_Mapping_Table (DEPT, Client) values ('FnL', 'CompanyC');
"CREATE TABLE ""Company_Secure_Mapping_Table""
(
""SECUREID"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""CompanyAlias"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
)"
Insert into Company_Secure_Mapping_Table (SECUREID, CompanyAlias) values ('SECURE-A', 'CompanyA');
Insert into Company_Secure_Mapping_Table (SECUREID, CompanyAlias) values ('SECURE-B', 'CompanyB');
Insert into Company_Secure_Mapping_Table (SECUREID, CompanyAlias) values ('SECURE-C', 'CompanyC');
Insert into Company_Secure_Mapping_Table (SECUREID, CompanyAlias) values ('SECURE-Z', 'CONTRACTOR');
"CREATE TABLE ""Source_Table""
(
""Project_Given_By"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Project_done_By"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""DEPT"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Work_Type"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Effort in hours"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Effort in dollars"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
)"
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyA','SECURE-A','RnD','Testing',10,100);
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyA','SECURE-X','RnD','Testing',20,200);
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyB','SECURE-X','RnD','Testing',30,300);
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyA','SECURE-A','RnD','Integration',10,100);
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyB','SECURE-A','RnD','Testing',40,400);
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyA','SECURE-X','RnD','Integration',20,200);
Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort in hours,Effort in dollars) values ('CompanyC','SECURE-C','FnL','Testing',50,500);
"CREATE TABLE ""Output_Table""
(
""Project_Given_By"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Project_done_By"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Work_Type"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Original_Requester"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""Know_How"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""EFFORT_TYPE"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
""NUMBER"" VARCHAR2(100 BYTE) NOT NULL ENABLE,
)"
[Updated on: Sun, 28 February 2016 04:03] Report message to a moderator |
|
|
|
|
|
|
|
| Re: Finding the source of the Company [message #648653 is a reply to message #648652] |
Sun, 28 February 2016 09:08 |
|
hypesslearner
Messages: 15
Registered: July 2015
|
Junior Member |
|
|
Hi All ,
I have modified the code
CREATE TABLE "Department_Mapping_Table"
(
"DEPT" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Client" VARCHAR2(100 BYTE) NOT NULL ENABLE
)
Insert into "Department_Mapping_Table" (DEPT,"Client") values ('RnD', 'InHouse');
Insert into "Department_Mapping_Table" (DEPT,"Client") values ('FnL', 'CompanyC');
CREATE TABLE "Company_Secure_Mapping_Table"
(
"SECUREID" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"CompanyAlias" VARCHAR2(100 BYTE) NOT NULL ENABLE
)
Insert into "Company_Secure_Mapping_Table" (SECUREID, "CompanyAlias") values ('SECURE-A', 'CompanyA');
Insert into "Company_Secure_Mapping_Table" (SECUREID, "CompanyAlias") values ('SECURE-B', 'CompanyB');
Insert into "Company_Secure_Mapping_Table" (SECUREID, "CompanyAlias") values ('SECURE-C', 'CompanyC');
Insert into "Company_Secure_Mapping_Table" (SECUREID, "CompanyAlias") values ('SECURE-Z', 'CONTRACTOR');
CREATE TABLE "Source_Table"
(
"Project_Given_By" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Project_done_By" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"DEPT" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Work_Type" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Effort in hours" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Effort in dollars" VARCHAR2(100 BYTE) NOT NULL ENABLE
)
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyA','SECURE-A','RnD','Testing',10,100);
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyA','SECURE-X','RnD','Testing',20,200);
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyB','SECURE-X','RnD','Testing',30,300);
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyA','SECURE-A','RnD','Integration',10,100);
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyB','SECURE-A','RnD','Testing',40,400);
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyA','SECURE-X','RnD','Integration',20,200);
Insert into "Source_Table" ("Project_Given_By","Project_done_By","DEPT","Work_Type","Effort in hours","Effort in dollars") values ('CompanyC','SECURE-C','FnL','Testing',50,500);
CREATE TABLE "Output_Table"
(
"Project_Given_By" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Project_done_By" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Work_Type" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Original_Requester" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"Know_How" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"EFFORT_TYPE" VARCHAR2(100 BYTE) NOT NULL ENABLE,
"NUMBER" VARCHAR2(100 BYTE) NOT NULL ENABLE
)
|
|
|
|
|
|
|
|
| Re: Finding the source of the Company [message #648656 is a reply to message #648655] |
Sun, 28 February 2016 12:03 |
|
hypesslearner
Messages: 15
Registered: July 2015
|
Junior Member |
|
|
Hi ,
I have removed only one set of double quotes , When I saw the create table statements , double quotes are available , so I thought they are mandatory.
When i remove the double quotes from the column names i got SQL Error: ORA-00902: invalid datatype error .
You are asking about - how you select the 2nd (project done by) and 6th (effort type) columns from the last table (output table)
Output table is the one which I need to populate using mapping and input tables .I am trying to do a self join on the same table , however below I am doing on Project_Given_By and Project_Done_By, which doesnt work . How do I join this with Company_Secure_Mapping_Table
select *
from (select x.Project_Given_By,x.Project_done_By,x.Work_Type,
Original_Requester,
x.Effort_in_hours,x.Effort_in_dollars
from source_table x
left outer join
source_table y
on x.Work_Type = y.Work_Type
and x.Project_Given_By = y.Project_done_By
left outer join
Department_Mapping_Table l
on x.dept = l.dept
)
unpivot (value for Effort_Type in (Effort_in_hours as 'Effort in hours',Effort_in_dollars as 'Effort in dollars'))
Thanks
|
|
|
|
|
|
| Re: Finding the source of the Company [message #648658 is a reply to message #648656] |
Sun, 28 February 2016 15:50 |
 |
Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA
|
Senior Member |
|
|
Clarification is needed on a bunch of things.
Are these tables that you have created and have the ability to modify or are they tables that you have access to query but not modify? If you can only query them, then you will need to match the case (upper, lower, mixed) for every table name and every column name and enclose them in double quotes everywhere that they are referenced in any of your code. If you can modify these tables, then you need to recreate them without double quotes or anything that violates the usual standards for names, such as having spaces in them or using reserved words like number. That way you can reference them anywhere in your code without double quotes or worrying about matching the case.
Your explanation of how you want to derive the original_requester and know_how columns is unclear. You know to explain it in detail, one row at a time in the order that it is to be done. For example for the first row of your data, you would compare the value CompanyA in the project_given_by column of the source_table to the value CompanyA in the companyalias column of the company_secure_mapping_table to obtain the value SECURE-A from the secureid column of the company_secure_mapping_table, then what column of what table do you compare that value SECURE-A to, and so on.
You said, "Checking record for comparision to be done always from the top of the Source_Table and should exclude the record which is in execution at that instance." You need to realize that Oracle does not store records in the order inserted, so there is no top of the table. If you need to order things, then you need to supply a column for them to be ordered by, such as a numeric id column.
The following shows how you should create your table, insert your data, and query it. I have only used one table and have not included the original_requester or know_how columns, because you have not clearly explained how to derive them.
SCOTT@orcl> -- create table with added id column and
SCOTT@orcl> -- without double quotes around table name or column names and
SCOTT@orcl> -- without spaces in column names:
SCOTT@orcl> CREATE TABLE Source_Table
2 (ID NUMBER GENERATED ALWAYS AS IDENTITY,
3 Project_Given_By VARCHAR2(100 BYTE) NOT NULL ENABLE,
4 Project_done_By VARCHAR2(100 BYTE) NOT NULL ENABLE,
5 DEPT VARCHAR2(100 BYTE) NOT NULL ENABLE,
6 Work_Type VARCHAR2(100 BYTE) NOT NULL ENABLE,
7 Effort_in_hours VARCHAR2(100 BYTE) NOT NULL ENABLE,
8 Effort_in_dollars VARCHAR2(100 BYTE) NOT NULL ENABLE)
9 /
Table created.
SCOTT@orcl> -- insert data without double quotes around table or column names:
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyA','SECURE-A','RnD','Testing',10,100);
1 row created.
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyA','SECURE-X','RnD','Testing',20,200);
1 row created.
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyB','SECURE-X','RnD','Testing',30,300);
1 row created.
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyA','SECURE-A','RnD','Integration',10,100);
1 row created.
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyB','SECURE-A','RnD','Testing',40,400);
1 row created.
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyA','SECURE-X','RnD','Integration',20,200);
1 row created.
SCOTT@orcl> Insert into Source_Table (Project_Given_By,Project_done_By,DEPT,Work_Type,Effort_in_hours,Effort_in_dollars)
2 values ('CompanyC','SECURE-C','FnL','Testing',50,500);
1 row created.
SCOTT@orcl> commit;
Commit complete.
SCOTT@orcl> -- column formatting for easier to read display:
SCOTT@orcl> COLUMN Project_Given_By FORMAT A16
SCOTT@orcl> COLUMN Project_done_By FORMAT A15
SCOTT@orcl> COLUMN Work_Type FORMAT A11
SCOTT@orcl> COLUMN EFFORT_TYPE FORMAT A17
SCOTT@orcl> COLUMN VALUE FORMAT A6
SCOTT@orcl> -- query with ordering added:
SCOTT@orcl> select * from
2 (select x.id, x.Project_Given_By, x.Project_done_By, x.Work_Type, x.Effort_in_hours, x.Effort_in_dollars
3 from Source_Table x)
4 unpivot (value for Effort_Type in (Effort_in_hours as 'Effort in hours', Effort_in_dollars as 'Effort in dollars'))
5 order by id, Effort_Type desc
6 /
ID PROJECT_GIVEN_BY PROJECT_DONE_BY WORK_TYPE EFFORT_TYPE VALUE
---------- ---------------- --------------- ----------- ----------------- ------
1 CompanyA SECURE-A Testing Effort in hours 10
1 CompanyA SECURE-A Testing Effort in dollars 100
2 CompanyA SECURE-X Testing Effort in hours 20
2 CompanyA SECURE-X Testing Effort in dollars 200
3 CompanyB SECURE-X Testing Effort in hours 30
3 CompanyB SECURE-X Testing Effort in dollars 300
4 CompanyA SECURE-A Integration Effort in hours 10
4 CompanyA SECURE-A Integration Effort in dollars 100
5 CompanyB SECURE-A Testing Effort in hours 40
5 CompanyB SECURE-A Testing Effort in dollars 400
6 CompanyA SECURE-X Integration Effort in hours 20
6 CompanyA SECURE-X Integration Effort in dollars 200
7 CompanyC SECURE-C Testing Effort in hours 50
7 CompanyC SECURE-C Testing Effort in dollars 500
14 rows selected.
|
|
|
|
| Re: Finding the source of the Company [message #648659 is a reply to message #648658] |
Mon, 29 February 2016 00:04 |
 |
Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe
|
Senior Member
Account Moderator |
|
|
As of you, using double quotes: what I meant to say was that whenever you use them, you have to strictly follow what you initially wrote. In your example, selecting form the output table would look like this:
select "Project_done_By",
"EFFORT_TYPE"
from "Output_Table"
Writing the same query as
select "Project_Done_By", -- as you can't remember any more whether you used initial caps
"Effort_Type" -- hoping that you used the same convention here as well
from "Output_Table"
or
select "project_done_by",
"effort_type"
from "output_table"
or any other way (which differs from the first one I wrote) would result in an error.
If you omit double quotes, you can type anything you want (upper, lower, mixed case; avoid spaces and certain special characters - read the link BlackSwan posted previously), but Oracle will create those tables and columns as UPPERCASE. As it (Oracle) is not case sensitive about table/column names, you could select values in a simple manner, not paying attention to what you write. It means that all three sample codes I wrote *would* work if you omit double quotes.
I hope you understand the point.
|
|
|
|
Goto Forum:
Current Time: Fri Apr 26 09:37:06 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
