CASE WITH EXISTS

Question

Post reply

CASE WITH EXISTS

TSQL Tryer

SSCrazy Eights

Points: 8869
More actions
October 31, 2016 at 7:52 am

#318489

Hi,

I have the following -

SELECT
CASE WHEN EXISTS ( SELECT CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf') FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO] i ) THEN '1' ELSE 'None'
END AS 'result'

What I want though is to pull back the -

SELECT CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')

FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO]

instead of the '1'

How do I achieve that? Thanks

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

TSQL Tryer (10/31/2016)

Hi,

I have the following -

SELECT
CASE WHEN EXISTS ( SELECT CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf') FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO] i ) THEN '1' ELSE 'None'
END AS 'result'

What I want though is to pull back the -

SELECT CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')

FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO]

instead of the '1'

How do I achieve that? Thanks

You have been around here long enough to know you need to provide some details. This is pretty vague but I think you are wanting to return the result of the select statement when there are rows or the scalar value 'None' when there are no rows? Could you not handle the empty result set in your application instead of in the database? Is this is a stored procedure or a single select statement?

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Thom A SSC Guru Points: 99009 More actions · Answer 2

Thom A

SSC Guru

Points: 99009

October 31, 2016 at 8:04 am

#1909631

Why not use COALESCE around the SELECT statement? Why is it in a sub query, instead of in the FROM clause?

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Luis Cazares SSC Guru Points: 183686 More actions · Answer 3

Why don't you simply use

SELECT CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')

FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO] i

Instead of using subqueries and CASE clauses?

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 4

You are right in what you say in what I am trying to achieve.

It's for a single select statement result set.

I have an SSIS package, where I need to generate a file name based on the data available to me in FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO].

That single result set is then written to a variable called "filename". Later on in the package I am dealing with the a file being produced if the result set is zero, so it doesn't matter what the result set is if there are no results.

Thom A SSC Guru Points: 99009 More actions · Answer 5

Thom A

SSC Guru

Points: 99009

October 31, 2016 at 8:13 am

#1909637

I therefore assume your SSIS package SQL task is expecting a Single Row Result set then?

COALESCE (T-SQL) would work for you then.

Edit: Added link.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

Sean Lange SSC Guru Points: 286562 More actions · Answer 6

Here is one way you can force it to always return a single row.



select top 1

	CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')

	, 1 as SortOrder

FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO]  i

UNION ALL

select 'None'

	, 2

order by SortOrder

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 7

I believe that Sean means something like this:



SELECT TOP 1 *

FROM(

    SELECT TOP 1

	    CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')

	    , 1

    FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO]  i

    UNION ALL

    SELECT 'None'

	    , 2) x(Name, SortOrder)

ORDER BY SortOrder;

Although, if the row count is really low on that table, here's an alternative.



SELECT TOP ISNULL( MAX(CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')), 'None')

FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO]  i;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

TSQL Tryer SSCrazy Eights Points: 8869 More actions · Answer 8

TSQL Tryer

SSCrazy Eights

Points: 8869

October 31, 2016 at 8:25 am

#1909642

Thank you Sean. That will meet my needs.

Sean Lange SSC Guru Points: 286562 More actions · Answer 9

Luis Cazares (10/31/2016)

I believe that Sean means something like this:

SELECT TOP 1 * FROM( SELECT TOP 1 CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf') , 1 FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO] i UNION ALL SELECT 'None' , 2) x(Name, SortOrder) ORDER BY SortOrder;

Although, if the row count is really low on that table, here's an alternative.

SELECT TOP ISNULL( MAX(CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')), 'None') FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO] i;

Thank Luis. I thought of ISNULL but I think the problem is that there isn't a row in that table, not that the value is NULL.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Luis Cazares SSC Guru Points: 183686 More actions · Answer 10

Sean Lange (10/31/2016)

Luis Cazares (10/31/2016)

Although, if the row count is really low on that table, here's an alternative.

SELECT TOP ISNULL( MAX(CONCAT(i.BatchId,+'_'+ i.Company+'_',ltrim(rtrim(i.[Accounting Period]))+'_nondlo.saf')), 'None') FROM [Havebury].[RepairInvoice].[RepairInvoiceNONDLO] i;

Thank Luis. I thought of ISNULL but I think the problem is that there isn't a row in that table, not that the value is NULL.

That's why I'm using the MAX() function without a GROUP BY. If there are no rows, it will simply return a NULL value.

The problem would be if there are enough rows to hit the performance in a significant way.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2