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

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

convert access boolean field value to oracle field value

We have a dynamic series of insert queries that get run on an Access DB from dotnet program. We have duplicated the tables in Oracle (don't ask why) and these table need to get updated with the same queries. One problem I've seen so far is the boolean (Yes/No) Access fields we have converted to char(1) fields in Oracle so when it tries to insert using the query:
INSERT INTO ITEMMASTER
(StoreNum, BatchNumber, FileType, SKU, DESCRIPTION, ACTIVE, PRD_LVL_CHILD, PRD_LVL_PARENT)
VALUES (427, '2009051819010600', 'I', 29254, 'Kool Family B1G1F Sng Pk-Test-Inactive', -1, 27480, 205)

for example the -1 doesn't fit in the char(1) field. Can we do something like put a trigger on the ACTIVE field and convert the -1 value into '1' so it will fit?
0
bmutch
Asked:
bmutch
  • 5
  • 3
  • 3
2 Solutions
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you could use NUMBER(1) instead of CHAR(1) ,  or use ABS(-1) to save 0 and 1 instead of 0 and -1,,,
0
 
JonasMalmstenCommented:
When using oracle, a char(1) that equals 'Y' or 'N' is the most common way to represent a boolean value.

You can translate into this using Decode(value, '0', 'N', '-1', 'Y', null)

in your example that would be Decode(-1, '0', 'N', '-1', 'Y', null)
0
 
bmutchAuthor Commented:
This is going to be occurring in vb code and it will be a pain to have to parse the query string and replace the value, I will have to hunt and peck at the string to find the "boolean" field location for the given table in the string, I am reading these queries in from a text file, then I have to run each query in Oracle via ado.net. So is there any way to convert the value in a trigger?
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>So is there any way to convert the value in a trigger?
no, as the trigger will happen AFTER the "-1" is rejected by the basic data type test against CHAR(1)

your only choice is to use VARCHAR2(2) instead, and then you could use the trigger to remove the "-" from "-1"
0
 
JonasMalmstenCommented:
What she said, for example:

create table test (b varchar2(2));

create or replace trigger tr_iu_test
before insert or update on test
for each row
begin  
  select decode(:new.b, '-1', 'N', '0', 'Y', null) into :new.b from sys.dual;
end;
/
0
 
JonasMalmstenCommented:
or even better:

create or replace trigger tr_iu_test
before insert or update on test
for each row
begin  
  select decode(:new.b, '-1', 'N', 'N', 'N', '0', 'Y', 'Y', 'Y', null) into :new.b from sys.dual;
end;
/
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>What she said ...
correction needed:
>What he said ...
without offense, btw :)

0
 
JonasMalmstenCommented:
ops, appologies :)
0
 
bmutchAuthor Commented:
thanks , can you possibly explain this in the trigger:
for each row ...

this doesn't mean it checks each row of the table every time there is an insert does it?
Concerned about performance.
0
 
JonasMalmstenCommented:
It means that the trigger will execute for each row that you insert or update, not for each row in the whole table.

If you ommit "for each row", the trigger will only fire once for each insert statement (that can possibly affect multiple rows). Consequently, if you ommit it you will not be able to access the :new.b variable.
0
 
bmutchAuthor Commented:
ok, very helpful, thanks both.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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