This is a little SQL puzzle I've been wrestling with for awhile now. I am getting requests from users for List and Output Set elements that mimic the functionality of FT_GET_ADDRESS. Namely, to return a control grouped email address, postal address, salutation based on a parameter selection and then return the primary value if the selected type doesn't exist.
I'm struggling with how to implement this as a view. I've built something for salutations but it is pretty kludgy. Essentially, I create the Cartesian results of T_CUSTOMER and VRS_SIGNOR and then make the needed joins to get the primary value when the control grouped salutation doesn't exist. This works ok in limited circumstances. But it definitely has the potential to generate several million rows if I use it in the wrong spots.
Can anyone suggest a way to implement a call to FT_GET_ADDRESS or FT_GET_EADDRESS in a view? This means that I would have to make the parameter calls to the function from inside the view without knowing what the user is doing when they invoke the view itself.
Or am I just barking up a dead tree?
Thanks!
- Levi
I'm also wondering if Contact Points in V12 might make this a little simpler? Will there be functions or views in 12 or 12.5 that would streamline this process?
Hi Levi,
Did you ever work this out?
Gloria
Hi Gloria,
I think I solved the immediate problem by hard coding address types in whatever report I was working on. It doesn't have the flexibility that I was looking for in my original question but it got the job done for the users.
I tinkered around with it a bit more but never got it working. The path I was going down was to have the view look at the security tables to figure out what the user was supposed to be able to see. But there were too many variations for me to ever get it working smoothly.
Hi Levi and Gloria,
I needed to do something sort of similar for an output set element that needed to return multiple emails for a mail purpose if it existed or the primary email if not. I ended up creating a view, wrapped around a function that queried VS_EADDRESS first and then FT_GET_EADDRESS if nothing was returned (yikes!).
Happy to share if interested, but the mail purpose was hard coded and the query element did not accept parameters.
Patrick.
select a.*, b.description, b.short_desc, dbo.AF_FORMAT_STRING(a.postal_code, b.zip_mask) as zipfrom dbo.ft_get_address(null, null, null, @customer_no) a JOIN tr_country b ON a.country = b.id
--
Regards,
Troy Nelson
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>Sent: 2/3/2014 2:51:24 PM