February 25, 2002 at 4:06 pm
Hi:
I have the following stored procedure
Alter Procedure sp_test
@strProxyContactID VARCHAR(20) = 'C6UJ9A00175B'
As
-- License SNs registered to a specific contact
SELECT
SERIAL_NO,
(SELECT DISTINCT FEATRSET.FTRSET_ID
FROM dbo.FEATR INNER JOIN
dbo.FEATRSET ON
dbo.FEATR.FEATURESET = dbo.FEATRSET.FEATURESET
WHERE (dbo.FEATR.SERIAL_NO = dbo.Register.Serial_No) AND
(dbo.FEATRSET.SUPERCEDED = 0 OR
dbo.FEATRSET.SUPERCEDED IS NULL) AND
(dbo.FEATR.SUPERCEDED = 0 OR
dbo.FEATR.SUPERCEDED IS NULL)) AS FTRSET_ID,
(SELECT DISTINCT Spro_In.Key_SN
FROM Spro_In
WHERE CONVERT(VARCHAR(12),Spro_In.Key_SN) = Register.Serial_No) AS KEYA
FROM dbo.REGISTER
WHERE
(ProxyContactID = @strProxyContactID)
ORDER BY Serial_No ASC
This stored procedure returns three fields.
The first from the primary query, the other two fields come one each from the two subqueries.
This works OK, but the fact of the matter is that, by design, the results of the two subqueries are always complimentary (you can also look at them as being mutually exclusive). That being the case, my goal is to represent both values in one column.
When a value is returned from the first subquery, there is no value to return from the second one.
When no value is returned from the first subquery, there very likely will be a value returned from the second subquery, but not necessarily.
The third condition exists that the value exists in neither the first nor the second subqueries.
I could be wrong, but I believe this can be done. I've attempted a number of times using variations of IF...ELSE, CASE..., and local variables, but I've been unsuccessful at getting this to work (I'm sure it's syntax, I'm fairly new at T-SQL and complex SQL).
Any suggestions are appreciated.
Thanks for your time.
JK
February 25, 2002 at 7:42 pm
Off the top of my head, try using the 'if exists' keywords to control what is queried.
i.e.
If exists(select * from table1)
begin
select serialnumber, (select * from table1) from table 3 where ....
end
else
begin
select serialnumber, (select * from table2) from table 3 where ....
end
February 25, 2002 at 7:47 pm
I try to conform you syntax a little to make it run faster, but it should accomplish everything you want or lead you down the right path as I don't know exactly what it is you are expecting.
SELECT
SERIAL_NO,
(CASE
WHEN FTRSET_ID.FTRSET_ID IS NOT NULL THEN FTRSET_ID.FTRSET_ID
WHEN KEYA.Key_SN IS NOT NULL THEN KEYA.Key_SN
ELSE NULL
END) AS Col2
FROM
dbo.REGISTER
INNER JOIN
(
SELECT
DISTINCT
Spro_In.Key_SN,
CONVERT(VARCHAR(12),Spro_In.Key_SN) AS SerialKey
FROM
Spro_In
) AS KEYA
ON
SerialKey = Register.Serial_No
INNER JOIN
(
SELECT
DISTINCT
FEATRSET.FTRSET_ID,
dbo.FEATR.SERIAL_NO
FROM
dbo.FEATR
INNER JOIN
dbo.FEATRSET
ON
dbo.FEATR.FEATURESET = dbo.FEATRSET.FEATURESET
WHERE
(
dbo.FEATRSET.SUPERCEDED = 0
OR
dbo.FEATRSET.SUPERCEDED IS NULL
)
AND
(
dbo.FEATR.SUPERCEDED = 0
OR
dbo.FEATR.SUPERCEDED IS NULL
)
) AS FTRSET_ID
ON
FTRSET_ID.SERIAL_NO = dbo.Register.Serial_N
WHERE
(
ProxyContactID = @strProxyContactID
)
ORDER BY Serial_No ASC
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
February 26, 2002 at 10:06 am
Hi:
Thank you both for your responses. I'm a bit overwhelmed with things that I need to do right now, but I will look at both responses in detail as soon as I can.
I do appreciate the help.
Thanks,
JK
February 26, 2002 at 1:01 pm
quote:
I try to conform you syntax a little to make it run faster, but it should accomplish everything you want or lead you down the right path as I don't know exactly what it is you are expecting.
Hello:
What you provided me with did not work at first, but that's not surprising since, as you said, you can't be certain of what I was expecting.
After staring at the SQL you provided for a while, I came to the (hopeful) conclusion it would work if the joins where LEFT and not INNER. Initial tests show that it does what I want it to do with that change.
I have only a vague understanding of what this query is doing. I need to stare at it and think about it a lot more.
I can say that I'm just astounded sometimes at the power in this language.
Thank you very much for sharing your expertise.
JK
February 26, 2002 at 4:42 pm
Great to hear. If you need I can do a break down explanation, just let me know. If not then look at subqueries, joins and case for TSQL as that is the basic building blocks of what I did.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply