Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 460
  • Last Modified:

Inconsistancy between data file and control file

What are the possible scenarios where there may be inconsistancy between
data file and the control file.
0
gram77
Asked:
gram77
  • 4
  • 4
1 Solution
 
gram77Author Commented:
What will be the effect on loading data if some columns of control file are commented?
will colD in data file get loaded into colF of control file?
data file      ControlFile
colA            colA
colB            colB
colC            colC
colD            colD (commented)
colE            colE (commented)
colF            colF
colG            colG


What will be the effect on loading data if some columns of data file are commented.
will colF of data file get loaded into colD of control file?
data file            ControlFile
colA                  colA
colB                  colB
colC                  colC
colD (commented)      colD
colE (commented)      colE
colF                  colF
colG                  colG


What will be the effect on loading data if both data file and control file are commented.
will colB in data file get loaded into colD in control file and
will colC in data file get loaded into colE in control file and
will colF in data file get loaded into colG in control file and
data file            ControlFile
colA                  colA
colB                  colB (commented)
colC                  colC (commented)
colD (commented)      colD
colE (commented)      colE
colF                  colF (commented)
colG                  colG
0
 
slightwv (䄆 Netminder) Commented:
This sounds a lot like a homework/exam question.

Please clarify.

If this isn't a homework question:  I do not understand what you mean by "control file are commented"
0
 
gram77Author Commented:
"control file are commented" means columns of control file are commented

We get data from a vendor that is entered into tables using control file.
The vendor keeps removing/ adding columns in the data file.

I need to verify if we are inserting right data into the table. ie. colA is not going into colB but goes into colA only.

There are times when some columns are removed by vendor from the data file, in such cases we need to comment out the columns in control file
as well so that there is a sync between the datafile and the control file. At times this may not be done properly and it is feared that
there is a mismatch between columns of data file and control file... I need to check such instances

0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
slightwv (䄆 Netminder) Commented:
Still not understanding.

SQL Loader control file?

Can you show me an example of what 'commented' means?
0
 
gram77Author Commented:
--SQLLDR ctl file
OPTIONS (DIRECT=FALSE, PARALLEL=FALSE, BINDSIZE=50000, ROWS=200, READSIZE=65536)
LOAD DATA
APPEND
INTO TABLE abc
FIELDS TERMINATED BY "|"
TRAILING NULLCOLS
(
ADR_UNDL_TICKER
ADR_SH_PER_ADR
DVD_CRNCY
EQY_DVD_SH_12M_NET
EQY_DVD_SH_12M
EQY_DVD_SH_LAST
--CPN
EQY_LAST_DPS_GROSS
EQY_DVD_PCT_FRANKED
EQY_DVD_TYP_LAST
EQY_DVD_FREQ
--CPN_FREQ
PFD_DVD_PAY_DT
DVD_RECORD_DT
DVD_DECLARED_DT
EQY_SPLIT_DT
EQY_SPLIT_RATIO
..
..
0
 
slightwv (䄆 Netminder) Commented:
I've never tried commenting out fields in SQL Loader.

If that doesn't cause a syntax error then data will get loaded in position order.

field position 1 in the datafile will get loaded into field position 1 in the control file.

In the example above whatever is in position 7 in the data file will be placed in EQY_LAST_DPS_GROSS.
0
 
gram77Author Commented:
There are times where the sequence of fileds in data file does not match with the sequence of fileds in control file.
Does this mean that wrong data is going into wrong columns of the table?

DataFile      ControlFile
colA            ColA
colB            ColB
colC            ColD
colD            colE
0
 
slightwv (䄆 Netminder) Commented:
I'm not sure I am fully understanding the full question here.

If your datafile 'columns' do not positionally match the columns in the control file, the data likely isn't going where you think it is.

I don't know everything there is to know about sql loader but I believe it is a one-to-one deal between data fields and control file fields.

It should be a simple matter to create a quick test case to test it.

A simple table, three columns, a simple control file with some data.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now