CASE Alias In WHERE

Jul 20, 2005

Hello,

I found members of this group very helpful for my last queries.
Have one problem with CASE. I can use the column name alias in Order By Clause
but unable to use it in WHERE CLAUSE.
PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.

QUERY:

SELECT
M.SECS =
CASE
WHEN NO_OF_SEC IS NULL THEN -1
WHEN NO_OF_SEC =0 THEN 1
ELSE NO_OF_SEC
END
FROM DOWNLOAD_MASTER M
WHERE M.SECS < 100
ORDER BY M.SECS



Hoping for a immediate reply.
thanks in advance

View 3 Replies


ADVERTISEMENT

Using A Field Alias For A CASE Statement In A GROUP BY Clause

May 20, 2004

When I created a CASE statement (This is at work, Pat:)) it is about 30-40 lines long. I gave it a name and set the name = to the case statement:

ie,

SELECT fieldname1 =
CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END
, fieldname2
, fieldname3
FROM tablename1
GROUP BY CASE
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
WHEN condition THEN 'blah blah'
ELSE thisandthat
END, , fieldname2, fieldname3

etc.


The long CASE statement in my GROUP BY is awkward to me. Is this the only way to do it? I tried using the fieldname1 but it comes back as an invalid field name and asks for the "expression".

Regards,

Dave

View 5 Replies View Related

Joining On And Grouping By CASE Function Column Alias (URGENT)

Apr 14, 2004

I REALLY need to perform a JOIN and a GROUP BY on a CASE function column alias, but I'm receiving an "Invalid column name" error when attempting to run the query. Here's a snippet:

SELECT NewColumn=
CASE
WHEN Table1.Name LIKE '%FOO%' THEN 'FOO TOO'
END,
Table2.SelectCol2
FROM Table1
JOIN Table2 ON NewColumn = Table2.ColumnName
GROUP BY NewColumn, Table2.SelectCol2
ORDER BY Table2.SelectCol2

I really appreciate any help anyone can provide.

Thanks,
DC Ross

View 5 Replies View Related

SQL Server 2012 :: Case Statement - How To Get Values Into Alias Extension

Oct 6, 2014

I have a bit of trouble getting values into one alias field, my code is below. I am trying to get values into the alias extension, Agent_ID is sometimes null, and so is agent_id2, however sometimes they both have values in them, and then only one of the values is every returned. When in the example below only Agent_ID (11111) is ever returned by I want both of them returned.

Agent_ID Agent_ID2
11111 22222
<code>
SELECT DISTINCT
CASE WHEN [AGENT_ID] is not null then AGENT_ID
when agent_id2 is not null then agent_id2 end as extension
FROM [AA_Helper].[dbo].[tblEmpData]
</code>

View 9 Replies View Related

Can Create Alias Server1DBInstance1 On Server3DBInstance3 And Assign Objects To That Alias

Nov 24, 2015

We will be moving 2 different databases (SS2005 & SS2008) to a new SS2014 SQL Server.  Currently our codes looks something like Server1DBInstance1... & Server2DBInstance2... Is it possible to move the objects from these 2 instances to Server3DBInstance3 and then use an alias to reference the objects?  Or does Server3 need to have DBInstance1 & DBIstance2?  Basically, is the alias just for the database or for the instance too?  Can I create an alias "Server1DBInstance1' on Server3DBInstance3 and assign objects to that alias?

View 16 Replies View Related

SQL Server 2008 :: Change Text Format From Case Sensitive To Case Insensitive?

Aug 31, 2015

How can I change my T-SQL text editor from text sensitive to text insensitive?

View 2 Replies View Related

Case Insensitivity Is On Server Wide: Tables Render Case Sensative...

Jan 6, 2005

Hello:

I have created an SQL server table in the past on a server that was all case sensative. Over time I found out that switching to a server that is not case sensative still caused my data to become case sensative. I read an article that said you should rebuild your master database then re-create your tables. So after rebuilding the master database, a basic restore would not be sufficient? I would have to go and manually re-create every single table again?

