The Vertica Forum recently got a makeover! Let us know what you think by filling out this short, anonymous survey.
Please take this survey to help us learn more about how you use third party tools. Your input is greatly appreciated!
How to use subquery fetch data in Vertica

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,
Tagged:
0
Answers
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