[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 137
  • Last Modified:

How can I get this query to run in a function?

ALTER function [cd].[address_label] (@type varchar(15), @program varchar(5))


returns table
as
return(
---How can I get this query to work in this function?
--Query Start
if @type not in ('primary','client mailing') 
select 
(case when ec.contact_vc is null then cl.firstname_vc+' '+cl.lastname_vc else ec.contact_vc end) as name,
       (case when ea.address1_vc is null then ep.address1_vc else ea.address1_vc end) as address1,
       (case when ea.address1_vc is null then ep.address2_vc else ea.address2_vc end) as address2,
       (case when ea.address1_vc is null then ep.city_vc else ea.city_vc end) as city,
       (case when ea.address1_vc is null then ep.state_c else ea.state_c end) as state,
       (case when ea.address1_vc is null then ep.zip_c else ea.zip_c end) as zip                    

from ar.client cl 
join echo.er_contact ec on ec.linkid_c = cl.uniqueid_c 
                            and ec.source_c = 'client' and (ec.type_c = @type or ec.type_c is null)
                            and (ec.startdate_d <= getdate() or ec.startdate_d is null)
                            and (ec.enddate_d is null or ec.enddate_d > getdate())
left join echo.er_address ep on ep.linkid_c = cl.uniqueid_c 
                            and ep.source_c = 'client' and ep.type_c = 'primary'
                            and (ep.startdate_d <= getdate() or ep.startdate_d is null)
                            and (ep.enddate_d is null or ep.enddate_d > getdate())
left join echo.er_address ea on ea.linkid_c = cl.uniqueid_c and (ea.country_vc = ec.priority_c or ea.country_vc is null)
                            and ea.source_c = 'client' and ea.type_c = @type
                            and (ea.startdate_d <= getdate() or ea.startdate_d is null)
                            and (ea.enddate_d is null or ea.enddate_d > getdate())
where cl.termdate_d is null
and (exists (select 1 from cd.enrollments where (program_c = @program and clientid_c = cl.uniqueid_c and 
                                                 (enddate_d is null or (enddate_d <= getdate())))) 
     or @program = 'all')


	 if @type in ('primary','client mailing')
select 
 cl.firstname_vc+' '+cl.lastname_vc  as name,
       (case when ea.address1_vc is null then ea.address2_vc else ea.address1_vc end) as address,
       --(case when ea.address1_vc is null then ep.address2_vc else ea.address2_vc end) as address2,
        ea.city_vc  as city,
        ea.state_c  as state,
        ea.zip_c  as zip                    

from ar.client cl 
--Took out some fluff because when we look for Primary thats all we want is primary..... and we do not nee contact info for the primary address
 join echo.er_address ea on ea.linkid_c = cl.uniqueid_c --and (ea.country_vc = ec.priority_c or ea.country_vc is null)
                            and ea.source_c = 'client' and ea.type_c = @type
                            and (ea.startdate_d <= getdate() or ea.startdate_d is null)
                            and (ea.enddate_d is null or ea.enddate_d > getdate())
where cl.termdate_d is null
and (exists (select 1 from cd.enrollments where (program_c = @program and clientid_c = cl.uniqueid_c and 
                                                 (enddate_d is null or (enddate_d <= getdate())))) 
     or @program = 'all')

	 --End of Query
)

Open in new window

0
mguptill
Asked:
mguptill
  • 2
1 Solution
 
mguptillAuthor Commented:
One thing I forgot to mention is the Variables are pulled from the App the fires this function off...

I'd like to declare @type = @type etc
0
 
ste5anSenior DeveloperCommented:
You need a Table-Valued User-Define Function (TVF) instead of a Inline User-Defined Function.

E.g.
ALTER FUNCTION [cd].[address_label]
    (
      @type VARCHAR(15) ,
      @program VARCHAR(5)
    )
RETURNS @Result TABLE
    (
      name NVARCHAR(255) ,
      address1 NVARCHAR(255) ,
      address2 NVARCHAR(255) ,
      city NVARCHAR(255) ,
      [state] NVARCHAR(255) ,
      zip NVARCHAR(255)
    )
AS
    BEGIN
        IF @type NOT IN ( 'primary', 'client mailing' )
            INSERT  INTO @Result
                    ( name ,
                      address1 ,
                      address2 ,
                      city ,
                      [state] ,
                      zip
                    )
                    SELECT  ( CASE WHEN ec.contact_vc IS NULL THEN cl.firstname_vc + ' ' + cl.lastname_vc
                                   ELSE ec.contact_vc
                              END ) AS name ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.address1_vc
                                   ELSE ea.address1_vc
                              END ) AS address1 ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.address2_vc
                                   ELSE ea.address2_vc
                              END ) AS address2 ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.city_vc
                                   ELSE ea.city_vc
                              END ) AS city ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.state_c
                                   ELSE ea.state_c
                              END ) AS state ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ep.zip_c
                                   ELSE ea.zip_c
                              END ) AS zip
                    FROM    ar.client cl
                            JOIN echo.er_contact ec ON ec.linkid_c = cl.uniqueid_c
                                                       AND ec.source_c = 'client'
                                                       AND ( ec.type_c = @type
                                                             OR ec.type_c IS NULL
                                                           )
                                                       AND ( ec.startdate_d <= GETDATE()
                                                             OR ec.startdate_d IS NULL
                                                           )
                                                       AND ( ec.enddate_d IS NULL
                                                             OR ec.enddate_d > GETDATE()
                                                           )
                            LEFT JOIN echo.er_address ep ON ep.linkid_c = cl.uniqueid_c
                                                            AND ep.source_c = 'client'
                                                            AND ep.type_c = 'primary'
                                                            AND ( ep.startdate_d <= GETDATE()
                                                                  OR ep.startdate_d IS NULL
                                                                )
                                                            AND ( ep.enddate_d IS NULL
                                                                  OR ep.enddate_d > GETDATE()
                                                                )
                            LEFT JOIN echo.er_address ea ON ea.linkid_c = cl.uniqueid_c
                                                            AND ( ea.country_vc = ec.priority_c
                                                                  OR ea.country_vc IS NULL
                                                                )
                                                            AND ea.source_c = 'client'
                                                            AND ea.type_c = @type
                                                            AND ( ea.startdate_d <= GETDATE()
                                                                  OR ea.startdate_d IS NULL
                                                                )
                                                            AND ( ea.enddate_d IS NULL
                                                                  OR ea.enddate_d > GETDATE()
                                                                )
                    WHERE   cl.termdate_d IS NULL
                            AND ( EXISTS ( SELECT   1
                                           FROM     cd.enrollments
                                           WHERE    ( program_c = @program
                                                      AND clientid_c = cl.uniqueid_c
                                                      AND ( enddate_d IS NULL
                                                            OR ( enddate_d <= GETDATE() )
                                                          )
                                                    ) )
                                  OR @program = 'all'
                                )


        IF @type IN ( 'primary', 'client mailing' )
            INSERT  INTO @Result
                    ( name ,
                      address1 ,
                      city ,
                      [state] ,
                      zip
                    )
                    SELECT  cl.firstname_vc + ' ' + cl.lastname_vc AS name ,
                            ( CASE WHEN ea.address1_vc IS NULL THEN ea.address2_vc
                                   ELSE ea.address1_vc
                              END ) AS address ,
                            ea.city_vc AS city ,
                            ea.state_c AS state ,
                            ea.zip_c AS zip
                    FROM    ar.client cl
                            JOIN echo.er_address ea ON ea.linkid_c = cl.uniqueid_c
                                                       AND ea.source_c = 'client'
                                                       AND ea.type_c = @type
                                                       AND ( ea.startdate_d <= GETDATE()
                                                             OR ea.startdate_d IS NULL
                                                           )
                                                       AND ( ea.enddate_d IS NULL
                                                             OR ea.enddate_d > GETDATE()
                                                           )
                    WHERE   cl.termdate_d IS NULL
                            AND ( EXISTS ( SELECT   1
                                           FROM     cd.enrollments
                                           WHERE    ( program_c = @program
                                                      AND clientid_c = cl.uniqueid_c
                                                      AND ( enddate_d IS NULL
                                                            OR ( enddate_d <= GETDATE() )
                                                          )
                                                    ) )
                                  OR @program = 'all'
                                )
        RETURN;
    END;

Open in new window


p.s. please use the Embed Code Snippet button from the toolbar in future posts. Also post formatted code. E.g. using an online formatter like T-SQL Tidy.
0
 
mguptillAuthor Commented:
This worked perfectly thank you so much!!!!!!!
0

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now