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 Jun 14 2019, 02:40 PM
Post#1



Posts: 805
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 Jun 14 2019, 05:23 PM
Post#2


UA Admin
Posts: 35,325
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 Jun 14 2019, 07:11 PM
Post#3



Posts: 805
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 Jun 14 2019, 08:25 PM
Post#4


UA Admin
Posts: 35,325
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 Jun 14 2019, 08:53 PM
Post#5



Posts: 805
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 Jun 14 2019, 09:40 PM
Post#6


UA Admin
Posts: 35,325
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 Jun 14 2019, 09:59 PM
Post#7



Posts: 805
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 Jun 14 2019, 10:03 PM
Post#8



Posts: 3,171
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 Jun 14 2019, 10:19 PM
Post#9


UA Admin
Posts: 35,325
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: Jun 14 2019, 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
 
LilAnnCC1
post Jun 17 2019, 08:26 AM
Post#10



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


Thank you so much, George! With your help, I was able to create my very first CTE and get my view. I had to add additional information to get both and Address Block and an Address Line. I was also able to incorporate the Store/Brand in address if needed.

Here is my final View:

CODE
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:    
-- Create date: 2019-06-17
-- Description:    Address Block View        
-- =============================================

Alter View vwAddressBlock
AS
With A1 AS

    (
    SELECT a.ID As AddID, a.PeopleID, a.StoreID, a.AddressTypeID, a.StatusID, IsNull(a.AltName,p.Company) As CoName,  
        Case When a.StoreID IS Not Null THEN Concat('STORE # ', StoreNo, ' ', Brand) ELSE Null End As Store,
        a.Attention, a.Address1, a.Address2, a.Address3,
        CASE When a.CountryID=13 Then
                CONCAT(c.City, ' ', s.[State], ' ', Zip)
        ELSE    
                CONCAT(a.International, + char(13) + char(10), Country)
        END AS CITYSTATEZIP,

        Case When a.CountryID<>13 then
            CONCAT(a.International, ' ', Country)
        END AS IntLine
        
    FROM tAddress a
    INNER JOIN tPeople p on a.PeopleID=p.ID
    INNER JOIN tCountry ct on a.CountryID=ct.ID
    LEFT JOIN tCity c on a.CityID=c.ID
        LEFT JOIN tTaxState s on a.StateID=s.ID
    LEFT JOIN tStore st on a.StoreID=st.ID
    LEFT JOIN tStoreBrand b on st.BrandID=b.ID
    
    )

    SELECT
        a1.AddID, A1.PeopleID, A1.StoreID, A1.AddressTypeID, A1.StatusID,
        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.Store))>0 THEN
            LTRIM(RTRIM(A1.Store))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.Attention))>0 THEN
            LTRIM(RTRIM(A1.Attention))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.Address2))>0 THEN
            LTRIM(RTRIM(A1.Address2))END + char(13) + char(10),'')+
        COALESCE(CASE WHEN LEN(RTRIM(A1.Address3))>0 THEN
            LTRIM(RTRIM(A1.Address3))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,
        CONCAT(ISNULL(A1.Address1, ''), ' ', ISNULL(A1.Address2, ''), ' ', ISNull(A1.Address3,''), ' ',
        ISNULL(a1.CityStateZip, a1.IntLine)) as AddressLine
                    
  FROM A1

  GO


Other than having extra spaces in the Address Line, all is perfect!

Quick question regarding the Stored Procedure. How do I get a list of all address in the view? I understand how to get just one-but I don't understand how to get all of them using that stored procedure. I tried leaving the variable blank, using a 0, etc.; but I can't get any results unless I actually supply and AddressID.

I so appreciate the time you spent teaching me!
This post has been edited by LilAnnCC1: Jun 17 2019, 08:50 AM

--------------------
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 Jun 17 2019, 08:58 AM
Post#11


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


I can think of at least two approaches that can either return all records or a filtered set.

One would be to write the stored procedure to accept the input variable, but then conditionally run one of two SELECT statements, one with the WHERE clause and one without a WHERE clause. Or perhaps just include the WHERE clause or a null string in place of the WHERE portion. That's a bit harder, IMO.

The other is two create three stored procs. One for the parameterized version of the stored proc, and one for the non-parameterized version, which could just be the view. Then add third stored proc, which calls one of the two others. If you pass a 0 into this "pilot" stored proc, it in turn runs the non-parameterized version, or if the variable is not 0, it runs the parameterized version.

I've done it both ways and probably would look at the second one primarily, but others may have different, and better solutions.

--------------------
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
 
cheekybuddha
post Jun 17 2019, 09:30 AM
Post#12


UtterAccess VIP
Posts: 11,424
Joined: 6-December 03
From: Telegraph Hill


Hi,

Or, just test the parameter in the WHERE clause:
CODE
IF object_id('getAddressBlock') > 0
  DROP PROCEDURE getAddressBlock;
GO
  
CREATE PROCEDURE getAddressBlock
  @address_id         INT
AS
BEGIN

  SELECT
    *
  FROM vwAddressBlock
  WHERE AddID = @address_id
     OR @address_id IS NULL
;

END

Pass an specific ID to the sp to get a single address block, or NULL to get all of them.

hth,

d

--------------------


Regards,

David Marten
Go to the top of the page
 
LilAnnCC1
post Jun 17 2019, 10:20 AM
Post#13



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


Thank you both, George and David!

I altered the procedure to accept either/or and I got it to work using Null or an actual ID.

You both have a fantastic day!

--------------------
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
 


Custom Search


RSSSearch   Top   Lo-Fi    22nd July 2019 - 05:41 PM