Any suggestions?

View 4 Replies View Related

Case Insensitive Searching In Sql Server 2000 When It's Case Sensitive

May 4, 2007

Can someone point me to a tutorial on how to search against a SQL Server 2000 using a case insensitive search when SQL Server 2000 is a case sensitive installation?
 
thanks in advance.

View 3 Replies View Related

HELP! Case Insensitive Database On Case Sensitive Server

Aug 17, 2005

We need to install CI database on CS server, and there are some issueswith stored procedures.Database works and have CI collation (Polish_CI_AS). Server hascoresponding CS collation (Polish_CS_AS). Most queries and proceduresworks but some does not :-(We have table Customer which contains field CustomerID.Query "SELECT CUSTOMERID FROM CUSTOMER" works OK regardless ofcharacter case (we have table Customer not CUSTOMER)Following TSQL generate error message that must declare variable @id(in lowercase)DECLARE @ID INT (here @ID in uppercase)SELECT @id=CustomerID FROM Customer WHERE .... (here @id in lowercase)I know @ID is not equal to @id in CS, but database is CI and tablenames Customer and CUSTOMER both works. This does not work forvariables.I suppose it is tempdb collation problem (CS like a server collationis). I tried a property "Identifier Case Sensitivity" for myconnection, but it is read only and have value 8 (Mixed) by default -this is OK I think.DO I MISS SOMETHING ????

View 4 Replies View Related

Doing A Case-sensitive Query In A Case-insensitive Database

May 29, 2008

I am working in a SQL server database that is configured to be case-insensetive but I would like to override that for a specific query. How can I make my query case-sensitive with respect to comparison operations?

Jacob

View 5 Replies View Related

Transact SQL :: Upper Case To Lower Case Conversion

May 4, 2015

I have column with value of all upper case, for example, FIELD SERVICE, is there anyway, I can convert into Field Service?

View 7 Replies View Related

Can You Use Replication From A Case Sensitive Db To A Case Insensitive Db?

Aug 19, 2007

I am curious with using replication in sql server 2005 one way from db A (source) replicating to db B(destination) in which db A has a collation of CS and db B has a collation of CI.  Will there be any problems with this scenario? Thanks in advance! 

View 2 Replies View Related

Problem Using Result From CASE In Another CASE Statement

Nov 5, 2007

I have a view where I'm using a series of conditions within a CASE statement to determine a numeric shipment status for a given row. In addition, I need to bring back the corresponding status text for that shipment status code.

Previously, I had been duplicating the CASE logic for both columns, like so:




Code Block...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,
shipment_status_text =
CASE
[logic for condition 1]
THEN 'Condition 1 text'
WHEN [logic for condition 2]
THEN 'Condition 2 text'
WHEN [logic for condition 3]
THEN 'Condition 3 text'
WHEN [logic for condition 4]
THEN 'Condition 4 text'
ELSE 'Error'
END,
...remainder of SQL view...






This works, but the logic for each of the case conditions is rather long. I'd like to move away from this for easier code management, plus I imagine that this isn't the best performance-wise.

This is what I'd like to do:



Code Block
...beginning of SQL view...
shipment_status =
CASE
[logic for condition 1]
THEN 1
WHEN [logic for condition 2]
THEN 2
WHEN [logic for condition 3]
THEN 3
WHEN [logic for condition 4]
THEN 4
ELSE 0
END,


shipment_status_text =

CASE shipment_status

WHEN 1 THEN 'Condition 1 text'

WHEN 2 THEN 'Condition 2 text'

WHEN 3 THEN 'Condition 3 text'

WHEN 4 THEN 'Condition 4 text'

ELSE 'Error'

END,
...remainder of SQL view...


This runs as a query, however all of the rows now should "Error" as the value for shipment_status_text.

Is what I'm trying to do even currently possible in T-SQL? If not, do you have any other suggestions for how I can accomplish the same result?

Thanks,

Jason

View 1 Replies View Related

Dbo Alias

Mar 30, 2000

We have an alias for dbo in various code areas, development/test/production. It was easy to tell the DBO alias
on release 6.5 but now on 7.0, the only way I can figure out if the userid is aliased to dbo is to sign on as the userid
and query 'select user_name( )' and if it returns dbo, I have the answer. Is there an easier way to do this by looking in a
system catalog or is there something else I am missing? Thanks in advance. Karen Suenram

View 7 Replies View Related

DBO ALIAS?

Jul 1, 1999

In 6.5 you could alias someone as dbo, thus preventing an ownership chain break. In 7.0 there are roles, one of which is db_owner. I gave a user db_owner role, and they created a table. But no one else can see it? And he is listed as the owner in EM. How do I more effectivly mimic the dbo alias available in 6.5?

View 1 Replies View Related

Having With Alias?

Jun 25, 2007

Hi All,

I am trying to filter my result with HAVING clause. vchEditorName is an alias which I get in the result as a column (it gives me correct values). I want to select only those records which have the value in this field like 'akash'


sql Code:






Original
- sql Code




SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName,
Case a.enumEditorType
WHEN 'A' THEN am.vchFirstName
WHEN 'M' THEN u.vchScreenName
END as vchEditorName
FROM tblArticles a
LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID
LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID

WHERE a.enumStatus='A'
GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName
HAVING vchEditorName LIKE '%akash%'






SELECT a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus, am.vchFirstName, u.vchScreenName,     CASE a.enumEditorType         WHEN 'A' THEN am.vchFirstName         WHEN 'M' THEN u.vchScreenName     END  AS vchEditorNameFROM  tblArticles a LEFT OUTER JOIN tblLogin u ON a.intEditorId = u.intUserID LEFT OUTER JOIN tblAdminMaster am ON a.intEditorId = am.intAdminID          WHERE a.enumStatus='A' GROUP BY a.intArticleId, a.intEditorId, a.enumEditorType, a.vchArticleTitle, a.enumStatus,  am.vchFirstName, u.vchScreenNameHAVING vchEditorName LIKE '%akash%' 

View 5 Replies View Related

Alias

Apr 21, 2008

I want an alias instead of display the column name for the below query..

select STE = case when STE ='AT'then'01'
when STE ='AT1'then'02'
......
.......
.......
........
else STE
end
from tableinfo

Output for the query

STE
---
01
02

Desired output:

STEDN
-----
01
02

Thanks for the help in advance !!!

View 4 Replies View Related

Alias

Jun 2, 2008

Hi All,
I've just installed an active/active sql cluster. Both are named instance hence if i need to connect to it, I need to use NODE1SQLINST1 and NODE2SQLINST2. Is there a way to just use NODE1 or any other single name to connect to the sql instance? I was looking at Alias but I can't get it working. Anyone has any ideas or suggestion for me? Reason why i want to do this is in an odbc connection, instead of using NODE1SQLINST1, I can use NODE1.
Thanks

Regards
Ken

View 6 Replies View Related

Anybody Pls Help Me!!!!!!!!! About Alias T.y.

Aug 15, 2007

pls someone help me about adding alias column or column alias

here's my code

select

(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage is null or (dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'POS')) as 'NoOfPendingTransaction',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'POS') as 'NoOfConfirmedPositive',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage in ('CHM','CHA') and dto_client_dtl.testResult = 'NEG') as 'NoOfConfirmedNegative',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage is not null and dto_client_dtl.testResult = 'POS') as 'TotalNoOfScreeningPositive',
(select count(*)
from dto_client_dtl
where dto_client_dtl.testStage = 'INI' and dto_client_dtl.testResult = 'NEG') as 'TotalNoOfScreeningNegative',
(select count(*)
from dto_client_dtl
where dto_client_dtl.tranStat in ('CCn','TCN')) as 'TotalNoOfCancelledTxns',
('NoOfPendingTransaction'+'TotalNoOfScreeningPositive'+'TotalNoOfScreeningNegative') as 'SubTotal'

