How to use subquery fetch data in Vertica

gg1234

Dear All,
Could anyone help to suggest?
I would like to create view from Sql Server in Vertica, but the script using a user define function
How can I use subquery for loop matching data instead of function?

#

select t3.UserID ,
t1.UserrProjectID,
t1.ProjectID,
fGetOptions('Name',t1.UserProjectID,9) AS Member ,
fGetOptions('Name',t1.UserProjectID,10) AS Salary,
FROM t1
INNER JOIN t2 ON t1.ProjectID = t2.ProjectID
INNER JOIN t3 ON t1.UserID = t3.UserID
Left Join t4 On t1.UserProjectID = t3.UserProjectID
And t3.Status = 'A'

#

inside the function fGetOptions
CREATE FUNCTION [fGetOptions] (@Name varchar(50) ,@UserProjectID int , @DescriptionID int )
RETURNS Varchar(500) AS

BEGIN
DECLARE @ReturnResult varchar(500)
SET @ReturnResult = ""

If @Name = "'Name'"
Begin               
    Set @ReturnResult = 
                    Isnull((SELECT TOP 1 t6.DescriptionName
                    FROM  t4
                    INNER JOIN t5 ON t4.ID = t5.ID And t5.Status = 'A'
                    INNER JOIN t6 ON  t5.DescriptionID = t6.DescriptionID 
                                           And t6.Status = 'A'
                    WHERE 1 = 1
                    AND t5.IsAnswer = 1
                    AND t5.DescriptionID = @DescriptionID
                    AND t4.UserProjectID = @UserProjectID),'')
End

#

Thank you,

Siddhi_Lal_Kaye

Hi,
you can create function to work on queries. More details at https://www.vertica.com/docs/9.2.x/HTML/Content/Authoring/ExtendingVertica/UDF-SQLFunctions/CreatingUser-DefinedSQLFunctions.htm