Home » Developer & Programmer » Reports & Discoverer » string convertion
string convertion [message #271899] Wed, 03 October 2007 05:55 Go to next message
brintha
Messages: 28
Registered: August 2007
Junior Member
hi,
can any one help me in solving my issue... I have a userparameter and if its value is 'Begins with~xxx' i have to append % at the end and check that with my query. But it is not working. The following is the code i used.


function CF_1Formula return CHAR is
S_CHAR VARCHAR(200):='AAA';
P_CHAR VARCHAR(200):='AAA';
NUM_COUNT NUMBER;
begin

SELECT LTRIM((REPLACE(REPLACE(:P_DUMMY_COUNTRY,'BEGINS WITH~',''),'''',''))||'%') INTO S_CHAR FROM DUAL; 
SELECT COUNT(DISTINCT SHIPMENT_GID)INTO NUM_COUNT FROM SHIPMENT WHERE SHIPMENT_GID LIKE TO_CHAR(S_CHAR);

RETURN  NUM_COUNT;
end;



all spaces are removed in the first select code and % is appended at the end and this value is used in the where condition of the 2nd query. But the like is not working and count is zero.

regards,
brintha

Re: string convertion [message #271927 is a reply to message #271899] Wed, 03 October 2007 06:51 Go to previous messageGo to next message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why is your function returning CHAR when you are calculating the COUNT? This function (COUNT, i.e.) returns NUMBER, not CHARACTER.

What are those 'AAA's doing here?

's_char' is declared as a CHARACTER variable. Why do you convert it to character again, using the TO_CHAR function?

I must admit I didn't quite understand what is your first SELECT statement doing ...

But never mind me. Here's how I understood your question; I believe it is simple enough and doesn't need to be explained.
FUNCTION cf_1 
  RETUN NUMBER
IS
  num_count NUMBER := 0;
BEGIN
  IF :p_dummy_country = 'BEGINS WITH ~'
  THEN
    SELECT COUNT(DISTINCT shipment_gid)
      INTO num_count
      FROM shipment
      WHERE shipment_gid LIKE 'BEGINS WITH ~%';
  END IF;
		
  RETURN (num_count);
END;
Re: string convertion [message #271951 is a reply to message #271899] Wed, 03 October 2007 08:02 Go to previous messageGo to next message
brintha
Messages: 28
Registered: August 2007
Junior Member
HI,
what u understood is not exact the way how my doubt is. this is what i want:

FUNCTION cf_1
RETUN NUMBER
IS
num_count NUMBER := 0;
BEGIN
IF :p_dummy_country = 'BEGINS WITH ~test'
THEN
SELECT COUNT(DISTINCT shipment_gid)
INTO num_count
FROM shipment
WHERE shipment_gid LIKE 'test%';
END IF;

RETURN (num_count);
END;

this is how i want., csn u please help me out...

[Updated on: Wed, 03 October 2007 08:04]

Report message to a moderator

Re: string convertion [message #271991 is a reply to message #271951] Wed, 03 October 2007 10:35 Go to previous message
Littlefoot
Messages: 20888
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Please, read the OraFAQ Forum Guide to learn how to properly format a message.

Now I don't follow you; are you saying that the beginning of the string is 'BEGINS WITH ~', while the rest of it may be anything (for example, 'BEGINS WITH ~TEST' or 'BEGINS WITH ~LITTLEFOOT HAS LITTLE FOOT' etc.)?

If so, test for
SUBSTR(:p_dummy_country, 1, 13) = 'BEGINS WITH ~'

[Updated on: Wed, 03 October 2007 10:36]

Report message to a moderator

Previous Topic: calling report from forms where report is in another folder
Next Topic: HELP! TRYING TO CREATE A DISCOVERER REPORT USING SQL
Goto Forum:
  


Current Time: Fri Dec 02 18:28:05 CST 2016

Total time taken to generate the page: 0.43969 seconds