For eg can I write such query like Select location from xxxloc where location_id = @location_idformula

 

where @location_idformula is a formula which return a single numeric value.

 

Currently when I go and write a SQL Expression in the editor , I can only see table and fields but cannot see any formulas which I have created. This is a nuisance as I am not able to use the value which is extracted from elsewhere on the basis of some conditions.

 

Is there any way to use formulas in SQL EXPRESSIONS

 

I have developed a stored proc and in this there is an input 'IN' in the form loc_id from the location table and it returns the name as the output 'OUT'. Now the stored proc compiles fine on the sql editor and even it executes properly. But when I add the stored proc in the Crystal from Database Expert, a screen comes out prompting Enter Values for Loc_id.

 

I don't know the exact functionality or process how the stored proc is executed. According to my thought process I had thought that the stored proc's loc_id would be linked to the location table's loc_id and when the report will be executed the stored proc's loc_id would receive loc_id from the location table and then it would return the name on each page(my report is only of one page so it dispalys one page per loc_id) but now i suspect something else in the functionality.

 

Please guide me on the same topic and also suggest how to use the stored proc for the same purpose.

 

Given below is the stored proc that I have used

 

CREATE OR REPLACE PROCEDURE sps_name (
p_loc_id IN NUMBER,
p_name OUT VARCHAR2,
p_err_code_out OUT NUMBER,
p_err_mesg_out OUT VARCHAR2
)
IS
lv_sql VARCHAR2 (2000);
BEGIN
lv_sql :=
'((select name from (
select loc_id,name,row_number()over( order by r) rn from (
SELECT 0, loc_id, Misc1_txt NAME,''A'' STATUS ,rownum r
FROM location
WHERE loc_id ='
|| p_loc_id
|| '
union
SELECT parent_loc_id, loc_id, (SELECT a.Misc1_txt
FROM location a
WHERE a.loc_id =b.loc_id) NAME,''B'' ,ROWNUM
FROM locationpath b
START WITH b.loc_id = '
|| p_loc_id
|| '
CONNECT BY PRIOR parent_loc_id=loc_id
) where name is NOT NULL order by STATUS, R
) where rn = 1))';
EXECUTE IMMEDIATE lv_sql INTO p_name;
EXCEPTION
WHEN OTHERS
THEN
p_err_code_out := SQLCODE;
p_err_mesg_out :=
'Error in sps_provider_number ==> ' || SQLERRM;
raise_application_error (-20001, p_err_mesg_out, TRUE);
END sps_provider_number;
      

 

i created this function with some help from google and then a view and added it to the crystal reports and finally i got the data i required on my crystal report.

The above stored proc needed some tuning to make it generic and not specific for just one loc_id and I tried doing the same but it  did not work then i thought why not functions you input one you get an output for one and then i wrote down a select query calling the function with input value as the loc_id column of the table and that worked perfectly and my view was created

 

But thanks a lot and i'd definitely try working the same thing with a stored proc.

 

Thanks a lot for the guidance.