from dto_client_dtl

when i run this code the output is

Truncated incorrect DOUBLE value: 'NoOfPendingTransaction'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningPositive'
Truncated incorrect DOUBLE value: 'TotalNoOfScreeningNegative'

View 2 Replies View Related

Alias

Oct 5, 2006

hi. i face a problem . i cant reference an alias and have to copy and paste code again. how can i ? see "Computed Total", i had to copy its code again.

set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

ALTER PROCEDURE [dbo].[Select_Bill_]

@payment_or_bill nvarchar(2),

@spcode nvarchar(25)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @fieldname varchar(10) DECLARE @stmt varchar(4000)

DECLARE Fields CURSOR FOR

SELECT Amounttype FROM BILL_Amounttypes

SET @stmt = 'select billid,Date_,Suppliercode,billtype,typecode,payment_or_bill,roe,currency'

OPEN Fields

FETCH Next FROM Fields INTO @fieldname

WHILE @@Fetch_Status = 0

BEGIN

SET @stmt = @stmt + ', (select billid_detailed from

bill_Detailed where billid = bill1.billid and

amounttype = ''' + @fieldname + ''''

SET @stmt = @stmt + ') As ' + @fieldname

FETCH Next

FROM Fields

INTO @fieldname END

CLOSE Fields

