Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

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

Trigger code: ....Error converting data type varchar to real

Alex, please this thread to "trigger error"
------------------------------------------------------------
Rmp table structure:
_sow_bank2, varchar (40)
_sow_bank3, varchar (40)
_sow_bank4, varchar (40)
_sow_bank5, varchar (40)

_sow_money2, float(8)
_sow_money3, float(8)
_sow_money4, float(8)
_sow_money5, float(8)

_sow_date2, smalldatetime
_sow_date3, smalldatetime
_sow_date4, smalldatetime
_sow_date5, smalldatetime
--------------------------------

I got this error msg...
....error converting data type varchar to real
SQL Error msg 8114, state 5...

0
Vasi04
Asked:
Vasi04
  • 15
  • 15
1 Solution
 
alexprestonCommented:
try this.

set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + cast(@bank_assets as float(8)) + ', ''' + @changed_date + '''';
 
0
 
alexprestonCommented:
hang on let me think. I don't usually build up SQL like this, so it's a bit out of my box :)
0
 
alexprestonCommented:
yes, it's not the @bank_assets that's the problem.
It's the lack of quotes around @bank_name. (which I had spotted but not mentioned!)

try this

set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + @bank_assets + ', ''' + @changed_date + '''';

i.e. the only change is the inclusion of singles quotes around the bank name
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Vasi04Author Commented:
will get back soon. thanks
0
 
Vasi04Author Commented:
One more question:
suppose if there is no value to insert (say bank_name), then can I retain whatever was there in the rmp field. can I have this in my code? Sorry too many questions
0
 
alexprestonCommented:
Not quite sure what you mean! Since you are inserting, what do you want to retain?
0
 
Vasi04Author Commented:
CREATE TRIGGER [myRMP] ON [BANKREL]
FOR INSERT, UPDATE, DELETE
AS

BEGIN

DECLARE query CURSOR FOR
select _bank, convert(varchar, _change_date), _assets
from bankrel where bankrel._active = 'Y' and _siteid in (select _siteid from inserted)
order by bankrel._assets desc ;
declare @bank_name varchar(500);
declare @changed_date varchar(500);
declare @bank_assets varchar(500);
declare @counter int;
declare @sqlstr varchar (4000);
declare @comma varchar;

set @sqlstr = 'insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3, _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values (';
set @counter = 0;
set @comma = ' ';

OPEN query;
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;
WHILE (@@FETCH_STATUS <> -1 and @counter <> 4 )
BEGIN
      set @counter = @counter + 1;
           set @sqlstr = @sqlstr + @comma + '''' @bank_name + ''', ' + @bank_assets + ', ''' + @changed_date + '''';
           set @comma = ',';
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;
END;

CLOSE query;
DEALLOCATE query;

set @sqlstr = @sqlstr + ')';
exec(@sqlstr);
END;

----------------------------------------------------------------
Error msg:

Incorrect syntax near @bank_name
0
 
alexprestonCommented:
oops, missed a "+"

set @sqlstr = @sqlstr + @comma + '''' + @bank_name + ''', ' + @bank_assets + ', ''' + @changed_date + '''';
0
 
Vasi04Author Commented:
syntax is fine.,
I get error msg:
server msg 170, state1,.... incorrect syntax nere '19'
0
 
alexprestonCommented:
Could you maybe write out @sqlstr instead of executing it. Then I can see exactly what SQL it has produced.
Maybe just set up a table with one varchar(8000) field.
Instead of the exec(@sqlstr) line, do insert into [table] values @sqlstr.
Then look up what has been inserted and post the result.
Ta.
0
 
alexprestonCommented:
values ( @sqlstr )          :-)
0
 
Vasi04Author Commented:
ok
0
 
Vasi04Author Commented:
Hi
It has written complete sqlstr into first 3 rows but I cannot copy the field
. Says "coinitialize not found" when try to copy the content of the row.
0
 
alexprestonCommented:
That's a new one on me. I'll have a think.
0
 
alexprestonCommented:
Are you trying to copy from EM? Try it in QA.
0
 
Vasi04Author Commented:
Here it goes (results from QA)
.................................

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Aargauische Kantonalbank', Aug 19 2004 12:00AM, '90','ABN-AMRO', Aug 19 2

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Bank of Bermuda', Aug 19 2004 12:00AM, '99','Aargauische Kantonalbank', A

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Bank of Bermuda', Aug 19 2004 12:00AM, '99','Aargauische Kantonalbank', A
insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Bank of Bermuda', Aug 19 2004 12:00AM, '99','Aargauische Kantonalbank', A

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale du Valais', Aug 19 2004 12:00AM, '100')

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale du Valais', Aug 19 2004 12:00AM, '100','Bank Rothschild'

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale de Zurich', Aug 19 2004 12:00AM, '120','Banque Cantonale

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale de Zurich', Aug 19 2004 12:00AM, '120','Banque Cantonale

