Pl sql

[Former Member]

Hi Can I use a pl sql statement like this one to get a report out of CRXI?

I tried to use next code, but It failed.

Declare

v_name varchar2(8);

Begin

Select dhname into v_name

from table1

where dhname like 'ZFN0001';

dbms_output.put_line('Name: '||v_name);

end;

/

Thanks

Maria

[Former Member · Accepted solution]

Hi,

If you are doing this in stored procedure/DB function then it work, if you are doing this in the command object of the crystal report then it wont coz there is a ; which crystal assumes as a invalid character.

Moreover what exactly you want to achieve here? The example you have written is to just output the line on the PL SQL window, you need to select the value like.

Declare

v_name varchar2(8);

Begin

Select dhname into v_name

from table1

where dhname like 'ZFN0001';

Select 'Name: ' ||v_name from dual; -- Here is the change.

end;

Alternatively, you can use a case statement in your query to get the result you want as below.

Select case when dhname like '%ZFN0001%' then 'Name: '||dhname end as Name from table1

I have added those % signs because I assume you want to get the records which includes 'ZFN0001' in the name, you can remove % sign if you want the exact match.

Hope this helps, do not hesitate to ask further if needed.

Thanks

-Azhar