Using IFs in T-SQL select statement
-
Here is my delema. A company row can have multiple representations in the security table, based on the priusa or prican values in the company table matching the iid values int he security table, or the values in company table being null, in which case i just want to match the cue of the first iid listed for the key in the security table. The If section works if I change out the copmany.key references for a literal value, so as far as i can tell its having an issue with the assignment of the value to security.iid = (IF...). Any ideas? SELECT company.key, company.name, security.cue FROM company LEFT OUTER JOIN security ON company.key= security.key AND security.iid = (IF (select priusa FROM company WHERE key = company.key) is NOT NULL BEGIN select priusa FROM company WHERE key= company.key END ELSE IF (select prican FROM company WHERE key= company.key) is NOT NULL BEGIN select prican FROM company WHERE key= company.key END ELSE BEGIN select TOP 1 iid FROM security WHERE key= company.key ORDER BY security.iid END) WHERE (company.name like @name)
-
Here is my delema. A company row can have multiple representations in the security table, based on the priusa or prican values in the company table matching the iid values int he security table, or the values in company table being null, in which case i just want to match the cue of the first iid listed for the key in the security table. The If section works if I change out the copmany.key references for a literal value, so as far as i can tell its having an issue with the assignment of the value to security.iid = (IF...). Any ideas? SELECT company.key, company.name, security.cue FROM company LEFT OUTER JOIN security ON company.key= security.key AND security.iid = (IF (select priusa FROM company WHERE key = company.key) is NOT NULL BEGIN select priusa FROM company WHERE key= company.key END ELSE IF (select prican FROM company WHERE key= company.key) is NOT NULL BEGIN select prican FROM company WHERE key= company.key END ELSE BEGIN select TOP 1 iid FROM security WHERE key= company.key ORDER BY security.iid END) WHERE (company.name like @name)
The reason is that you cannot have correlation in inline view when used in from section. Perhaps something like this would be a simpler approach:
SELECT company.key,
company.name,
(SELECT security.cue
FROM security
WHERE company.key = security.key
AND security.iid = ISNULL(company.priusa,
ISNULL(company.prican,
(select MAX(iid) FROM security WHERE key= company.key))))
FROM company
WHERE (company.name like @name)Mika
The need to optimize rises from a bad design. My articles[^]
-
The reason is that you cannot have correlation in inline view when used in from section. Perhaps something like this would be a simpler approach:
SELECT company.key,
company.name,
(SELECT security.cue
FROM security
WHERE company.key = security.key
AND security.iid = ISNULL(company.priusa,
ISNULL(company.prican,
(select MAX(iid) FROM security WHERE key= company.key))))
FROM company
WHERE (company.name like @name)Mika
The need to optimize rises from a bad design. My articles[^]
-
The reason is that you cannot have correlation in inline view when used in from section. Perhaps something like this would be a simpler approach:
SELECT company.key,
company.name,
(SELECT security.cue
FROM security
WHERE company.key = security.key
AND security.iid = ISNULL(company.priusa,
ISNULL(company.prican,
(select MAX(iid) FROM security WHERE key= company.key))))
FROM company
WHERE (company.name like @name)Mika
The need to optimize rises from a bad design. My articles[^]
Perhaps the
COALESCEfunction would be better? -
Thanks much That works wonderfully for this case. However, what if i wasn't skipping null values. What if the default value was 0 and not null?
-
Perhaps the
COALESCEfunction would be better? -
Yep, that would also work out fine.
The need to optimize rises from a bad design. My articles[^]
-
Thanks. I'm just getting started and coundn't find anything without knowing the keywords to look for.