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>
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".
i was tasked to created an UPDATE statement for 6 tables , i would like to update 4 columns within the 6 tables , they all contains the same column names. the table gets its information from the source table, however the data that is transferd to the 6 tables are sometimes incorrect , i need to write a UPDATE statement that will automatically correct the data. the Update statement should also contact a where clause
the columns are [No] , [Salesperson Code], [Country Code] and [Country Name]
i was thinking of doing
Update [tablename] SET [No] = CASE WHEN [No] ='AF01' THEN 'Country Code' = 'ZA7' AND 'Country Name' = 'South Africa' ELSE 'Null' END
Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".
Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.
Below is the scenario which I have currently in my query. I need to write this query without any hardcode values , so that it will work til n number of years without modifications.
Startdate = CASE WHEN Trandate between '06-04-2013' and '05-04-2014' then '06-04-2013' Trandate between '06-04-2012' and '05-04-2013' then '06-04-2012' Trandate between '06-04-2011' and '05-04-2012' then '06-04-2011' Trandate between '06-04-2010' and '05-04-2011' then '06-04-2010' Trandate between '06-04-2009' and '05-04-2010' then '06-04-2009' Trandate between '06-04-2008' and '05-04-2019' then '06-04-2008' END
How I am using a CASE statement within a WHERE clause to filter data:
CREATE PROCEDURE dbo.GetSomeStuff @filter1 varchar(100) = '', @filter2 varchar(100) = '' AS BEGIN SELECT
[Code] .
What I want, is to be able to pass in a single value to filter the table, or if I pass in (at the moment a blank) for no filter to be applied to the table.
Is this a good way to accomplish that, or is there a better way? Also, down the line I'm probably going to want to have multiple filter items for a single filter, what would be the best way to implement that?
1. I have a simple JOIN statement between A and B, e.g. Cities A JOIN Countries B:
SELECT A.City_Name, B.Country_Code, B.Country_Area FROM Cities A JOIN Countries B ON B.Country_Id = A.Country_Id WHERE B.Country_Type='ABC';
That statement works absolutely fine, very fast (less than a second) and returns me 2 records
2. I need to replace Country Area column with 1 for Europe and 0 for all the rest. I implement so in the following way:
SELECT A.City_Name, B.Country_Code, CASE B.Country_Area WHEN 'EUR' THEN 1 ELSE 0 AS Country_Area FROM Cities A JOIN Countries B ON B.Country_Id = A.Country_Id WHERE B.Country_Type='ABC';
Now to get the same two records it takes 03:55 minutes (!)
I have looked into Estimated Execution Plan, but couldn't spot any difference - all straight forward.
It is SQL 2012 SP1 with compatibility level set to 110
I have a stored proc that contains an update which utilizes a case statement to populate values in a particular column in a table, based on values found in other columns within the same table. The existing update looks like this (object names and values have been changed to protect the innocent):
UPDATE dbo.target_table set target_column = case when source_column_1= 'ABC'then 'XYZ' when source_column_2= '123'then 'PDQ'
[Code] ....
The powers that be would like to replace this case statement with some sort of table-driven structure, so that the mapping rules defined above can be maintained in the database by the business owner, rather than having it embedded in code and thus requiring developer intervention to perform changes/additions to the rules.
The rules defined in the case statement are in a pre-defined sequence which reflects the order of precedence in which the rules are to be applied (in other words, if a matching value in source_column_1 is found, this trumps a conflicting matching value in source_column_2, etc). A case statement handles this nicely, of course, because the case statement will stop when it finds the first "hit" amongst the WHEN clauses, testing each in the order in which they are coded in the proc logic.
What I'm struggling with is how to replicate this using a lookup table of some sort and joins from the target table to the lookup to replace the above case statement. I'm thinking that I would need a lookup table that has column name/value pairings, with a sequence number on each row that designates the row's placement in the precedence hierarchy. I'd then join to the lookup table somehow based on column names and values and return the match with the lowest sequence number, or something to that effect.
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2)) Declare @S nvarchar(255) Insert into @T Values ('AR'), ('AT'), ('AW') Set @S = 'Auto Repairs' Select * from @T T where case @S when 'Auto Repairs' then 'AR' when 'Auto Target' then 'AT' when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
I have an Address column that I need to Substring. I want to remove part of the string after either, or both of the following characters i.e ',' OR '*'
Example Record 1. Elland **REQUIRES BOOKING IN*** Example Record 2. Theale, Nr Reading, Berkshire Example Record 3. Stockport
How do I achieve this in a CASE Statement?
The following two case statements return the correct results, but I some how need to combine them into a single Statement?
,LEFT(Address ,CASE WHEN CHARINDEX(',',Address) =0 THEN LEN(Address ) ELSE CHARINDEX(',' ,Address ) -1 END) AS 'Town Test'
,LEFT(Address ,CASE WHEN CHARINDEX('*',Address ) =0 THEN LEN(Address) ELSE CHARINDEX('*' ,Address ) -1 END) AS 'Town Test2'
I wrote a select statement, I only want to see orders with max lastUpdatedOn date of 14 days and older. Now my results show dates with all orders of 14 days and older (which is OK), but all others are displayed in the "Uitgifte" column as "NULL". But those orders should not be displayed at all.
selectdistinct ProductionHeader.ProdHeaderOrdNr, ProductionHeader.PartCode, ProductionHeader.Description, ProductionHeader.Qty, (select max (ProdStatusLog.ProdStatusCode)
SELECT NAME, CASE WHEN PHONE IS NULL THEN '-' ELSE PHONE END AS PHONE FROM PATIENT WHERE PHONE = '-'
If I am switching the NULL values to hyphens (-), why am I not getting any records when I look for hyphens in the WHERE clause? The script is not as simple as this or else I would just check for NULL values. I really need to switch NULL values to something I can pass on to a variable and query on it from Reporting Services.
how we can replace the multiple values in a single select statement? I have to build the output based on values stored in a table. Please see below the sample input and expected output.
create table #test (id int ,color varchar(20) ) insert into #test (id, color) values (1, 'blue'),(2, 'red'),(3,'green'),(4,'red,green')
if I wanted to run a query to select any records that had red in the color field, how would I do that? Not the one with only red, but a query that would give me both record number 2 and record number 4.
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.
What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX
If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.
After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.
CASE HB.PLAN_TYPE
WHEN '10' THEN 'Y'
ELSE 'N' END AS 1A,
CASE HB.PLAN_TYPE
WHEN '10' THEN 'N'
ELSE ' ' END AS 1B,
' ' AS 1C,
CASE HB.PLAN_TYPE
WHEN '10' THEN HB.BENEFIT_PLAN
ELSE ' ' END AS 1D,
CASE HB.PLAN_TYPE
WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'
WHEN HB.COVRG_CD ='2' THEN 'YY'
WHEN HB.COVRG_CD ='3' THEN 'ZZ'
ELSE 'WW' END)
ELSE ' ' END AS 1E,
It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX
While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL. Any assistance in this matter would be greatly appreciated.
SELECT SUM(((CASE WHEN o.date>= a.activity_date, other filter condition, other filter condition THEN (select coalesce(d.balance,d2.balance) from drawtable d where coalesce(d.date, d2.date) < a.activity_date order by d.date desc limit 1) - ( select coalesce(d.balance, d2.balance) from drawtable d where coalesce(d.date, d2.date) = interval 'current date' else end ))
from emailtable a LEFT JOIN opportunity o left join drawtable d left join drawtable d2 etc
The tricky part is I'm joining that same table twice.....would this be better in a max/min case when statement?
I have successfully built a messaging system into my application, I now in the process of displaying the messages in the UI.
The following are how my tables are constructed.
CREATE TABLE [MailBox].[Message]( [Id] [bigint] IDENTITY(1,1) NOT NULL, [SenderId] [bigint] NOT NULL, [Message] [nvarchar](max) NOT NULL, [SentDate] [datetime] NOT NULL, CONSTRAINT [PK_MailBox.Message] PRIMARY KEY CLUSTERED
[Code] .....
Now I haven't set the foreign key on the MessageReceipient table yet. When someone sends me an email I insert a record into [MailBox].[Message] and output the insert id into MessageReceipient along with the ReceipientId this is working as expected, when I then click on my inbox I call the following stored procedure:
Select p.Username, count(mr.RecipientId) [TotalMessages], CASE WHEN mr.ReadDate is null then 1 -- New message WHEN mr.ReadDate is not null then 0 -- Message has been read END AS NewMessage FROM [User].[User_Profile] p JOIN [MailBox].[Message] m on p.Id = m.SenderId JOIN [MailBox].[MessageRecipient] mr on m.Id = mr.MessageId GROUP BY p.Username, mr.RecipientId, mr.ReadDate
This will give me the person who has emailed me, the total amount of messages and if the message is new or its been read, I do this by checking the ReadDate column as shown in the case statement (but this gives me duplicates, which is not what I want). Lets say user1 emails me 5 times so when I call this proc I will have the same user displayed to me 5 times, what I'm trying to achieve with the proc is it will show User1 as the following:
User1 5 Messages 1 or 0 New Messages
I can get it to display as follow when I remove the case statement
User1 5 Messages
but as soon as I add the case statement back in then I get 5 rows.
How can I change this proc in such a way that it will display the data as follows;
User1 5 Messages 1 or 0 New Messages
New Messages is dependent on ReadDate if its null then we have a new message, otherwise its been read.
I'm trying to create a case statement that if a field = a certain code, I'd like to take another field * 0.9. But, I'm getting a Null value for the answer..here is the statement:,case when parts.ndc = '50242-0138-01' then labels.BAGSDISP*0.9 end "Units Dispensed"..For this example labels.BAGSDISP is a value of 2. So, in theory it should be 2 * 0.9 and the result should be 1.8 but I'm getting a NULL
I have a DistributorInvoiceNumber that can end with in 'R', 'A', 'CRR' or 'CR'.I am trying to write a case statement like so:
CASE WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'A' THEN 'ADJ' WHEN RIGHT([ih].[DistributorInvoiceNumber],1) = 'R' THEN 'REV' WHEN RIGHT([ih].[DistributorInvoiceNumber],3) = 'CRR' THEN 'REV' WHEN RIGHT([ih].[DistributorInvoiceNumber],2) = 'CR' THEN 'CREDIT' ELSE NULL END AS 'Status'
For the most part the code is working, with the exception of the fields that just end in 'R'. An example of this is 471268R, 2525125901CRR, 11100325230CR Basically if the number ends with an A, then its an Adjustment, if it ends with JUST an R, then its a Reversal; if it ends with just a CR then it is a Credit and if it ends with CRR then it is a Reversal (Credit Reversal). How can I differentiate between the different R's since three of them end with R? Would I use a RTRIM command somehow?
I'm trying to set up a statement that gives me a field called 'BINNO' if the payor = Commercial. But, I have a few customers that don't have Commercial. They have a Payor of Grant or Part D. How would I set up a statement that looks for Commerical 1st...then Grant or Part D. I started with this
case when inscomp.payor = 'COMMERCIAL' then INSCOMP.BINNO
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8 DECLARE @DetailQuestionID INT = 1380 /*------- I need the query to run for the below three data. Here i'm updating my planids that already exists in my database*/ DECLARE @planids VARCHAR(MAX) = '2,4,5'
My goal is to have a file (it comes from another system) that I want to import via an OPENROWSET style query.
The query would look like this:
select [NoName] from openrowset('MSDASQL' ,'Driver={Microsoft Access Text Driver (*.txt, *.csv)}; DefaultDir=c:filedir' ,'select * from "file.lst"')
If I make the file a .csv it works fine. However, if it has a not CSV or TXT extension it throws the following error and cannot seem to find a solution to it.
OLE DB provider "MSDASQL" for linked server "(null)" returned message "[Microsoft][ODBC Text Driver] Cannot update. Database or object is read-only.". Msg 7350, Level 16, State 2, Line 1 Cannot get the column information from OLE DB provider "MSDASQL" for linked server "(null)".
In addition, (although I can probably find this elsewhere), I need to have the first line 'BLANK' so that it does not miss data (there is no header row). Is there a way to use OPENROWSET without BULK to basically include all rows as data?
I am selecting the count of the students in a class by suing select COUNT(studentid) as StCount FROM dbo.student But I need to use a case statement on this like if count is less than 10 I need to return 'Small class' if the count is between 10 to 50 then I need to return 'Medium class' and if the count is more than 50 then 'Big class'.
Right now I am achieving this by the following case statement
SELECT 'ClassSize' = CASE WHEN Stcount<10 THEN 'Small Class' WHEN Stcount>=10 and StCount<=50THEN 'Medium Class' WHEN Stcount>50 THEN 'Big Class' END FROM( select COUNT(studentid) as Stcount FROM dbo.student) Stdtbl
I have a stored procedure in which we are deriving some flags. So, we used series of CASE statements.
For examples
CASE WHEN LEFT(CommissionerCode, 3) IN ('ABC','DEF',...) THEN 1 WHEN PracticeCode IN (.......) THEN 1 WHEN (CommissionerCode IN (.....) OR PracticeCode NOT IN (.....) OR .....) THEN 1 ELSE 0 END
I need to put these conditions in config table and generate dynamic sql.
What is the best way to do this? especially, 3rd condition with OR logic with multiple columns involved.
I had two servers, in which SQL Servers are installed. I created a alias for server
Server 1 : test1.sqlcloud.com (Alias name with port no other than 1433) Server 2 : test2.sqlcloud.com (Alias name with port no other than 1433)
I can connect to the servers using the port. for ex server1 to server2 and viceversa using "test2.sqlcloud.com,port". But I could not connect to the server by only specifying the alias name ("test2.sqlcloud.com"). When I am connecting I am getting the following error.
"TITLE: Connect to Server ------------------------------
Cannot connect to test2.sqlcloud.com.
------------------------------
ADDITIONAL INFORMATION:
Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=21020; handshake=0; (Microsoft SQL Server, Error: -2)
I know how to create an alias for a SQL server instance, so instead of MyServerMyInstanceName, I can type MyInstanceName into my SSMS connection box. But SSAS does not allow me to connect with the alias.
I've looked all over and found plenty of documentation that SQL 2005 and 2008 doesn't allow the use of instance aliases for SSAS. I've even found notes from Microsoft saying "we'll fix this in a future edition." I have not found any indications or notes as to whether or not this has been fixed.
Can aliases be created for 2012 SSAS? Or is this still not available?
I have some stored procedures that do updates using table aliases, like this:
UPDATE TableAlias SET ColVal = 1 FROM RealTable AS TableAlias WHERE TableAlias.ColVal <> 1
This allows me to include joins and stuff in the update (not shown) and make it all more readable for me.
It all works fine, and the SSMS parser says it's fine. But I also have another script which looks at sys.sql_expression_dependencies and sys.objects to find stored procedures with invalid object references (see below), and it's understandably saying that all of the above type stored procedures have invalid references.
SELECT OBJECT_NAME(DEP.referencing_id) AS referencing_name, DEP.referenced_entity_name FROM sys.sql_expression_dependencies AS DEP WHERE -- Only validate local references: ( DEP.referenced_database_name = DB_NAME()
[Code] ....
So I have a couple questions.
1. Is the UPDATE syntax I'm using kosher? 2. Can you recommend any updates to my stored procedure validation script that will better accommodate table aliases like mine?
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 Clausebut unable to use it in WHERE CLAUSE.PLS TELL ME IF IT IS POSSIBLE TO USE IT IN WHERE CLAUSE AND SOME ALTERNATIVE.QUERY:SELECTM.SECS =CASEWHEN NO_OF_SEC IS NULL THEN -1WHEN NO_OF_SEC =0 THEN 1ELSE NO_OF_SECENDFROM DOWNLOAD_MASTER MWHERE M.SECS < 100ORDER BY M.SECSHoping for a immediate reply.thanks in advance
As a database developer, I have so many databases that I "own" scattered across various servers that it''s getting difficult to remember where all of my databases reside. It doesn't work that the DBAs have taken to some very hard to remember server naming conventions.
I was going to create aliases via the configuration manager, but it turns out the DBAs overwrite my entries each night with THEIR aliases and they won't add any for my use.
So I decided to simply add some records to my host file so that instead of having to connect to "SERVER-AD_DADF-DAFDASS" I can just use "CustomerA".
This solution seems to work until I tried to access a linked server. If I connect to the main server via it's actual name, I can hit the remote/linked server with no issue. However, if I connect to the main server using it's alias, connection to the remote/linked server fails with: Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.
The linked server is set up to use current security context and I'm not logging into the alias any differently that I do when using the actual server name.
