<div>
You cannot use an alias in the same level it is created.<br />
<br />
<br />
Nothign can '=' null so you need to use &quot;is null&quot;. &nbsp;<br />
<br />
Not sure how your will get a null concatenating a ' ' in the string.<br />
<br />
Anyway, either a CTE or sub select.<br />
<br />

<pre><code id="code-20-43065763-1">with CTE as (
	SELECT Name,
		RTRIM([CompanyID) + ' ' + RTRIM([CustomerNumber) + ' ' + RTRIM([JobNumber)  as [preContPaid]
	FROM Jobs_Rept
)
select 
	CASE
      WHEN [preContPaid] is NULL
      THEN 0
      ELSE 1
from CTE</code></pre>
</div>


You cannot use an alias in the same level it is created.


Nothign can '=' null so you need to use "is null".  

Not sure how your will get a null concatenating a ' ' in the string.

Anyway, either a CTE or sub select.



with CTE as (
	SELECT Name,
		RTRIM([CompanyID) + ' ' + RTRIM([CustomerNumber) + ' ' + RTRIM([JobNumber)  as [preContPaid]
	FROM Jobs_Rept
)
select 
	CASE
      WHEN [preContPaid] is NULL
      THEN 0
      ELSE 1
from CTE

Fractional CTO

David Favor

<div>
@slightwv...<br />
<br />
I am receiving an &quot;incorrect syntax&quot; error on &quot;from CTE&quot;
</div>


@slightwv...

I am receiving an "incorrect syntax" error on "from CTE"

<div>
Nevermind...I forgot the &quot;End&quot;
</div>


<div>
@slightwv...<br />
<br />
The query is not returning &quot;SELECT Name,&quot;<br />
<br />
I want it to also show other fields that are not part of the alias
</div>


@slightwv...

The query is not returning "SELECT Name,"

I want it to also show other fields that are not part of the alias

BI Director

Grayson Hampton

<div>
<div class="content wysiwyg-content">
Is there a way to use a Case Statement with an alias.<br />
I would like Case statement to return a 0 or 1 based on preContPaid <br />
<br />
SELECT Name<br />
RTRIM([CompanyID) + ' ' + RTRIM([CustomerNumber) + ' ' + RTRIM([JobNumber) &nbsp;as [preContPaid],<br />
CASE<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;WHEN [preContPaid] = NULL<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;THEN NULL<br />
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;ELSE [preContPaid]<br />
<br />
FROM Jobs_Rept
</div>
</div>


Is there a way to use a Case Statement with an alias.
I would like Case statement to return a 0 or 1 based on preContPaid 

SELECT Name
RTRIM([CompanyID) + ' ' + RTRIM([CustomerNumber) + ' ' + RTRIM([JobNumber)  as [preContPaid],
CASE
	WHEN [preContPaid] = NULL
	THEN NULL
	ELSE [preContPaid]

FROM Jobs_Rept

SQL Case Statement used with an Alias

T-SQL

SQL (Structured Query Language) is designed to be used in conjunction with relational database products as of a means of working with sets of data. SQL consists of data definition, data manipulation, and procedural elements. Its scope includes data insert, query, update and delete, schema creation and modification, and data access control.