OraFAQ Forum: Reports & Discoverer

Home » Developer & Programmer » Reports & Discoverer » string convertion

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

string convertion [message #271899]

Wed, 03 October 2007 05:55

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

Report message to a moderator

Re: string convertion [message #271927 is a reply to message #271899]

Wed, 03 October 2007 06:51

Littlefoot
Messages: 21823
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;

Report message to a moderator

Re: string convertion [message #271951 is a reply to message #271899]

Wed, 03 October 2007 08:02

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

Littlefoot
Messages: 21823
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:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Nov 26 19:32:11 CST 2024