DEALLOCATE Fields

SET @stmt = @stmt + ',(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end)

from bill_detailed where billid = bill1.billid ) as "Computed Total",Total'

SET @stmt = @stmt + ',(Select case when

(Select sum(amountfc * case when amountfc < 0 then -1 else 1 end ) from bill_detailed where billid = bill1.billid ) =

Total then ''Yes'' else ''No'' end ) as Processed'

SET @stmt = @stmt + ' From bill_ as bill1'

exec(@stmt)

select (@stmt)

END

View 5 Replies View Related

Alias OK, IP Not

Dec 11, 2006

Hi all,



I was able to get my mirroring setup to work only when I use Alias instead of IP address. Any idea why it is so?



Thanks,

Avi

View 3 Replies View Related

Alias

May 18, 2006

How to get more columns within same alias?

(
select DateOpen AS Date,TestObjectID from RprRepair where TestObjectID = @AssetID
union all
select DateSent ,TestObjectID from RprRepair where TestObjectID = @AssetID
union all
select DateRepairFinished,TestObjectID from RprRepair where TestObjectID = @AssetID
) AS Der



This works fine alone, but when i put it into union i get an error that no more than one value can be in subqueries.

View 3 Replies View Related

Case Sensitivity When A User Enters Data Into The Database. How To Deal With Case Sensitivity.

Sep 6, 2007

I am working on a C#/asp.net web application. The application has a text box that allows a user to enter a name. The name is then saved to the database.
Before the name is saved to the database, I need to be able to check if the name already exists in the database. The problem  here is that what if the name is in the database as "JoE ScMedLap" and somoene enters the name as "Joe Schmedlap" which already exists in the database,but just differs in case.
In other words how do deal with case sensitiviy issues.

View 2 Replies View Related

Restore Of Case Insensitive Database To A Case Sensitive Database - SQL Server 2000

Jul 20, 2005

Yesterday I received a response to my CI/CS Collation problem and therecommendation was to try and restore a CI Collation database to a CSCollation database. After creating a blank CS database a full restore(Force restore over existing database) does change the Collation toCI. I'm unsure as to how I can restore without changing theCollation. Any suggestions?

View 2 Replies View Related

Alias Information In 70

Jun 27, 2000

Hi,
In my database one user is aliased like a guest. When i run sp_helpuser on the particular database it is giving following result.

username login name database
-------- ----------- --------
aaaa NULL XYZ


