August 11, 2008 at 10:47 am
Hi,
This might belong in the "Newbie" group, but I thought I'd ask here...
I'm setting up a DSV and want to create a new "column" that would be
a result of a coalesce function. Here's what I've come up with and
entered into the Edit Named Calculation box:
(SELECT
coalesce(CountryCode1,CountryCode2,CountryCode3) as ResultingCountryCode
FROM TableA a left join TableB b
on a.ContactID=b.ContactID
left join TableC c
on a.ContactID=c.ContactID)
I have 3 tables each containing a CountryCode, however it may or may
not be populated in 1 or 2 of them for any given Contact, thus the coalesce.
The action is accepted, but when I try and "Explore Data", I get the following
error message:
Subquery returned more than 1 value. This is not permitted when the subquery
follows =, !=, = or when the subquery is used as an expression.
Since I am new to Named Calculations, it may just be a wrong understanding of
what this is supposed to do. Again, I am trying to create a new "column"
in TableA that contains the first non-null value of CountryCode between the 3 tables.
Thanks in advance
August 12, 2008 at 1:17 pm
this sounds to me that the join between table a and either b and\or c is returning more than one record. so, the contact id not enough to join between the tables and that is why you are seeing the error.
if you run your query in SMS, do you get one for one result or do you see multiple records? meaning, if there are 10 records in table A, when you run the query, do you still only get 10 records or do more records join into the result?
August 12, 2008 at 2:01 pm
Hi Chuck,
Thanks for your reply....
As you suggested, I ran the query in SSMS and came out with the same number
of records as in TableA which is the table used for the left join.....very puzzling.
August 13, 2008 at 7:16 am
sorry for getting back to you a little late, i do not know if this is still an issue or not for you but this is what I see can be done to get you what you need.
the problem is not that the query does not return a one to one, the new column is returning multiple values per row of the original table. meaning, you are not joining this group to a single row to produce just the one value for the given row.
if you were to run the query: select a.*, (coalesce code) from tableA in SSMS, you would get the same error because the coalesce code does not join back to TableA and there lies the problem.
to perform what you want, I would create a named query to give me my column in addition to the other columns in the original table. The named query would look something like this:
--i am just saying orig.* because i have no idea what you want, you would want to explicitly state your columns needed
select orig.*, d.ResultingCountryCode
from TableA orig
inner join (
SELECT a.id,
coalesce(CountryCode1,CountryCode2,CountryCode3) as ResultingCountryCode
FROM TableA a left join TableB b
on a.ContactID=b.ContactID
left join TableC c
on a.ContactID=c.ContactID) d
on orig.id = d.id
where orig.id = d.id is the join on whatever the primary key is of TableA.
hth
August 13, 2008 at 9:07 am
Hi Chuck,
My query actually worked fine in SSMS, however I reworked it per your example in the Edit Named Calculation window, but I get an error message:
"Only one expression can be specified in the select list when the subquery is not introduced with EXISTS".
This window seems a bit querky anyway, because the whole query string needs to be enclosed
with parentheses, otherwise the error returned is
"Incorrect syntax near the keyword 'AS'".
"Incorrect syntax near the keyword 'select'".
August 13, 2008 at 9:16 am
the exists error is occurring because there are two values in the subquery. that example is only valid in the FROM clause of the overall SELECT statement for the table. If you were to remove the Id field and just select back the COALESCE field in the Edit field, the field would save. However, when you went to explore the data, you would get the Subquery error of multiple values being returned and the reason being that you need to join the COALESCE subquery to the original table.
My suggestion is to create a New Named Query with the Select statement that I had enclosed before. Then, you can pick out which columns you want to use. This Named query would replace the original table that you had in the DSV.
August 13, 2008 at 11:46 am
I finally got it to work.....I did use the Replace table with Named Query that you suggested, but I was able to use my original query. It still seems strange that the Named Calculation wouldn't work using the same syntax, but, hey, I'm not going to complain!
Thanks for your suggestions and time, Chuck.....
August 13, 2008 at 12:05 pm
for my own curiousity, what was the complete query that you used in the named query to replace the original table?
August 13, 2008 at 12:15 pm
pretty much the same as what I had to begin with:
SELECT a.ContactID, a.[other fields],
COALESCE (b.CountryCode1, c.CountryCode2, a.CountryCode3) AS CountryCode
FROM TableA AS a LEFT OUTER JOIN
TableB AS b ON a.ContactID = b.ContactID LEFT OUTER JOIN
TableC AS c ON a.ContactID = c.ContactID
August 13, 2008 at 12:24 pm
ah, but it is not.
in the named calculation, because you added the coalesce code as the column, here is the sql that was being rendered:
SELECT a.ContactID, a.[other fields],
( select
COALESCE (b.CountryCode1, c.CountryCode2, a.CountryCode3) AS CountryCode
FROM TableA AS a LEFT OUTER JOIN
TableB AS b ON a.ContactID = b.ContactID LEFT OUTER JOIN
TableC AS c ON a.ContactID = c.ContactID )
FROM TableA
If you ran this code in SSMS, you would get the multiple values error for the specific column because the coalesce code did not join to anything in TableA, which is why I suggested the join in the From clause.
However, by performing the Named Query approach with the Coalesce code as a field but all of the tables joined to one another in the FROM, you eliminated the problem.
does that make sense?
August 13, 2008 at 3:10 pm
yup.....makes sense....the subquery was returning multiple rows but not able
to join back 1-for-1. I think I was imputing some "smarts" into the Named Calculation
function that it didn't really have. Thanks again for directing me to Named Query.....
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply