[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

how to accept mutiple formats for inputing?

I am developing an Oracle 8i form. You know in Oracle form, you can query the result by doing "Enter->input into field-> run" to display  result


I have an input query field. So far it accept format like 28171110

Now I want it also accept 2817-1110, and L2817-1110

That means when input 2817-1110, and L2817-1110, it will automatically convert it to 28171110 format

How to do that?
0
wasabi3689
Asked:
wasabi3689
  • 5
  • 5
1 Solution
 
sdstuberCommented:
where are you doing your processing?  Inside the form? or in pl/sql on the server?

8i isn't a forms version,  they went from 6i to 9i and 10g.

0
 
sdstuberCommented:
If your database is 8i

then try this...
 your_field :=
        TRANSLATE(
            your_field,
            '0123456789' || TRANSLATE(your_field, CHR(0) || '0123456789', CHR(0)),
            '0123456789'
        );

Open in new window

0
 
wasabi3689Author Commented:
where I put this code to? under the Find button trigger or ...
The form version is 11.0.0

the field is license_number

Here is the image
find-download.JPG
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
sdstuberCommented:
wherever you want.  You could put it behind the find button, if you pass the string to a pl/sql procedure, you could put the code inside that procedure, you could pass it to a function get the stripped string back and pass that to whatever you want.

0
 
wasabi3689Author Commented:
I have the following error

Compiling WHEN-BUTTON-PRESSED trigger on FIND item in FIND_DOWNLOADS block...
Compilation error on WHEN-BUTTON-PRESSED trigger on FIND item in FIND_DOWNLOADS
block:
PL/SQL ERROR 303 at line 6, column 1
qualifier 'FIND_DOWNLOADS' must be declared
PL/SQL ERROR 0 at line 6, column 1
Statement ignored


attached the code is for Find button (when-button-pressed)
:parameter.G_query_find := 'TRUE';
app_find.find('XX_DOWNLOADS_V');
:parameter.G_query_find := 'FALSE';
 
 
find_downloads.license_number :=
        TRANSLATE(
            license_number,
            '0123456789' || TRANSLATE(your_field, CHR(0) || '0123456789', CHR(0)),
            '0123456789'
        );

Open in new window

0
 
wasabi3689Author Commented:
any ideas?
0
 
wasabi3689Author Commented:
I have the following code dealt with the same issue in other form. I don't know how to make an arrangment for it. Can you modify it and where it put to if you don't have any ideas.

    -- This will allow users to query by license numbers including the L or -
   
	IF :find_downloads.license_number IS NOT NULL THEN
        IF INSTR(:find_downloads.license_number,'L',1) > 0 OR INSTR(:find_downloads.license_number,'-',1)  > 0 THEN
	    :find_downloads.license_number := REPLACE(REPLACE(:find_downloads.license_number,'L',''),'-','');
        END IF;
        END IF;
 
 
 
    IF :parameter.G_query_find = 'TRUE' THEN      
       
        IF :find_downloads.license_number IS NOT NULL THEN
	    IF v_where_clause IS NULL THEN
	        v_where_clause := v_where_clause ||'license_number= '||replace(replace(:find_downloads.license_number,'L',''),'-','');
	    ELSE
	        v_where_clause := v_where_clause ||' and license_number= '||replace(replace(:find_downloads.license_number,'L',''),'-','');
	    END IF;
        END IF;
 
     END IF; 

Open in new window

0
 
sdstuberCommented:
how about something like this...  (You left out the colon, then left out the find_downloads then left the your_field in.  all 3 places need to be the full ":find_downloads.license_number"




:find_downloads.license_number :=
        TRANSLATE(
            :find_downloads.license_number,
            '0123456789' || TRANSLATE(:find_downloads.license_number, CHR(0) || '0123456789', CHR(0)),
            '0123456789'
        );


or, if L and - are the only special characters you'll ever need to worry about then you cand do the double replace as you did above
0
 
wasabi3689Author Commented:
excellent
0
 
sdstuberCommented:
glad I could help
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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