Do your homework.
Post what you've done if you are stuck.
We help you to go on.
On 9 Apr 2007 05:49:59 -0700, "Usman" <khanus
@gmail.com> wrote:
>Hi,
>I am a newbee to Oracle SQL programming. I have a problem where I have
>three columns, A - varchar, B-varchar and C-numeric
>Column A can contain alphanumeric character.
>Column B will always contain numeric characters.
>I need to write a trigger that will on every row inserted,
>1. Check if column A exists then,
>2. If 1 is true, convert each alphabet in column A to its equivalent
>ASCII and leave the numeric characters as it is. Therefore, for
>example if I have 'A567', I should have 67567 as an end result,
>assuming ascii code of 'A' is 67.
>3. Concatenate converted string in 2 above with data inserted in
>column B.
>4. Convert to number/long in 3 above and store the data in column C.
>Example:
>column A = 'A567'
>column B= '1234'
>column C = 675671234
>Can anyone please help me implementing this logic?
>Any help in this regard will be appreciated.
>Thanks in anticipation,
>Usman
You have the algorithm, you have access to the manuals, you only need
to look up how to convert an ascii character to it's numeric
representation, and how to convert it to a string and to concatenate
it to another string, so why don't do it yourself. Especially since I
already gave away 80 percent of the solution.
--
Sybrand Bakker
Senior Oracle DBA
-----------------------------------------------Reply-----------------------------------------------
Usman wrote:
> Hi,
> I am a newbee to Oracle SQL programming. I have a problem where I have
> three columns, A - varchar, B-varchar and C-numeric
Wonderful. Then start by getting the names of the data types in Oracle
correct. You do not have a VARCHAR2 column in a table.
> Column A can contain alphanumeric character.
> Column B will always contain numeric characters.
> I need to write a trigger that will on every row inserted,
> 1. Check if column A exists then,
Check if the column exists or the value exists? If the later then I
think you should hit
http://tahiti.oracle.com and look up "Mutating
Triggers."
> 2. If 1 is true, convert each alphabet in column A to its equivalent
> ASCII and leave the numeric characters as it is. Therefore, for
> example if I have 'A567', I should have 67567 as an end result,
> assuming ascii code of 'A' is 67.
http://www.psoug.org click on built-in functions
scan the list for the functions that will accomplish that goal
> 3. Concatenate converted string in 2 above with data inserted in
> column B.
You will find that on the same page.
> 4. Convert to number/long in 3 above and store the data in column C.
This makes no sense. What is number/long?
> Example:
> column A = 'A567'
> column B= '1234'
> column C = 675671234
> Can anyone please help me implementing this logic?
Just did.
> Any help in this regard will be appreciated.
> Thanks in anticipation,
> Usman
We don't do homework but we do offer hints. Post your work.
--
Daniel A. Morgan
University of Washington
damor
@x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Add to del.icio.us |
Digg this |
Stumble it |
Powered by Megasolutions Inc
