UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Convert Function Result To A View?, Any Versions    
 
   
LilAnnCC1
post Yesterday, 02:40 PM
Post#1



Posts: 796
Joined: 31-May 04
From: Wisconsin, USA


Using SQL Server 2017

Hello all! I created this function (it took me all day) and it works (I, know, it's shocking) that creates a Mailing Address Block for all the addresses we store in our database.

Addresses rarely change once they are entered into the database correctly, so I'm wondering if it wouldn't be better to either store the Address Block in the address table or create a view having the AddressID and the AddressBlock in the view. We need the Address Block on just about every report so this function would have to run many times. I'm thinking it would be more efficient to just store it.

If you all agree that storing it in the address table or a view is fine, then I would really appreciate your help on how to set up this function to update the existing records in the database--over 11,000 addresses.

Here is my function:

CODE
ALTER Function [dbo].[fnAddressBlockUSA](
        @AddressID int
)
Returns nvarchar(max)
AS
BEGIN
        DECLARE
        @A1 nvarchar(max),
        @A2 nvarchar(max),
        @A3 nvarchar(max),
        @A4 nvarchar(max),
        @A5 nvarchar(max),
        @A6 nvarchar(max)

    Set @A1=(SELECT IsNull(a.AltName,p.Company) FROM tAddress a INNER JOIN tPeople p on a.PeopleID=p.ID WHERE a.ID=@AddressID)    

    Set @A2=(SELECT a.Attention FROM tAddress a WHERE a.ID=@AddressID)

    SET @A3=(SELECT a.Address1 FROM tAddress a WHERE a.ID=@AddressID)

    SET @A4=(SELECT a.Address2 FROM tAddress a WHERE a.ID=@AddressID)

    SET @A5=(SELECT a.Address3 FROM tAddress a WHERE a.ID=@AddressID)

    SET @A6=(SELECT Concat(c.City, ' ', s.[State], ' ', a.Zip)
        FROM tAddress a
        LEFT JOIN tCity c on a.CityID=c.ID
        LEFT JOIN tTaxState s on a.StateID=s.ID
        WHERE a.ID=@AddressID)


    Return
            NULLIF(COALESCE(CASE WHEN LEN(RTRIM(@A1))>0 THEN
                        LTRIM(RTRIM(@A1))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(@A2))>0 THEN
                        LTRIM(RTRIM(@A2))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(@A3))>0 THEN
                        LTRIM(RTRIM(@A3))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(@A4))>0 THEN
                        LTRIM(RTRIM(@A4))END + char(13) + char(10),'')+    
                    COALESCE(CASE WHEN LEN(RTRIM(@A5))>0 THEN
                        LTRIM(RTRIM(@A5))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(@A6))>0 THEN
                        LTRIM(RTRIM(@A6))END + char(13) + char(10),''),'')
    
END
GO


I have another function that I use for International Addresses as well. I use the CountryID in the Address table to determine which function to call.


Open to any suggestions! Thank you!


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
GroverParkGeorge
post Yesterday, 05:23 PM
Post#2


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


Have you considered a calculated column in the address table?


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LilAnnCC1
post Yesterday, 07:11 PM
Post#3



Posts: 796
Joined: 31-May 04
From: Wisconsin, USA


That would be fantastic! But the some of the fields actually come from other tables and I thought you couldn't involve other tables. If there is a way, can you please point me to where I could find instructions for this?

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
GroverParkGeorge
post Yesterday, 08:25 PM
Post#4


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


Unfortunately, no, that won't work then. You store city and state separately from the addresses? That makes it harder.

But given that, I wonder if it might make sense to consider doing this as a view instead. The only reason it's a function is t create a set of variables that you concatenate into a final block. I think you can do that entirely within a view, using CTE's for the elements instead of the variables.

Without a sample of the tables, it's harder to come up with potential syntax, but let's see what we can do.


--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LilAnnCC1
post Yesterday, 08:53 PM
Post#5



Posts: 796
Joined: 31-May 04
From: Wisconsin, USA


Address table is:
ID--PK
AddressTypeID--FK to tAddressType
PeopleID--FK to tPeople
StoreID--FK to tStore (shipping locations for a PeopleID)--My function did not include this, but I will have to include it eventually. If I can get a good handle on it, I should be able to add it later.
CountryID--FK to tCountry (USA ID is 13)
AltName--text
Attention--text
Address1--text
Address2--text
Address3--Text
CityID--FK to tCity
StateID--FK to tTaxState
Zip--text
International--text (Used for international addresses. City, State and Zip will be null if this column is used)
Other columns not related to address block

Please keep in mind that I'm really very new to SQL Server and I have read about CTE, but have never actually done one. I have created views (and I love them).

I really appreciate your help!


--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
GroverParkGeorge
post Yesterday, 09:40 PM
Post#6


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


Try this view.

It doesn't have a WHERE clause, but you can easily add it in stored proc, if needed.
CODE
With A1 AS
(SELECT a.ID AS AddieID, a.PeopleID , a.AltName,p.Company  AS CoName,   a.Attention ,  a.Address1 , A.Address2, A.Address3  FROM tAddress a INNER JOIN tPeople p on a.PeopleID=p.ID) ,

