Using If's and selects

Using If's and selects

  

SELECT 

SUBMITED = ( SELECT COUNT({DocumentCore2}.[Id]) 

FROM {DocumentCore2} 

INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]

WHERE {StatusCore2}.[Label] = 'Completed' 

COMPLETED = ( SELECT COUNT({DocumentCore2}.[Id]) 

FROM {DocumentCore2} 

INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status] 

WHERE {StatusCore2}.[Label] = 'Submited' )


I have these two values but what I want to do is a bit different.

If (SUBMITED != 0) select '1'

Else ( If (COMPLETED != 0) select '2'

          Else select '3'

        )

I want something like that but don't know how.


Hi Miguel,

Conditionals in SQL are handled by the CASE statement. However, in your case, do you need to handle that in the query? Why not have an assignment after the query that assigns the values?

Also, having subqueries inside a SELECT might not be the best way to go about this. Do you really need them to be seperate columns? If not, you could do e.g. just

SELECT {StatusCore2}.[Label], COUNT(1)
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]
GROUP BY {StatusCore2}.[Label]

and then loop over the result and post-proces that. Or, if you do not even need the actual number of documents with the statusses, just a

SELECT DISTINCT {StatusCore2}.[Label]
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]

Kilian Hekhuis wrote:

Hi Miguel,

Conditionals in SQL are handled by the CASE statement. However, in your case, do you need to handle that in the query? Why not have an assignment after the query that assigns the values?

Also, having subqueries inside a SELECT might not be the best way to go about this. Do you really need them to be seperate columns? If not, you could do e.g. just

SELECT {StatusCore2}.[Label], COUNT(1)
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]
GROUP BY {StatusCore2}.[Label]

and then loop over the result and post-proces that. Or, if you do not even need the actual number of documents with the statusses, just a

SELECT DISTINCT {StatusCore2}.[Label]
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]

Thanks for the reply Kilian but that is what I have now.

I have 1 query that gets all documents. Then, for each document, I check the status is 'Completed' or 'Submited' and do the assignment depending on that. 

But I want to know if is possible to reduce this process, having only 1 query and no 'for each' cycle.


Hi Miguel,

So what exactly do you want to know then? Do you need the count, or only whether any of the types are present?

Kilian Hekhuis wrote:

Hi Miguel,

So what exactly do you want to know then? Do you need the count, or only whether any of the types are present?

I want to know if any documents have a certain status, but retrieving only one value.


If at least 1 document have the status 'Submited' return 1 (for example)
Else If at least 1 document have status 'Completed' return 2
Else return 3.

The exact value of the count isn't important since I only want to know if it's gratter than 0.


Solution

And if you set MaxRecords=1 and get [0,1] rows with the value?


SELECT TOP(1) 1

FROM documents

WHERE status=submitted

UNION ALL

SELECT TOP(1) 2

FROM documents

WHERE status=completed

UNION ALL

SELECT 3



Solution

Well, if you want it in that order, you can ORDER the output accordingly, and even if you have more than one row returned, you only need to look at the first row (so you can set the Max. Records to 1):

SELECT DISTINCT {StatusCore2}.[Label]
FROM {DocumentCore2}
INNER JOIN {StatusCore2} ON {StatusCore2}.[Id] = {DocumentCore2}.[Status]
ORDER BY
  CASE {StatusCore2}.[Label]
    WHEN 'Submitted' THEN 1
    WHEN 'Completed' THEN 2
    ELSE 3
  END

If there are any 'Submitted' documents, the first row returned will contain 'Submitted'. If there aren't any, the first row will contain 'Completed', else it will contain something else (if there are any) or the list is empty.

Nuno ninja'd me :). As for his solution, it's possible but I'd go for SELECT TOP 1 instead of SELECT DISTINCT, I think that's slightly faster. Also, use UNION ALL for the same reason.

Thanks for both answers! :D

They both work but I understand better the one from Kilian.

Even though, as Nuno's faster, that will be marked as the answer   

Answer edited as Kilian suggested. Between UNION and UNION ALL for only three one-columned rows there is no visible difference, but TOP is a lot better than DISTINCT, especially in a table for documents that keeps on growing.