0

I have a table named Addresses(having multiple address line num column) and another table named Phy_Addr

Table 1: Addresses Columns: EmployeeID, Addr_Line_1, Addr_Line_2, Addr_Line_3

Table 2: Phy_Addr Column: EmployeeID, Addr_Line_Filled

I would like to populate the value of a column in Phy_Addr(Addr_Line_Filled) based on the which columns in Addresses(Table 1) has a value. e.g.

  • If Addr_Line_1 has a Value then put 1 in Addr_Line_Filled
  • If Addr_Line_2 also has Value then put 12 in Addr_Line_Filled
  • If Addr_Line_3 also has a value then put 123 in Addr_Line_Filled
  • and so on for each EmployeeID

How can I do this as part of my sql loader .ctl file? This is what I have currently in both the ctl files

phy_addr.ctl

load data
CHARACTERSET UTF8
replace
into table Phy_Addr
WHEN (01) <> 'TRAILER'
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(
EMPLOYEE_ID,
PHYSICAL_ADDRESS_ID,
)

Addresses.ctl

load data
CHARACTERSET UTF8
replace
into table Addresses
WHEN (01) <> 'TRAILER'
fields terminated by '|'
optionally enclosed by '"'
trailing nullcols
(
EMPLOYEE_ID,
ADDRESS_LINE_NUM,
ADDRESS_LINE_TEXT ,
ADDRESS_LINE_LABEL,
PHYSICAL_ADDRESS_ID,
)

DDL Table Phy_Addr

CREATE TABLE Phy_Addr
(
  *PK EMPLOYEE_ID VARCHAR2(7 CHAR) NOT NULL 
, *PK PHYSICAL_ADDRESS_ID VARCHAR2(100 CHAR) NOT NULL 
)

DDL Table Addresses

CREATE TABLE Addresses
(
  *PK EMPLOYEE_ID VARCHAR2(7 CHAR) NOT NULL
, *PK ADDRESS_LINE_NUM NUMBER(*, 0) NOT NULL 
, ADDRESS_LINE_TEXT1 VARCHAR2(55 CHAR)
, ADDRESS_LINE_TEXT2 VARCHAR2(55 CHAR)
, ADDRESS_LINE_TEXT3 VARCHAR2(55 CHAR)
, ADDRESS_LINE_TEXT4 VARCHAR2(55 CHAR)
, ADDRESS_LINE_LABEL VARCHAR2(50 CHAR) 
, *PK PHYSICAL_ADDRESS_ID VARCHAR2(100 CHAR) NOT NULL 
)

How can I achieve the same? Should I be making changes in the Addresses.ctl file for the PHYSICAL_ADDR_LINE field to have the common logic across both the columns data in both the tables?

Darshak
  • 31
  • 5
  • Any reason you plan on using sql loader (not even sure if sql loader can do table to table copy) for this, given it can be done via insert into table2(column-list) select from tabl1 (column-list-with transformation)? – Pankaj Mar 31 '22 at 22:32
  • You can do via insert as - insert into address2 select id,nvl2(a1,'1',a1)||nvl2(a2,'2',a2)||nvl2(a3,'3',a3)||nvl2(a4,'4',a4)||nvl2(a5,'5',a5) as addrnum from address1; – Pankaj Mar 31 '22 at 23:10
  • You can refer to other columns in the SQL Loader field list as binds, eg COL1 CHAR(100), COL2 CHAR(100) "UPPER(:COL1)" etc. So you just build your case expression that way – Connor McDonald Apr 01 '22 at 00:19
  • @Pankaj i can even do insert into address2 but it needs a new sql query and I want to use it within the above ctl code so that when data is loaded from file it is auto validated – Darshak Apr 01 '22 at 11:32
  • @ConnorMcDonald How can I concat the addr_num field based on above conditions? – Darshak Apr 01 '22 at 11:34
  • I think your basic data design is flawed. The table PHY_ADDR is simply redundant of table ADDRESSES. It is a fundamental rule of data design that you not store a value that can be derived from other data at any time. And that is exactly what PHY_ADDR is doing. Instead of table PHY_ADDR, all you need is a view on table ADDRESSES. Besides, the combined length of all of the addresses is 270 characters, yet PHY_ADDR will only hold 100. – EdStevens Apr 01 '22 at 13:41
  • @EdStevens yes it is redundant to have the table PHY_ADDR (not sure why we have it in our data design). We have the field PHYSICAL_ADDRESS_ID in table ADDRESSES, which can be populated based on the above logic and can then simply do an insert into the PHY_ADDR table for whatever value this field has, what would be the changes I need to do in the ctl file for this? – Darshak Apr 01 '22 at 19:26
  • Hi all, I have edited the question to include both the ctl files where the logic should be there, still unable to figure out how to set the values to the PHYSICAL_ADDRESS_ID ? – Darshak Apr 01 '22 at 20:22
  • _"We have the field PHYSICAL_ADDRESS_ID in table ADDRESSES,"_ Still a flawed design. There is no need to combine those individual address fields, regardless of what table you use to hold that combined data. And even if you do still try to combine them, the combined column isn't large enough to hold the data, as I already pointed out. If you really, really, really insist that you need to keep both sets of data (individual and combined) then forget sqlldr and access the file as an external table, Then you can easily do whatever transformations you want as you access it. – EdStevens Apr 01 '22 at 21:29
  • How can I do it in only 1 table for PHYSICAL_ADDRESS_ID in table Addresses. something like ``` CASE WHEN ADDRESSLINETEXT1 IS NOT NULL THEN PHYSICAL_ADDRESS_ID=CONCAT(:EMPLOYEE_ID, '1')``` – Darshak Apr 05 '22 at 15:18

0 Answers0