A2 AS
(SELECT A.ID As AddieID, a.PeopleID, Concat(c.City, ' ', s.[State], ' ', a.Zip) AS CityStateZIP
        FROM tAddress a
        LEFT JOIN tCity c on a.CityID=c.ID
        LEFT JOIN tTaxState s on a.StateID=s.ID)
    SELECT
    
           NULLIF(COALESCE(CASE WHEN LEN(RTRIM(A1.CoName))>0 THEN
                        LTRIM(RTRIM(A1.CoName))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(A1.Address1))>0 THEN
                        LTRIM(RTRIM(A1.Address1))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(A1.Address1))>0 THEN
                        LTRIM(RTRIM(A1.Address1))END + char(13) + char(10),'')+
                    COALESCE(CASE WHEN LEN(RTRIM(A2.CityStateZIP))>0 THEN
                        LTRIM(RTRIM(A2.CityStateZIP))END + char(13) + char(10),'')
      ,'')

  FROM A1 INNER JOIN  A2 on A1.AddieID = A2.AddieID

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 
LilAnnCC1
post Yesterday, 09:59 PM
Post#7



Posts: 796
Joined: 31-May 04
From: Wisconsin, USA


Thank you so much for getting me started, George! Off to weave your magic into my new view!

Have a great weekend!

--------------------
I am eternally grateful to all the people here who continually help get me out of jams!

Access 2010
Go to the top of the page
 
MadPiet
post Yesterday, 10:03 PM
Post#8



Posts: 3,120
Joined: 27-February 09



If you're using a stored procedure to add this record to your table, then you could create a variable inside your stored procedure and assign it a value by doing something like:

DECLARE @CompleteAddress VARCHAR(200);
SET @CompleteAddress = fnAddressBlockUSA(…);

INSERT INTO Table (field1, field2,CompleteAddress)
VALUES (@Address1, Address2, @CompleteAddress);
Go to the top of the page
 
GroverParkGeorge
post Yesterday, 10:19 PM
Post#9


UA Admin
Posts: 35,123
Joined: 20-June 02
From: Newcastle, WA


Actually, this can be simplified, also used as a stored Procedure.

CODE
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
Alter PROCEDURE [spAddressBlockUSA]

    @AddressID  int = 0
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

With A1 AS
(SELECT A.ID As AddieID, a.PeopleID, a.AltName, p.Company  AS CoName,   a.Attention ,  a.Address1 , A.Address2, A.Address3 , Concat(c.City, ' ', s.[State], ' ', a.Zip) AS CityStateZIP
        FROM tAddress a
        INNER JOIN tPeople p on a.PeopleID=p.ID
        LEFT JOIN tCity c on a.CityID=c.ID
        LEFT JOIN tTaxState s on a.StateID=s.ID)
    SELECT
        NULLIF(COALESCE(CASE WHEN LEN(RTRIM(A1.CoName))>0 THEN
        LTRIM(RTRIM(A1.CoName))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.Address1))>0 THEN
        LTRIM(RTRIM(A1.Address1))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.Address1))>0 THEN
        LTRIM(RTRIM(A1.Address1))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.CityStateZIP))>0 THEN
        LTRIM(RTRIM(A1.CityStateZIP))END + char(13) + char(10),'')
          ,'')
  FROM A1
  WHERE A1.AddieID = @AddressID  
END
GO



Or, Create a view:

CODE
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE VIEW [dbo].[vwAddressBlock]
AS
With A1 AS
(SELECT A.ID As AddieID, a.PeopleID, a.AltName, p.Company  AS CoName,   a.Attention ,  a.Address1 , A.Address2, A.Address3 , Concat(c.City, ' ', s.[State], ' ', a.Zip) AS CityStateZIP
        FROM tAddress a
        INNER JOIN tPeople p on a.PeopleID=p.ID
        LEFT JOIN tCity c on a.CityID=c.ID
        LEFT JOIN tTaxState s on a.StateID=s.ID)
    SELECT
    A1.AddieID,
        NULLIF(COALESCE(CASE WHEN LEN(RTRIM(A1.CoName))>0 THEN
        LTRIM(RTRIM(A1.CoName))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.Address1))>0 THEN
        LTRIM(RTRIM(A1.Address1))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.Address1))>0 THEN
        LTRIM(RTRIM(A1.Address1))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.CityStateZIP))>0 THEN
        LTRIM(RTRIM(A1.CityStateZIP))END + char(13) + char(10),'')
          ,'') AS AddressBlock
  FROM A1
GO


And a stored procedure to use it:
CODE
/****** Object:  StoredProcedure [dbo].[spAddressBlockUSA]    Script Date: 6/14/2019 8:18:47 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



ALTER PROCEDURE [dbo].[spAddressBlockUSA]

    @AddressID  int = 0
    
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;
Select *
FROM vwAddressBlock

  WHERE vwAddressBlock.AddieID = @AddressID  
END
GO


This allows you to use the view if you want to see the entire list, or the stored procedure to select one address.
This post has been edited by GroverParkGeorge: Yesterday, 10:20 PM

--------------------
My Real Name Is George. Grover Park Consulting is where I do business.
How to Ask a Good Question
Beginning SQL Server
Go to the top of the page
 


Custom Search


RSSSearch   Top   Lo-Fi    15th June 2019 - 10:13 PM