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
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
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.
From: Gloria Ormsby <bounce-gloriaormsby5026@tessituranetwork.com>Sent: 2/3/2014 2:51:24 PM
Hi Levi,
Did you ever work this out?
Gloria