Click here to Skip to main content
14,920,001 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
while executing the following function i am getting
query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.error


can any one sort out this problem..

What I have tried:

SQL
CREATE OR REPLACE FUNCTION Udf_HelpWindomaster(
	p_Action     VARCHAR(5), 
	p_colname varchar(100),
	p_colvalue varchar(100),
    p_User_id  VARCHAR(10) , 
    p_Comapny  Integer	
	) returns refcursor
AS $$ 
	declare v_query TEXT; v_Addquery text ;query_name refcursor;

BEGIN 
       
          Declare v_Mastertype VARCHAR(100); 
          v_Status CHAR(1); 
          v_M_DataDesc VARCHAR(100); 
          v_M_DataShrtdesc VARCHAR(25);
  BEGIN
          IF p_Action = 'FE' 
            THEN 
				CREATE SEQUENCE TblColumn_seq;
				
                CREATE TEMP TABLE TblColumn
                  ( 
                     id INTEGER DEFAULT NEXTVAL ('TblColumn_seq') , 
                     Columns VARCHAR(100) 
                  );
  				CREATE SEQUENCE TblValue_seq;
                CREATE TEMP TABLE TblValue
                  ( 
                     id INTEGER DEFAULT NEXTVAL ('TblValue_seq') , 
                     Value TEXT 
                  );

               INSERT INTO TblColumn(Columns) 
               SELECT SplitString(p_Colname,'$');

               INSERT INTO TblValue(Value) 
               SELECT SplitString(p_Colvalue,'$');

                 SELECT v_Mastertype=a.Value   
                      FROM TblValue a inner join TblColumn b on a.id=b.id
                      WHERE  b.columns = 'Mastertype'; 

                 SELECT v_Mastertype=a.Value   
                      FROM  TblValue a Inner Join TblColumn b on a.Id=b.Id
                      WHERE  b.columns = 'Master_type'; 

                 SELECT v_M_DataShrtdesc=a.Value   
                      FROM  TblValue a Inner Join TblColumn b on a.Id=b.Id
                      WHERE  b.columns = 'M_DataShrtdesc'; 

                 SELECT v_Status=a.Value  
                      FROM  TblValue a Inner Join TblColumn b on a.Id=b.Id
                      Where b.columns = 'Status'; 
   
                v_Addquery := ''; 

                IF v_Mastertype <> '' THEN 
                  v_Addquery := v_Addquery || ' AND Mastertype=''' || v_Mastertype 
                                  || '''';
                END IF; 

                IF v_Status <> '' THEN 
                  v_Addquery := v_Addquery || ' AND Status=''' || v_Status || '''';
                END IF; 

                IF v_M_DataShrtdesc <> '' THEN 
                  v_Addquery := v_Addquery || ' AND M_DataShrtdesc=''' 
                                  || v_M_DataShrtdesc || '''';
                END IF; 

                 v_query := 'SELECT M_DataDesc [Master Data],
									Mastertype AS [Master Type],
									CASE WHEN Status=''L'' THEN ''Live'' 
										 WHEN Status=''D'' THEN ''DeActivated''  
										 WHEN Status=''R'' THEN ''ReActivated'' 
									 END Status,
									 M_DataShrtdesc  
							 FROM Los_HWMaster WITH (NOLOCK) WHERE 1=1 AND CompanyCode ='''
							|| TO_CHAR (p_p_Comapny) 
							|| '''   ';
				v_query := v_query || v_Addquery || ' ORDER BY LstMByDt DESC'; 

				open query_name for execute v_query;
				return query_name;
				
		END IF; 

	    IF p_Action = 'SE' 
			THEN 
				  v_Addquery := ''; 
				  v_Addquery := v_Addquery || ' AND Mastertype=''' || p_p_Colvalue 
								  || ''''; 
				  v_query := 
							'SELECT M_DataDesc ,OrderNo FROM Los_HWMaster WHERE 1=1  AND CompanyCode =''' 
							|| TO_CHAR (p_p_Comapny) || '''  '; 
				  v_query := v_query || v_Addquery || ' ORDER BY OrderNo'; 

				open query_name for execute v_query;
				return query_name;
	
		END IF; 
	END;
	
END ;$$ LANGUAGE plpgsql; 
Posted
Updated 17-Jul-20 2:50am
v2

1 solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900