NetNews Usenet Archive 1992 #18/NN_1992_18.iso/spool/comp/database/oracle/1282

Internet Message Format | 1992-08-13 | 3.1 KB

Path: sparky!uunet!cs.utexas.edu!sdd.hp.com!zaphod.mps.ohio-state.edu!pacific.mps.ohio-state.edu!linac!unixhub!tethys.SLAC.Stanford.EDU!ian
From: [e-mail] (Ian A. MacGregor)
Newsgroups: comp.databases.oracle
Subject: Re: Forms3 Delete a char of the field data
Message-ID: <[e-mail]>
Date: 13 Aug 92 16:39:02 GMT
References: <[e-mail]>
Sender: [e-mail]
Lines: 53
Nntp-Posting-Host: tethys.slac.stanford.edu

In article <[e-mail]>, [e-mail] writes:
|> Greetings,
|>
|> I am using sqlforms version 3.0.
|> I have a problem which I don't even know where to start.  I want to delete a
|> char of the enter data into a field.
|> eg. Let 's say I have a field that requires data
|> HULL: _________; where the users will enter something like HULL: LST-1183,
|> I want to delete the "-" sign/char.
|> I want the data to be in the form of 'LST1183', but because of the way our data
|> bases are set up, the users will enter the HULL in the form of 'LST-1183'. I
|> want to delete the "-" sign/char, and also at the time somehow join the
|> LST 1183 together so that it is like the user had just entered 'LST1183' and
|> not 'LST-1183'.
|> I need to convert 'LST-1183' into 'LST1183', so that it corresponds with the
|> database HULL to do the query.
|>
|> I hope I have made myself clear.  Anyone who is interested, but didn't quite
|> undertand me, please feel free to ask.  I will be more than happy to make
|> myself clear.
|>
|> Any comments or suggestions will be appreciated.  Thanks in advance.

How far along are you on this project?  There appears to be a basic error in
design in that the HULL field is not atomic, but includes two different pieces
of information; the class of ship and its hull number.  These should be separate
fields.  This will make queries looking for all LST's, DDG's, FF's, etc. easier.
You can concatenate the two fields on output or to store the class/hull number
combination in another database field.

If your database design is immutable and you need to strip out the '-' character,
the following trigger will do the trick.

 BEGIN
    IF (INSTR(:NAVY.HULL,'-') > 0) THEN
      :NAVY.HULL := SUBSTR(:NAVY.HULL,1,
      INSTR(:NAVY.HULL,'-') -1)||
      SUBSTR(:NAVY.HULL,INSTR(:NAVY.HULL,'-')+1,
      LENGTH(:NAVY.HULL));
    END IF;
 End;


This code should be placed in an on-validate-trigger on the class_hull field
and on a pre-query trigger on the block.

Note: the use of this code will result in the '-' disappearing from the screen
field as well.

                            Ian MacGregor
                            Stanford Linear Accelerator Center
                            [e-mail]
                            [phone]