NOrmally we fill see like this for GUEST User. Now i want to drop this user 'aaaa' only. IF i use
sp_dropuser 'null'
i think guest user will also drop from XYZ database.
So can anyone pls suggest me how to drop this user. I have to add a same user with full permissions. i know in 65 we have sysalternates table gives all alias information. Is there any table gives the same information in sql7.
Pls suggest me regarding this.

thank u
raj

View 1 Replies View Related

ROLES Vs Alias

Feb 16, 1999

I am just getting started with SQL Sever 7.0, most things are must easier and simpler.
I do have one mind set problem
With SQL 6.5 I created a logon(JAWS) , aliased the developers to the logon(JAWS)
and all the tables and other objects were owned by ((JAWS). The developers would then grant
access to the tables. And the users would open JAWS.tablename.
In the SQL 7.0 test are I take an NT group that have the devlopers in them and allow SQL SErver
logon for that group. Then I allow the group access to the database and give the group
database roles of db_owner, db_ddladmin, db_datareader and db_datawriter.
The developers can create objects but the objects are not owned by the role but are
owned by the indivudual NT accounts that are in the NT group.
Any hint of what to change to have the ownership show up as the NT group??

View 1 Replies View Related

Using Alias In Where Condition

Mar 1, 2001

Can we do

Select BookNo as Catalog from Books
where Catalog = 12356

I need to find the way to use alias in "where" for very complex query
Is anyone has way around it ?
Thank you

View 1 Replies View Related

Several Alias For The Same Column

Mar 2, 2005

In SQLServer I can't change my SQL column name.

But I need to see another field name in query tool (Excel)

How can I do that (alias..., description ?

Thanks

View 1 Replies View Related

Column Alias

Aug 30, 2006

Hello everyone.

I was wondering if there is a way that you can set the alias name of a column to a value that resides in another table instead of the alias being a static value that you type in.

If anyone has any ideas on ho i can accomplish this i would greatly appreciate it.

View 14 Replies View Related

Database Alias

Sep 27, 2006

Is there any way in SQLServer (2000) to setup an alias to another SQLServer database?

What I want/need to do is this:

.i have a database for each project but many of the tables schemas/triggers/stored procedures/etc. are common so what I want to do is run one set scripts. I want to essentially set up a project structure so that all specific app databases also include some core stuff.

.for example, consider a Users table (simple example). For two applications we have exactly the same users structure, sprocs, triggers, etc. but we have physically different data. I just want to run the same ddl.

.but the users table has a log associated with it in an associated log database. The trigger (and some historical queries) refer to the explicit log database (App1Log.Users, for example).

.what i'm figuring is that if i could set up an alias to the log database and have the sql code refer to that I'd be able to reuse all the code very neatly:

set logDBAlias = 'App1Log'

insert into logDBAlias.Users etc....

.trouble is I cannot figure out how to alias the log database!

Any ideas?

Thank you.
John

View 1 Replies View Related

Server Alias

May 13, 2004

how to add a alias to a server?
I have gone through BOL where it says it can be done thru right click agent -->> connections and configure alias.
i want to know if i configure a alias to a server from a client is it valid for other connections also?
meaning there are two database servers A and B
i add alias to server A by using client network utilities on server B,
then is this alias available if i try to connect to the server A thro some other server c?
hope this is not that confusing as it sounds. ;)

View 2 Replies View Related

Alias Name For Table

Dec 5, 2006

Hi ,

I have query related to using of Alias names for the Tables in SQL query, will the performance of the query affected if we use Alias name for tables in joins. considering the fact that there was no need to use alias name in those joins.

Your response is highly appreciated.

Regrads

View 6 Replies View Related

Alias Yes Or No For Performance

Mar 27, 2007

Hi all,
I have a small argument with friend here regarding use of alias names for tables will affect the performance of the query in case of a large database. Please provide your thoughts on the same.

Necessity is the mother of all inventions!

View 20 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved