Invalid Number on Select Into Statement - OO4O and Wizards(Archived)

I have a select into statement that is pulling variables from my application and inserting them into a database. The variables are being assigned the proper numbers and the numbers are in the correct format to be put into Oracle but I keep getting the error "Invalid Number". Here is the code:
OraDatabase.ExecuteSQL ("INSERT INTO IM (IM_KEY,IM_ACT_MAT,IM_AVG_MAT,IM_PRICES1,IM_PRICES2,IM_PRICES3,IM_PRICES4,IM_PRICES5)VALUES (' & IMKEY & ',' & IMACTMAT & ',' & IMAVGMAT & ',' & IMPRICES1 & ',' & IMPRICES2 & ',' & IMPRICES3 & ',' & IMPRICES4 & ',' & IMPRICES5 & ')")
Can anyone tell me where I'm going wrong? Thanks, Jeremy

Related

Oracle Reports 6i Parameters

I have a SQL statments in my SQL query staments for my report. I have set up a parameter for the :FrmDpt, ToDpt, FrmDate, and ToDte, but I tried to setup a parametetr for the type which can be only Capital , All, or NonCapital. Here are some of the statements I have tried in my list values for the Type Parm.
('\'Capital\',\'Standard\',\'Blanket\',\'Commodity\',\'Invtyreq\',\'Service\',\'SMPO\'')
("Capital","Standard","Blanket","Commodity","Invtyreq","Service","SMPO")
'Capital,Standard,Blanket,Commodity,Invtyreq,Service,SMPO'
"Capital";"Standard";"Blanket";"Commodity";"Invtyreq";"Service";"SMPO"
' ' 'Capital' ',' 'Standard' ',' 'Blanket' ',' 'Commodity' ',' 'Invtyreq' ',' 'Service' ', ' 'SMPO' ' '
Non of them would work - it returned nothing. Here is the sql code I am using for that portion.
WHERE ( pli.po_requistn_id (+) = p.po_requistn_id) and
( pli.chgordr_id (+) = p.chgordr_id) and
( p.hr_id = hr_b.hr_id ) and
( p.orig_id = hr_a.hr_id ) and
( p.venid_num = v.venid_num ) and
( pli.department_id between :FrmDpt and :ToDpt) and
( p.req_create_date between :FrmDte and :ToDte) and
( p.purchase_type in :Type) 
Change ( p.purchase_type in :Type) to ( p.purchase_type in &Type)
Regards,
Martin 
Thanks - I was able to get the reports I needed. I just need to look into see if there is a way to show the user one word selection such as 'All' and pass the list to the
report query.

characters

hello
please i need to display characters like ",: in my report concat with another
fields like emp_no ,i fail to do that
please if any one help me where i am using report builder6i and database 10g
thanks 
try to use text items or boiler plates for these kind of objects. 
I'm not quite sure if i understand your requirement..
Do you just want to add an ";" behind a field? Or do you want to select just one big string where the fields are concatenated by ";"?
For both cases i would do i right away in the query:
SELECT COL1 || ';' ||
       COL2 || ';' ||
       COL3 || ';' ||
       COL4 || ';' THEDATA
  FROM THETABLE

How to escape quotes and commas when using the Spool command

I'm trying to create a .cvs file with the data in my database. I'm calling the spool function and the below select stmt
select '"' || id || '","' || name || '","' || description || '","'
I want everything to be seperated by quotes and commas. Description data can have quotes and commas so how do I escape them in my Spooling code so that when I use the SQL Loader the data will get inputted into the database correctly.
Thanks! 
Hi,
If the field is enclosed, then there's nothing else you have to do for the column separator (,).
To use the enclosing character (") in the field, double it, the same way you use two single-quotes to mean one single_quote in a SQL string literal:
{code}
SELECT     '"'     || id
||     '","'     || name
||     '","'     || REPLACE ( description
               , '"'
               , '""'
               )
||     '"'
FROM     table_x;
                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                       

SQLLDR issue with csv file

Hi All,
I have a table with the following columns
SYSTEM_SOURCE VARCHAR
NATIONAL_IDENTIFIER VARCHAR
HOURS NUMBER
EARNING_CODE VARCHAR
PAYROLL_NAME VARCHAR
COST_CENTER VARCHAR
BRAND VARCHAR
WEEK_NUMBER NUMBER
AMOUNT NUMBER
STATUS VARCHAR
DETAIL_FLAG VARCHAR
My sqlldr script is as follows:
LOAD DATA
INFILE *
APPEND INTO TABLE XXARG.XXARG_HR_TIME_SUMMARY_STG
fields terminated by ","
TRAILING NULLCOLS
(
SYSTEM_SOURCE CONSTANT 'RTI'
,NATIONAL_IDENTIFIER CHAR terminated by "," "substr(REPLACE(:NATIONAL_IDENTIFIER,'TC#'),1,3) || '-' || substr(REPLACE(:NATIONAL_IDENTIFIER,'TC#'),4,2) || '-' || substr(REPLACE(:NATIONAL_IDENTIFIER,'TC#'),6,4)"
,HOURS DECIMAL EXTERNAL terminated by "," NULLIF (HOURS=BLANKS)
,EARNING_CODE CHAR terminated by "," "trim(:EARNING_CODE)"
,PAYROLL_NAME CHAR terminated by ","
,COST_CENTER CHAR terminated by "," "'01'|| LPAD(TRIM(REPLACE(:COST_CENTER,'a')),5,'0')"
,BRAND CONSTANT 'ARG'
,WEEK_NUMBER DECIMAL EXTERNAL
,AMOUNT DECIMAL EXTERNAL NULLIF (AMOUNT=BLANKS)
,STATUS CONSTANT 'U'
,DETAIL_FLAG CONSTANT 'N'
)
The data file is as follows:
999999999 , 35.416, REG, RNO , 07157, 1, ,
999999999 , 34.549, REG, RNO , 07157, 2, ,
999999999 , , BON, RNO , 07157, 2, 25.00
When I run sqlldr from my desktop (sqlldr 9i), it works fine. However when I run the sqlldr from the server (unix box), it fails for row 3
Record 3: Rejected - Error on table XXARG.XXARG_HR_TIME_SUMMARY_STG, column AMOUNT.
ORA-01722: invalid number
If I put a ',' at the end of line 3, it works fine on the server as well. Appreciate your help in resolving this issue.
Thanks
Venky 
What is the full database version (I suspect 10g+) ? In your control file, you have specified
>
fields terminated by ","
>
yet your last field in the third row does not have this delimiter. 9i may have been forgiving and used the CR/LF as a separator, but the database version needs the delimiter.
HTH
Srini 
Hi Srini,
Yes, we are on 10.2.0.4
Yeah, the csv file we are trying to load has ',' at the end if amount is null, if there is value for Amount, there is no delimiter.
Is there a way to get around it?
Thanks,
Venky 
I do not believe there is (other than using the 9i client like you mentioned). Is there a reason the delimiter cannot be present if the amount is not null ?
Srini

Can SQL*Loader Insert concatenated string into table

Hi All,
I want to insert a column, whose format is "abc" + to_char(sysdate,'YYYYMMDD'), into temp table. How can I do it? Thank you in advance.
Best Regards,
Sheng 
Hi Sheng
Is this what you need?
LOAD DATA
  INFILE data.txt
  INTO TABLE tmp_table
  fields terminated by "," optionally enclosed by '"'
  (  c1   ":c1 || TO_CHAR(SYSDATE, 'YYYYMMDD')"
  )Lukasz 
Hi Lukasz,
Thank you for your help! The "abc" is a constant string, it isn't a column. And I use concat function to solve the problem. like this
LOAD DATA
INFILE data.txt
INTO TABLE tmp_table
fields terminated by "," optionally enclosed by '"'
( c1 "concat('abc',TO_CHAR(SYSDATE, 'YYYYMMDD'))"
)
Sheng
Edited by: Sheng on 2013-5-26 下午4:44

Categories

Resources