insert into rmp ( _sow_bank2, _sow_money2, _sow_date2, _sow_bank3, _sow_money3,
              _sow_date3,  _sow_bank4, _sow_money4, _sow_date4, _sow_bank5, _sow_money5, _sow_date5) values ( 'Banque Cantonale de Zurich', Aug 19 2004 12:00AM, '120','Banque Cantonale
...
...
(13 row(s) affected)

--

Its not writing ')' at the end!!
0
 
alexprestonCommented:
The assets and the date are in the wrong order.

Change your initial select to..
select _bank, _assets, convert(varchar, _change_date)

QA has a maximum number of characters. I suspect this is set too low. Hence it's cutting off a lot of each statement.
You can change this by
Tools
Options
Results
Maximum characters per column.

But I suspect by correcting the order of assets and date this might fix the original issue!!!
0
 
Vasi04Author Commented:
Thanks.
one more thing, are we inserting the values, instead of updating. Insert creates new rows. DO I have replace insert with update?
0
 
alexprestonCommented:
That depends what you want to do!!!

If you do need an update you'll want to change it to something like

UPDATE tablename SET numberfield = value, stringfield = 'value'
WHERE somefield = criteria

0
 
alexprestonCommented:
If I have the correct view of what you are trying to do (update the top 4 values when a change is made), then you probably want to delete the existing top 4, then insert the new top 4. Is this right?
0
 
Vasi04Author Commented:
You are right, delete old values and insert new values.
inserting or overwriting new values to already existing rmp rows
0
 
Vasi04Author Commented:
I replaced insert into table1...
 with.. exec ( @sqlstr);
I get this error:
...Canboot insert nullĀ“into column _SQL, tabeldbo.rmo, column does not allow null. INSERT fails.
0
 
alexprestonCommented:
I think that's because sometimes there aren't 4 rows, so it doesn't go around the loop 4 times. The simple solution is to either set the columns to allow nulls, or give them a default value.
0
 
Vasi04Author Commented:
I tried, but gives me same error.
0
 
alexprestonCommented:
Where has this column _SQL come from? It says it's in table rmo. I don't know what's going on with this table, I thought we were inserting into table rmp !?!?
0
 
Vasi04Author Commented:
I thnk, I am also lost. We need to update fields in rmp. I think that would solve the problem, could you please give me update code instead of insert into?
I thank you a lot.
0
 
alexprestonCommented:
sorry, bit busy, not sure I'll have time to look at this for a while.
0
 
Vasi04Author Commented:
anyway thanks. but If you could help, I can start a new thread
0
 
Vasi04Author Commented:
Hi ALex, I have tried to use update statements in our code.
Could you pls have a look. correct any incorrect code?
I get this error msg ... "cannot convert data type varchar to float".
Is it ok to use table "inserted", even there is update or delete action on bankrel?
Thanks
---------------------------------------------------------------

CREATE TRIGGER [myRMP] ON [BANKREL]
FOR INSERT, DELETE, UPDATE
AS

BEGIN

DECLARE query CURSOR FOR
select Top 4 bankrel._bank, bankrel. _change_date, bankrel._assets, bankrel._internid
from bankrel where bankrel._active = 'Y' and bankrel._internid in (select _internid from inserted)
order by bankrel._assets desc;
declare @bank_name varchar(500);
declare @changed_date varchar(500);
declare @bank_assets varchar(500);
declare @internid varchar(500);
declare @counter int;
set @counter = 0;

OPEN query;
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date, @internid;
WHILE (@@FETCH_STATUS <> -1 and @counter <> 4 )

BEGIN

UPDATE RMP SET _sow_bank2 = @bank_name, _sow_money2 = @bank_assets, _sow_date2 = @changed_date  
where rmp._internID = @internID and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

UPDATE RMP SET _sow_bank3 = @bank_name, _sow_money3 = @bank_assets, _sow_date3 = @changed_date
where rmp._internID = @internID and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

UPDATE RMP SET _sow_bank4 = @bank_name, _sow_money4 = @bank_assets, _sow_date4 = @changed_date
where rmp._internID = @internID  and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

UPDATE RMP SET _sow_bank5 = @bank_name, _sow_money5 = @bank_assets, _sow_date5 = @changed_date
where rmp._internID = @internID  and rmp._year = Year (today)
FETCH NEXT FROM query INTO @bank_name, @bank_assets, @changed_date;

END;

CLOSE query;
DEALLOCATE query;

END;
0
 
Vasi04Author Commented:
Hi ALex, Your earlier code "with insert into" works fine when I try to insert into a temp tabel. But I try to insert into rmp tbale, sql server comes up with converting error msg like ...cannot convert data type varchar to real or data type float to ..

Actually insert into statment overwrites existing data in rmp, it does not append. so this is fine.

Also I was getting this error:
...Cannot insert null into column _SQL, table dbo.rmp, column does not allow null. INSERT fails.

That was reason I was thinking about update instead of insert into. But if you help me me with converting data type error, I guess I would be almost solving the problem

Any idea? Thanks so much
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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