Home » RDBMS Server » Server Utilities » NO RECORDS SELECTED error ( SQL*PLUS ,10.2.0.2.0, Windows XP)
icon4.gif  NO RECORDS SELECTED error [message #339499] Thu, 07 August 2008 11:51 Go to next message
josephsjrmurrell
Messages: 8
Registered: August 2008
Junior Member
Hi all i am new to this forum and I am having some trouble with some SQL based work so i googled the error and i got this site as a search result.

I am creating and populating SQL tables using a batch file. The table data is pulled from a text file. after running the batch file i check the process logs for any errors of any kind and i get the "all clear" now when i type a DESC TABLENAME; statement in SQL*PLUS i get the table fields for the given table, however when i type a select * from tablename; statement i get a statement telling me NO RECORDS SELECTED. at 1st i thought that the table was not populated but when i check the logs it tell me that the table was in fact created and loaded with the data.
So can anyone tell me what is the cause of this error if the logs tell me that the table was successfully created and populated?
Re: NO RECORDS SELECTED error [message #339501 is a reply to message #339499] Thu, 07 August 2008 11:57 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above

Do not describe what you think is happening.
using CUT & PASTE along with SQLPLUS & <code tags> (in URL above) show us EVERYTHING you are doing so we can see for ourselves.
Re: NO RECORDS SELECTED error [message #339502 is a reply to message #339499] Thu, 07 August 2008 11:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You didn't look at the correct database.
You didn't look at the correct schema.
You didn't look at the correct table.
Who can know with what you posted?

Copy and paste all what you did.

Regards
Michel

[Updated on: Thu, 07 August 2008 11:58]

Report message to a moderator

Re: NO RECORDS SELECTED error [message #339503 is a reply to message #339499] Thu, 07 August 2008 12:00 Go to previous messageGo to next message
ThomasG
Messages: 3189
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
Read the forum guide first.

Don't describe what you think you did, since that is not what you actually did in 95% of cases.

Post a copy/paste of the session of what you actually did.

For example : there are at least 10 different posibilities of "populating SQL tables using a batch file." that I can think of, so what process did you actually use?

Also : Could it be that there were actually no rows for that table in the mysterious text file?

Edit:

Dang. Michel never sleeps. Laughing

[Updated on: Thu, 07 August 2008 12:01]

Report message to a moderator

Re: NO RECORDS SELECTED error [message #339508 is a reply to message #339499] Thu, 07 August 2008 12:19 Go to previous messageGo to next message
josephsjrmurrell
Messages: 8
Registered: August 2008
Junior Member
SQL*Plus: Release 10.2.0.2.0 - Production on Thu Aug 7 12:36:51 2008

Copyright (c) 1982, 2005, Oracle. All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

LEGACY_CIS@MTEST.WORLD>> desc chart_of_accts;
Name Null? Type
----------------------------------------- -------- ----------------------------
ACCOUNT_ID VARCHAR2(24)
FERC_NO VARCHAR2(5)
COST_ELEMENT VARCHAR2(9)
ACCOUNT_LNK VARCHAR2(16)
ACCOUNT_NAME VARCHAR2(34)
SEARCH_NAME VARCHAR2(34)
DEBIT_CREDIT_TYP VARCHAR2(Cool
STATEMENT_TYPE VARCHAR2(Cool
ACCOUNT_TYPE VARCHAR2(Cool
SUB_ACCOUNT_TYPE VARCHAR2(Cool
VALID_SUBSYSTEMS VARCHAR2(24)
VALID_AR_ACCOUNT VARCHAR2(Cool
VALID_AP_ACCOUNT VARCHAR2(Cool
VALID_OM_ACCOUNT VARCHAR2(12)
VALID_PR_ACCOUNT VARCHAR2(2)
VALID_IN_ACCOUNT VARCHAR2(2)
VALID_FA_ACCOUNT VARCHAR2(2)
ATTACHED_RECORDS VARCHAR2(12)
TOLERANCE_CODE VARCHAR2(1)
CREATE_DT VARCHAR2(10)
CREATE_TIME VARCHAR2(Cool
LAST_CHANGE_DT VARCHAR2(10)
LAST_CHANGE_TIME VARCHAR2(Cool
OPERATOR VARCHAR2(Cool
CHANGE_TYPE VARCHAR2(1)
PROGRAM_NAME VARCHAR2(40)

LEGACY_CIS@MTEST.WORLD>> select * from chart_of_accts;

no rows selected

Above it what i do and their respective results. what you see in bold is what i type everything else is what is generated by the application. How do i enclose code in tags? Forgive the smileys i don't know why they are appearing.
Re: NO RECORDS SELECTED error [message #339510 is a reply to message #339499] Thu, 07 August 2008 12:26 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Unless & until you follow Posting Guidelines & respond with requested feedback, You're On Your Own (YOYO)!

Re: NO RECORDS SELECTED error [message #339516 is a reply to message #339510] Thu, 07 August 2008 13:27 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
anacedent wrote on Thu, 07 August 2008 19:26
http://www.orafaq.com/forum/t/88153/0/
Unless & until you follow Posting Guidelines & respond with requested feedback, You're On Your Own (YOYO)!

I agree.
@josephsjrmurrell read your post, what is your opinion?

From the section of guide provided:
Quote:
please be sure to use formatting tags:
[code] Enter your code here.[/code]



Regards
Michel

[Updated on: Thu, 07 August 2008 13:49]

Report message to a moderator

Re: NO RECORDS SELECTED error [message #339795 is a reply to message #339508] Fri, 08 August 2008 09:11 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Does your log file tell you how many records it inserted into the table?

What it looks like is either
1) You're looking at a table in a different schema or database to the one that you think toy're looking at
2) The Batch load hasn't inserted any records
3) The Batch load hasn't commited after inserting the records.

To format your code, highlight it, and then click the icon just to the left of the Size dropdown list above the text box, or encluse it in Code tage like this:[CODE] Here is some code[/CODE]
Re: NO RECORDS SELECTED error [message #340155 is a reply to message #339795] Mon, 11 August 2008 12:50 Go to previous messageGo to next message
josephsjrmurrell
Messages: 8
Registered: August 2008
Junior Member
SQL*Plus: Release 10.2.0.2.0 - Production on Mon Aug 11 13:47:13 2008

Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.


Connected to:
Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.7.0 - Production

LEGACY_CIS@MTEST.WORLD>> select * from dep_move;

no rows selected

LEGACY_CIS@MTEST.WORLD>> desc dep_move;
 Name                                      Null?    Type
 ----------------------------------------- -------- ------------------
 DEP_MOVE_ENQ_KEY                                   VARCHAR2(29)
 SERV_KEY                                           VARCHAR2(14)
 SERVICE_NO                                         VARCHAR2(7)
 CUSTOMER_NO                                        VARCHAR2(7)
 ADJS_NO                                            VARCHAR2(8)
 DEP_TRANS_DATE                                     VARCHAR2(10)
 RECEIPT_NO                                         VARCHAR2(7)
 DEP_TRANS_AMOUNT                                   VARCHAR2(13)
 DEP_INTEREST                                       VARCHAR2(13)
 DEP_WITHHOLDING_TAX                                VARCHAR2(10)
 DEP_TRANS_TOTAL                                    VARCHAR2(13)
 DEP_REFUND                                         VARCHAR2(13)
 DEP_MISC_ACCOUNT                                   VARCHAR2(13)
 ACCOUNT_CODE                                       VARCHAR2(9)
 WO_NUM                                             VARCHAR2(4)
 ACCOUNT_LNK                                        VARCHAR2(9)
 UNIT_LNK                                           VARCHAR2(9)
 PROJECT_LNK                                        VARCHAR2(50)
 DEP_CUST_ACCOUNT                                   VARCHAR2(13)
 DEP_TRANS_TYPE                                     VARCHAR2(1)
 DEP_TRANS_DIGIT                                    VARCHAR2(1)
 DEP_TRANS_FORMNO                                   VARCHAR2(7)
 FIN_ADJS_REM                                       VARCHAR2(60)
 AUTH_CODE                                          VARCHAR2(1)
 DATE_OF_STAT                                       VARCHAR2(10)
 CURRENTDATE                                        VARCHAR2(10)
 CURRENT_TIME                                       VARCHAR2(12)
 OPERATOR                                           VARCHAR2(8)
 CHANGE_TYPE                                        VARCHAR2(1)
 PROGRAM_NAME                                       VARCHAR2(20)
 AUTHOR_ID                                          VARCHAR2(8)
 AUTHOR_DATE                                        VARCHAR2(30)
Re: NO RECORDS SELECTED error [message #340157 is a reply to message #339499] Mon, 11 August 2008 13:04 Go to previous messageGo to next message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
yes, you have a table which has no rows.
Is this table the same table in the same schema & the same database the script is operating against?
I suspect that two different tables are involved, but once again we have no way to determine what really is or is not happening.
Re: NO RECORDS SELECTED error [message #340166 is a reply to message #340155] Mon, 11 August 2008 14:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
josephsjrmurrell wrote on Mon, 11 August 2008 19:50
LEGACY_CIS@MTEST.WORLD>> select * from dep_move;


For example, what piece of code generates this prompt? Is it really as dynamic as it implies, or do you happen to have a login.sql in each database's home?
(Don't laugh, I've seen it happen).
Re: NO RECORDS SELECTED error [message #340167 is a reply to message #340155] Mon, 11 August 2008 14:20 Go to previous messageGo to next message
josephsjrmurrell
Messages: 8
Registered: August 2008
Junior Member
here is the situation....

Here is what the log file says after running the batch file


SQL*Loader: Release 10.2.0.2.0 - Production on Mon Aug 11 13:07:45 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Control File:   R:\CIS_LEGACY_FILES\SQL_LOADER\control\DEP_MOVE.ctl
Data File:      R:\CIS_LEGACY_FILES\DEP-MOVE.txt
  Bad File:     bad_file_DEP_MOVE.log
  Discard File:  none specified
 
 (Allow all discards)

Number to load: ALL
Number to skip: 0
Errors allowed: 50
Bind array:     64 rows, maximum of 256000 bytes
Continuation:    none specified
Path used:      Conventional

Table LEGACY_CIS.DEP_MOVE, loaded from every logical record.
Insert option in effect for this table: REPLACE

   Column Name                  Position   Len  Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DEP_MOVE_ENQ_KEY                    FIRST     *   |       CHARACTER            
SERV_KEY                             NEXT     *   |       CHARACTER            
SERVICE_NO                           NEXT     *   |       CHARACTER            
CUSTOMER_NO                          NEXT     *   |       CHARACTER            
ADJS_NO                              NEXT     *   |       CHARACTER            
DEP_TRANS_DATE                       NEXT     *   |       CHARACTER            
RECEIPT_NO                           NEXT     *   |       CHARACTER            
DEP_TRANS_AMOUNT                     NEXT     *   |       CHARACTER            
DEP_INTEREST                         NEXT     *   |       CHARACTER            
DEP_WITHHOLDING_TAX                  NEXT     *   |       CHARACTER            
DEP_TRANS_TOTAL                      NEXT     *   |       CHARACTER            
DEP_REFUND                           NEXT     *   |       CHARACTER            
DEP_MISC_ACCOUNT                     NEXT     *   |       CHARACTER            
ACCOUNT_CODE                         NEXT     *   |       CHARACTER            
WO_NUM                               NEXT     *   |       CHARACTER            
ACCOUNT_LNK                          NEXT     *   |       CHARACTER            
UNIT_LNK                             NEXT     *   |       CHARACTER            
PROJECT_LNK                          NEXT     *   |       CHARACTER            

value used for ROWS parameter changed from 64 to 55
Record 1: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 2: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 3: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 4: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 5: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 6: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 7: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 8: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 9: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 10: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 11: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 12: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 13: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 14: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 15: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 16: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 17: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 18: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 19: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 20: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 21: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 22: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 23: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 24: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 25: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 26: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 27: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 28: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 29: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 30: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 31: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 32: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 33: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 34: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 35: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 36: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 37: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 38: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 39: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 40: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 41: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 42: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 43: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 44: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 45: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 46: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 47: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 48: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 49: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 50: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)
Record 51: Rejected - Error on table LEGACY_CIS.DEP_MOVE, column PROJECT_LNK.
Column not found before end of logical record (use TRAILING NULLCOLS)

MAXIMUM ERROR COUNT EXCEEDED - Above statistics reflect partial run.

Table LEGACY_CIS.DEP_MOVE:
  0 Rows successfully loaded.
  51 Rows not loaded due to data errors.
  0 Rows not loaded because all WHEN clauses were failed.
  0 Rows not loaded because all fields were null.


Space allocated for bind array:                 255420 bytes(55 rows)
Read   buffer bytes: 1048576

Total logical records skipped:          0
Total logical records read:            51
Total logical records rejected:        51
Total logical records discarded:        0

Run began on Mon Aug 11 13:07:45 2008
Run ended on Mon Aug 11 13:07:45 2008

Elapsed time was:     00:00:00.74
CPU time was:         00:00:00.17



The files that contain the records for the DEP_MOVE table has no blank rows after the last record, i made sure and deleted them but still i get the error.
Re: NO RECORDS SELECTED error [message #340168 is a reply to message #340167] Mon, 11 August 2008 14:37 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
show us record 1 from the file please.
Re: NO RECORDS SELECTED error [message #340172 is a reply to message #340168] Mon, 11 August 2008 15:15 Go to previous messageGo to next message
josephsjrmurrell
Messages: 8
Registered: August 2008
Junior Member
HERE ARE THE .CTL FILES THAT ARE USED TO JOIN THE TWO FILES WHICH MAKE UP THE DEP_MOVE TABLE.

load data
infile 'R:\CIS_LEGACY_FILES\DEP-MOVE.txt' 
badfile 'bad_file_DEP_MOVE.log'
        replace
        into table legacy_cis.DEP_MOVE
        fields terminated by "|"
           (
DEP_MOVE_ENQ_KEY,
SERV_KEY,
SERVICE_NO,
CUSTOMER_NO,
ADJS_NO,
DEP_TRANS_DATE,
RECEIPT_NO,
DEP_TRANS_AMOUNT,
DEP_INTEREST,
DEP_WITHHOLDING_TAX,
DEP_TRANS_TOTAL,
DEP_REFUND,
DEP_MISC_ACCOUNT,
ACCOUNT_CODE,
WO_NUM,
ACCOUNT_LNK,
UNIT_LNK,
PROJECT_LNK
)


load data
infile 'R:\CIS_LEGACY_FILES\DEP-MOVE-2.txt' 
badfile 'bad_file_DEP_MOVE_2.log'
        replace
        into table legacy_cis.DEP_MOVE_2
        fields terminated by "|"
           (
DEP_MOVE_ENQ_KEY,
SERV_KEY,
SERVICE_NO,
CUSTOMER_NO,
DEP_CUST_ACCOUNT,
DEP_TRANS_TYPE,
DEP_TRANS_DIGIT,
DEP_TRANS_FORMNO,
FIN_ADJS_REM,
AUTH_CODE,
DATE_OF_STAT,
CURRENTDATE,
CURRENT_TIME,
OPERATOR,
CHANGE_TYPE,
PROGRAM_NAME,
AUTHOR_ID,
AUTHOR_DATE
)


HERE ARE THE RELATED .SQL FILES FOR EACH HALF OF THE DEP_MOVE TABLE.

DROP  TABLE LEGACY_CIS.DEP_MOVE;
CREATE TABLE LEGACY_CIS.DEP_MOVE (
DEP_MOVE_ENQ_KEY VARCHAR2(29),
SERV_KEY VARCHAR2(14),
SERVICE_NO VARCHAR2(7),
CUSTOMER_NO VARCHAR2(7),
ADJS_NO VARCHAR2(8),
DEP_TRANS_DATE VARCHAR2(10),
RECEIPT_NO VARCHAR2(7),
DEP_TRANS_AMOUNT VARCHAR2(13),
DEP_INTEREST VARCHAR2(13),
DEP_WITHHOLDING_TAX VARCHAR2(10),
DEP_TRANS_TOTAL VARCHAR2(13),
DEP_REFUND VARCHAR2(13),
DEP_MISC_ACCOUNT VARCHAR2(13),
ACCOUNT_CODE VARCHAR2(9),
WO_NUM VARCHAR2(4),
ACCOUNT_LNK VARCHAR2(9),
UNIT_LNK VARCHAR2(9),
PROJECT_LNK VARCHAR2(50))
nologging;


DROP  TABLE LEGACY_CIS.DEP_MOVE_2;
CREATE TABLE LEGACY_CIS.DEP_MOVE_2 (
DEP_MOVE_ENQ_KEY VARCHAR2(29),
SERV_KEY VARCHAR2(14),
SERVICE_NO VARCHAR2(7),
CUSTOMER_NO VARCHAR2(7),
DEP_CUST_ACCOUNT VARCHAR2(13),
DEP_TRANS_TYPE VARCHAR2(1),
DEP_TRANS_DIGIT VARCHAR2(1),
DEP_TRANS_FORMNO VARCHAR2(7),
FIN_ADJS_REM VARCHAR2(60),
AUTH_CODE VARCHAR2(1),
DATE_OF_STAT VARCHAR2(10),
CURRENTDATE VARCHAR2(10),
CURRENT_TIME VARCHAR2(12),
OPERATOR VARCHAR2(8),
CHANGE_TYPE VARCHAR2(1),
PROGRAM_NAME VARCHAR2(20),
AUTHOR_ID VARCHAR2(8),
AUTHOR_DATE VARCHAR2(30))
nologging;


HERE IS THE .SQL FILE THAT JOINS THE TWO HALFS TOGETHER.

CREATE TABLE LEGACY_CIS.DEP_MOVE_ALL
AS
SELECT
A.DEP_MOVE_ENQ_KEY,
A.SERV_KEY,
A.SERVICE_NO,
A.CUSTOMER_NO,
A.ADJS_NO,
A.DEP_TRANS_DATE,
A.RECEIPT_NO,
A.DEP_TRANS_AMOUNT,
A.DEP_INTEREST,
A.DEP_WITHHOLDING_TAX,
A.DEP_TRANS_TOTAL,
A.DEP_REFUND,
A.DEP_MISC_ACCOUNT,
A.ACCOUNT_CODE,
A.WO_NUM,
A.ACCOUNT_LNK,
A.UNIT_LNK,
A.PROJECT_LNK,
B.DEP_CUST_ACCOUNT,
B.DEP_TRANS_TYPE,
B.DEP_TRANS_DIGIT,
B.DEP_TRANS_FORMNO,
B.FIN_ADJS_REM,
B.AUTH_CODE,
B.DATE_OF_STAT,
B.CURRENTDATE,
B.CURRENT_TIME,
B.OPERATOR,
B.CHANGE_TYPE,
B.PROGRAM_NAME,
B.AUTHOR_ID,
B.AUTHOR_DATE
FROM  DEP_MOVE A , DEP_MOVE_2 B
WHERE 
    A.DEP_MOVE_ENQ_KEY=B.DEP_MOVE_ENQ_KEY
AND A.SERV_KEY=B.SERV_KEY
AND A.SERVICE_NO=B.SERVICE_NO
AND A.CUSTOMER_NO=B.CUSTOMER_NO;


DROP TABLE DEP_MOVE;
DROP TABLE DEP_MOVE_2;
RENAME  DEP_MOVE_ALL   TO DEP_MOVE;


Re: NO RECORDS SELECTED error [message #340230 is a reply to message #340172] Tue, 12 August 2008 01:00 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
josephsjrmurrell wrote on Mon, 11 August 2008 22:15
HERE ARE THE .CTL FILES THAT ARE USED TO JOIN THE TWO FILES WHICH MAKE UP THE DEP_MOVE TABLE.


Please don't yell at us, we are only trying to help.

Reread what I asked.
Re: NO RECORDS SELECTED error [message #340406 is a reply to message #340172] Tue, 12 August 2008 11:41 Go to previous message
Barbara Boehmer
Messages: 8635
Registered: November 2002
Location: California, USA
Senior Member
Although we have not seen any sample data to verify it, your log file plainly states what the problem is. You do not have a "|" after your last field at the end of each line, so you need to use "trailing nullcols", as shown below. This may not be the only problem. I didn't check the rest.

load data
infile 'R:\CIS_LEGACY_FILES\DEP-MOVE.txt'
badfile 'bad_file_DEP_MOVE.log'
replace
into table legacy_cis.DEP_MOVE
fields terminated by "|"
TRAILING NULLCOLS
(
DEP_MOVE_ENQ_KEY,
SERV_KEY,
SERVICE_NO,
CUSTOMER_NO,
ADJS_NO,
DEP_TRANS_DATE,
RECEIPT_NO,
DEP_TRANS_AMOUNT,
DEP_INTEREST,
DEP_WITHHOLDING_TAX,
DEP_TRANS_TOTAL,
DEP_REFUND,
DEP_MISC_ACCOUNT,
ACCOUNT_CODE,
WO_NUM,
ACCOUNT_LNK,
UNIT_LNK,
PROJECT_LNK
)

load data
infile 'R:\CIS_LEGACY_FILES\DEP-MOVE-2.txt'
badfile 'bad_file_DEP_MOVE_2.log'
replace
into table legacy_cis.DEP_MOVE_2
fields terminated by "|"
TRAILING NULLCOLS
(
DEP_MOVE_ENQ_KEY,
SERV_KEY,
SERVICE_NO,
CUSTOMER_NO,
DEP_CUST_ACCOUNT,
DEP_TRANS_TYPE,
DEP_TRANS_DIGIT,
DEP_TRANS_FORMNO,
FIN_ADJS_REM,
AUTH_CODE,
DATE_OF_STAT,
CURRENTDATE,
CURRENT_TIME,
OPERATOR,
CHANGE_TYPE,
PROGRAM_NAME,
AUTHOR_ID,
AUTHOR_DATE
)
Previous Topic: sqlloader multiple into table clause
Next Topic: DBMS_DATAPUMP
Goto Forum:
  


Current Time: Thu Dec 08 04:27:59 CST 2016

Total time taken to generate the page: 0.14899 seconds