Using Case Statement To Determine Order By Field And Direction (asc Or Desc) When Using Row_number
I am trying to order by the field and direction as provided by input parameters @COLTOSORTBY and @DIR while using a CTE and assigning Row_Number, but am running into syntax errors.
Say I have a table called myTable with columns col1,col2,col3,
Here's what I'm trying to do
with myCTE AS
(
Select
col1
,col2
,col3
,row_number() over (order by
case when(@DIR = 'ASC') then
case when @COLTOSORTBY='col1' then col1 asc
when @COLTOSORTBY='col2' then col2 asc
else col3 asc
end
else
case when @COLTOSORTBY='col1' then col1 desc
when @COLTOSORTBY='col2' then col2 desc
else col3 desc
end
end
from myTable
)
Please let me know what i can do with minimal code repetition and achive my goal of dynamically sorting column and direction. I do not want to use dynamic SQL under any circumstance.
Thanks.
View Complete Forum Thread with Replies
Related Forum Messages:
Alternative To CASE In The Order By Statement?
The query below at the bottom works, but when I try to add the line below (a third line/argument in the CASE statement) then I get an error. It is not possible to have a table name and a DESC (sort order) in the same CASE line/argument. Are there other ways to accomplish this, because I would like to sort with both "h.ObjectType" and "h.ObjectType DESC"? WHEN @SortBy = 2 THEN h.ObjectType DESC SELECT weight, ObjectText.ObjectId, ObjectText.ObjectType, ObjectText.Title, ObjectText.ShortText FROM @hits h INNER JOIN ObjectText ON h.id = ObjectText.ObjectId AND h.ObjectType = ObjectText.ObjectType WHERE ObjectText.LanguageCode = @LanguageCode ORDER BY CASE WHEN @SortBy = 0 THEN weight WHEN @SortBy = 1 THEN h.ObjectType END DESCRegards, Sigurd
View Replies !
TSQL - Use ORDER BY Statement Without Insertin The Field Name Into The SELECT Statement
Hi guys, I have the query below (running okay): Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' FROM myTables WHERE Conditions are true ORDER BY Field01 The results are just as I need: Field01 Field02 ------------- ---------------------- 192473 8461760 192474 22810 Because other reasons. I need to modify that query to: Code Block SELECT DISTINCT Field01 AS 'Field01', Field02 AS 'Field02' INTO AuxiliaryTable FROM myTables WHERE Conditions are true ORDER BY Field01 SELECT DISTINCT [Field02] FROM AuxTable The the results are: Field02 ---------------------- 22810 8461760 And what I need is (without showing any other field): Field02 ---------------------- 8461760 22810 Is there any good suggestion? Thanks in advance for any help, Aldo.
View Replies !
Error Using Case Expression In Order By Statement
I have been trying to get the following Selects to work using a case expression in the order by section. I know I can easily separate out the two statements but I want to do a select using the case statement below ; however, I keep getting error 16 --"Order by items must appear in the select list if the statement contains a union. If remove the case statement and put order by "internalID desc" I receive no errors. Moreover, when I take out the union statement and execute the two select statements with each including the case expression it runs as planned. Can anyone tell what the problem is here? I have combed the web looking for an answer, but it seems that the statement is valid. Thanks J declare @date set @date = '2001' select internalID from section_data_v3 union select internalID from section_data_v4 order by case when (@date = '2001') then internalID end desc
View Replies !
Column Aliases In Case Statement In Order By
Hi All, I have this query : select col1, col2, col3, col4, col5,..... , (select col99 from tab2) as alias1 from tab1 where <condition> order by case @sortby when 'col1' then col1, when 'col2' then col2, when 'col3' then col3, when 'col99' then col99 end when i execute the above query it gives me the following error message. Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'col99'. Thanks in advance. Thanvi.
View Replies !
How Do I Get DESC Order?
Hey guys, I have a view with dates (TheDate) meant to be arranged in descending order. When I 'Execute SQL' while in the view, the DESC order works just fine and shows up with the latest date first going down. However, once I 'OPEN VIEW' the order keeps defaulting to ASCending order. How do I keep it in DESC order for viewing? Here's the statement: SELECT TOP (100) PERCENT TheDate FROM dbo.MyDates ORDER BY TheDate DESC
View Replies !
Order By Asc Then Desc Links?
hi, i have headers at the top of my list and would like for people to click the pubs link and it orders the pubs alphabetically descending and when they click again to ascend also to do this with towns and addresses and postcodes?! can someone please help? J x
View Replies !
Creating Index In DESC Order
Does anyone have a general rule or guide on when to use this SQL 2000 option when creating indexes? I was thinking generally on nonclustered indexes where the column would be unique and incremental and usually filtered on by range and often used in the order by clause. Such as columns of datetime or integers datatypes. Thanks.
View Replies !
Paging And Dynamic Sort Order (ASC/DESC)
Hi all,I have a SQL statement that allows paging and dynamic sorting of thecolumns, but what I can't figure out without making the SQL a dynamicstring and executing it, or duplicating the SQL statement between anIF and ELSE statement.Following is the SQL statement;set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgoALTER PROCEDURE [dbo].[sp_search]@search VARCHAR( 80 ), @startRow INT = 1, @endRow INT = NULL, @postcode AS CHAR( 4 ) = NULL, @suburb AS VARCHAR( 40 ) = NULL, @stateIdentity AS TINYINT = NULL, @fromLatitude AS REAL = NULL -- latitude the user is located in, @fromLongitude AS REAL = NULL -- longitude the user is located in, @sort TINYINT = 1ASBEGINSET NOCOUNT ON;DECLARE @calculateDistance BIT;SET @calculateDistance = 0;-- get the longitude and latitude if requiredIF ( NOT @postcode IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_postalcodeWHERE(postalcode = @postcode)SET @calculateDistance = 1ENDELSE IF ( NOT @suburb IS NULL AND NOT @stateIdentity IS NULL )BEGINSELECTDISTINCT@fromLatitude = latitude, @fromLongitude = longitudeFROMtbl_localityWHERE(locality = @suburb)AND(stateIdentity = @stateIdentity)SET @calculateDistance = 1END/*ELSE IF ( @fromLatitude IS NULL AND @fromLongitude IS NULL )BEGINRAISERROR( 'You need to pass a valid combination to this storedprocedure, example: postcode or suburb and state identity or longitudeand latitude', 18, 1 );END*/SELECT D1.[row], D1.[totalRecordCount], D1.[classifiedIdentity], D1.[title], D1.[summary], D1.[price], D1.[locality], D1.[state], D1.[postcode], D1.[addedLast24], D1.[dateStamp], D1.[t2Rank], D1.[t3Rank], D1.[tRank], D1.[distance], F.[originalName], F.[extension], F.[uniqueName]FROM(-- derived tableSELECT ROW_NUMBER() OVER ( ORDER BY CASE @sort WHEN 0 THENCAST( COALESCE( t2.RANK, 0 ) + COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) )WHEN 1 THEN C.title WHEN 2 THEN CAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN ( C.locality + ' ' +C.state ) WHEN 4 THEN CAST( C.price AS CHAR( 10 ) ) END ASC ) AS row, COUNT( * ) OVER() AS totalRecordCount, C.[classifiedIdentity], C.[title], C.[summary], C.[price], C.[locality], C.[state], C.[postcode], CASE WHEN ( C.[dateStamp] >= DATEADD( day, -1, GETDATE() ) )THEN 1 ELSE 0 END AS addedLast24, C.[dateStamp]/* , t1.RANK AS t1Rank */, t2.RANK AS t2Rank, t3.RANK AS t3Rank, /* COALESCE( t1.RANK, 0 ) + */ COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS tRank, CASE @calculateDistance WHEN 1 THEN CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude, @fromLongitude, L.latitude,L.longitude ) ) ELSE 0 END AS distanceFROM [tbl_classified] AS CINNER JOINtbl_locality LONC.localityIdentity = L.localityIdentity/* LEFT OUTER JOINCONTAINSTABLE( tbl_category, title, @keyword ) ASt1ON FT_TBL.categoryIdentity = t1.[KEY] */LEFT OUTER JOINCONTAINSTABLE( tbl_classified, title, @search ) ASt2ON C.classifiedIdentity = t2.[KEY]LEFT OUTER JOINCONTAINSTABLE( tbl_classified, description,@search ) AS t3ON C.classifiedIdentity = t3.[KEY]WHERE ( /* COALESCE( t1.RANK, 0 ) + */COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) ) != 0) AS D1LEFT OUTER JOINtbl_classified_file CFOND1.classifiedIdentity = CF.classifiedIdentityLEFT OUTER JOINtbl_file FONF.fileIdentity = CF.fileIdentityWHERE( row >= @startRow )AND( @endRow IS NULL OR row <= @endRow )ENDThe part I'm having trouble with is making the sort order in thefollowing line dynamicORDER BY CASE @sort WHEN 0 THEN CAST( COALESCE( t2.RANK, 0 ) +COALESCE( t3.RANK, 0 ) AS CHAR( 5 ) ) WHEN 1 THEN C.title WHEN 2 THENCAST( CEILING( [dbo].[fn_calculateDistance] ( @fromLatitude,@fromLongitude, L.latitude, L.longitude ) ) AS CHAR( 9 ) ) WHEN 3 THEN( C.locality + ' ' + C.state ) WHEN 4 THEN CAST( C.price ASCHAR( 10 ) ) END ASCany help would be greatly apprecaited.Thanks
View Replies !
Error In Image Field When Using CASE Statement
I've this Stored procedure on a SQLserver 2000 SP3: SELECT *,CASE immagine WHEN NULL THEN 0 ELSE 1 END AS hasImage FROM Squadre WHERE squadra = @squadra this is a flag that returns if the image field is present or not.. i've a lot of this type of stored procedures.. but this one returns me an error.. --------------------------- Microsoft SQL-DMO (ODBC SQLState: 42000) --------------------------- Errore 306: The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator. --------------------------- OK --------------------------- An i can't save.. why? reme,ber that in the same Db there's other Stored like this.. the same syntax and the same field or table.. can anyone help me??
View Replies !
Using A Field Alias For A CASE Statement In A GROUP BY Clause
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 Replies !
Determine Next Available Order ID
I am trying to determine the next available order id using the method below. It works provided the table has a record in it. If it doesn't I get the error "Input string was not in a correct format." I am certain that it is because the query is returning a value of NULL. How can get around that or check for the NULL value?' Establish data connection...Dim sqlConn As New SqlConnection(ConfigurationSettings.AppSettings("connectionstring"))'Determine order id number...Dim order_id As IntegerDim strSQL As StringstrSQL = "Select MAX(order_id) from mkt_order"Dim sqlCmd As New SqlCommand(strSQL, sqlConn)Dim sqlDA As New SqlDataAdapter(sqlCmd)Dim sqlDS As New DataSetsqlDA.Fill(sqlDS, "item")If sqlDS.Tables(0).Rows.Count <> 0 Thenorder_id = Convert.ToInt32(sqlDS.Tables(0).Rows(0)(0).ToString()) + 1Elseorder_id = 1End If
View Replies !
Determine Next Order Id
I'm working on a sproc that determines the next order id for a specified customer. The table has custid int, ordernum varchar(10) Data is: 1000, 1000-001 1000, 1000-002 1001, 1001-001 1000, 1000-003 I need to know the next ordernum for the specified custid. For example, GetNextOrderNum(1000) should return 1000-004. GetNextOrderNum(1002) should return 1002-001 (since there aren't any orders yet). I honestly don't know where to begin. Can someone please help?
View Replies !
Impossible? Sort On Dynamic Field (in Combination With Row_number)
I have images on which users may comment. Now I want to sort those images on the amount of comments each image has.I use custom paging in combination with ROW_NUMBERSo I want to sort items in one table based on the number of occurences of that item in another table...I have the following query (which doesnt work):SELECT *FROM(select ROW_NUMBER() OVER (ORDER BY mediaComments DESC) as RowNum,m.title,m.usercode,m.mediaid,mediaComments=(select count(*) from tblMediaComments where mediaid=m.mediaid)FROM tblAlbums a inner join tblMedia m on am.mediaid=m.mediaidleft join tblMediaComments mc on mc.mediaid=m.mediaidWHERE a.showTo=1group by m.title,m.usercode,m.mediaid) as InfoWHERE RowNum between @startRowIndex AND (@startRowIndex + @maximumRows-1)
View Replies !
Row_number Selecting From A Complex Select Statement
Hi, Code Snippet This is difficult to explain in words, but the following code outlines what I am trying to do: with myTableWithRowNum as ( select 'row' = row_number() over (order by insertdate desc), myValue from ( select table1Id As myValue from myTable1 union select table2Id As myValue from myTable2 ) ) select * from myTableWithRowNum Can anyone think of a work around so that I can use the Row_Number function where the data is coming from a union?
View Replies !
Determine Table Load Order
Does anyone have a script that analyzes primary and foreign key relationships of tables and produces a suggested load order based upon dependancies? Thanks, Fred.
View Replies !
Using An Array To Determine Query Order??
Hi. I have a VB.NET function that returns an array of Integers. Say, FunArray = [2, 3, 5, 8, 6, 23, 1, 10, 20 , 4, 54] One characteristic of the array is that no two numbers repeat - it reflects the IDs of my Users table. And, that it is not ascending or descending. What I would like to know is how do I sort my query in the order of the integers in this array? Ideally, I would like to use ORDER BY for this query. thanks in advance.
View Replies !
Problem Using Result From CASE In Another CASE Statement
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 Replies !
Determine If Values In A Field Are Alpha Or Numeric
HI, Thanks in advance for taking your time to read this post. I am trying to write a SQL query using MS SQL 2005 that will read the value of a field and tell if it is alpha or numeric. I have tried the following but it does not work: select field1 from table1 where left(field1,2)='[0-9]' select field1 from table1 where isnumber(left(field1,2) tried with a =1 at the end and without and =1 at the end the goal is to read through a field and format it so if a field looks like this 12xxx111xx I can change it to look like 12-xxx-111-xx. Any help is greatly apprecaited
View Replies !
SELECT Clause To Determine If A Field Is Japanese
I am currently trying to find a way in which I can determine if a column in a Select clause is Japanese. The column currently supports English and Japanese Kanjis and other kanas. Is there a way to determine if this column is not English or if it is Japanese without physically looking at it.? Thanks .... Chris
View Replies !
ORDER BY CASE
this is my query="SELECT i.itemid,title,SortKey from Items AS i JOIN Links AS L ON(i.ItemID=L.ItemID) WHERE L.instructorID='12232' AND courseID='12' ANDtype='Audio' order by CASE WHEN Sortkey is not null then 1 else 0 end"My SortKey can be NULL. Here's the output I am getting:(the || is to denote sortkey column)37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|I want use Sortkey when it is not null. desired output:61570 New Test Record |Africa|61572 Test 3 |Africa 1|63186 Music for Strings |Brazil|37542 Tape 1 ||37544 Tape 2 ||37819 Symphony1 ||37820 Symphony2 ||37821 Symphony3 ||37828 Symphony ||60962 Test ||
View Replies !
How To Write Select Statement Inside CASE Statement ?
Hello friends, I want to use select statement in a CASE inside procedure. can I do it? of yes then how can i do it ? following part of the procedure clears my requirement. SELECT E.EmployeeID, CASE E.EmployeeType WHEN 1 THEN select * from Tbl1 WHEN 2 THEN select * from Tbl2 WHEN 3 THEN select * from Tbl3 END FROM EMPLOYEE E can any one help me in this? please give me a sample query. Thanks and Regards, Kiran Suthar
View Replies !
ORDER BY, CASE, With Multiple Columns
I'm unable to specify multiple columns in my order by statement if i use a case statement. Does anyone know why this is, or what syntax would make this work? Thanks SELECT .... ORDER BY (CASE Lower(@SortExpression) WHEN 'prodname' THEN prodname, prodprice WHEN 'prodsize' THEN prodsize, prodname WHEN 'prodprice' THEN prodprice, prodname Else prodcompany, prodname END)
View Replies !
Sort Order And Case Sensitivity
I have a query which filters records containing uppercase andLowercase i.e.Smith and SMITH, Henderson and HENDERSON etc.Is there a way that I can filter only those records that contain thefirst uppercase letter and the remaining lowercase letters for myquery i.e. Smith , HENDERSON etc.Thanks
View Replies !
Sort Order/case-sensitivity
I have a case-insensitive sort order on my SQL7, NT4, SP1,2 installation. However, there are times when I want to do case-sensitive search ciriteria. Is there something like the pseudo code: SET SORT ORDER CASE-SENSITIVE ON SELECT * FROM table WHERE name = 'Black' SET SORT ORDER CASE-SENSITIVE OFF ..that doesn't return 'BLACK' thanks in advance for your time BL
View Replies !
Case Statement Error In An Insert Statement
Hi All, I've looked through the forum hoping I'm not the only one with this issue but alas, I have found nothing so I'm hoping someone out there will give me some assistance. My problem is the case statement in my Insert Statement. My overall goal is to insert records from one table to another. But I need to be able to assign a specific value to the incoming data and thought the case statement would be the best way of doing it. I must be doing something wrong but I can't seem to see it. Here is my code: Insert into myTblA (TblA_ID, mycasefield = case when mycasefield = 1 then 99861 when mycasefield = 2 then 99862 when mycasefield = 3 then 99863 when mycasefield = 4 then 99864 when mycasefield = 5 then 99865 when mycasefield = 6 then 99866 when mycasefield = 7 then 99867 when mycasefield = 8 then 99868 when mycasefield = 9 then 99855 when mycasefield = 10 then 99839 end, alt_min, alt_max, longitude, latitude ( Select MTB.LocationID MTB.model_ID MTB.elevation, --alt min null, --alt max MTB.longitude, --longitude MTB.latitude --latitude from MyTblB MTB ); The error I'm getting is: Incorrect syntax near '='. I have tried various versions of the case statement based on examples I have found but nothing works. I would greatly appreciate any assistance with this one. I've been smacking my head against the wall for awhile trying to find a solution.
View Replies !
How To Show Records Using Sql Case Statement Or If Else Statement
i want to display records as per if else condition in ms sql query,for this i have used tables ,queries as follows as per data in MS Sql my tables are as follows 1)material fields are -- material_id,project_type,project_id,qty, -- 2)AB_Corporate_project fields are-- ab_crp_id,custname,contract_no,field_no 3)Other_project fields are -- other_proj_id,other_custname,po for ex : vales in table's are AB_Corporate_project ===================== ab_crp_id custname contract_no field_no 1 abc 234 66 2 xyz 33 20 Other_project ============ other_proj_id other_custname po 1 xxcx 111 2 dsd 222 material ========= material_id project_type project_id qty 1 AB Corporate 1 3 2 Other Project 2 7 i have taken AB Corporate for AB_Corporate_project ,Other Project for Other_project sample query i write :-- select m.material_id ,m.project_type,m.project_id,m.qty,ab.ab_crp_id, ab.custname ,op.other_proj_id,op.other_custname,op. po case if m.project_type = 'AB Corporate' then select * from AB_Corporate_project where ab.ab_crp_id = m.project_id else if m.project_type = 'Other Project' then select * from Other_project where op.other_proj_id=m.project_id end from material m,AB_Corporate_project ab,Other_project op but this query not work,also it gives errors i want sql query to show data as follows material_id project_type project_id custname other_custname qty 1 AB Corporate 1 abc -- 3 2 Other Project 2 -- dsd 7 so plz help me how can i write sql query for to show the output plz send a sql query
View Replies !
Order By Case Cast Convert Error
I have created a SQL Stored Procedure that uses a Case statement todetermine the Order By. For one of the Case statements I am trying toturn a Char field into Datetime in for the Order By, however I can notget it to work. Can someone please take a look and my code below andtell me what I am doing wrong. Thank you.ORDER BYCASE WHEN @SortBy = 'dttm_stamp' THEN dttm_stamp End,CASE WHEN @SortBy = 'Event_Date1' THEN CAST(CONVERT(char(10),Event_Date1,101) as datetime) End,CASE WHEN @SortBy = 'FullName' THEN Emp_lastname + ', ' +Emp_firstname End,CASE WHEN @SortBy = 'FullName Desc' THEN Emp_lastname + ', ' +Emp_firstname End DESC,CASE WHEN @SortBy = 'Emp_SSN' THEN Emp_SSN End
View Replies !
SQL Distinct Value And Order By Some Field......
Dear all,In SQL Server 2000 , how to get distinct records sort by onefield .ExampleSELECT DISTINCT A FROM tblTEST ORBER BY BHere, In TableField 'A' contain more than one same data...Field 'B' contain all are different Data......I want distince in Field 'A' and order by Field 'B'..... how to getit.........regardskrishnan
View Replies !
Order By On Character Field
Does ORDER BY work on character data type in SQL Server through ODBC? I tried using the SQL Query Tool in SQL Enterprise Manager and it works but using through ODBC I can't get any results. Query: SELECT company_id, company_name FROM lt_company ORDER BY company_name company_id = integer company_name = 30 characters
View Replies !
ORDER BY &&<VarChar Field&&>
Hi group, I've got a table with two columns named [Year] and [Month]. They are both defined as VarChar. Question: Is it possible to ORDER THEM as if they where of type DateTime? EG select [year], [month] from tbl_WeightedAverageGenerated where [Year] = 2006 ORDER BY [Month] Returns: 2006, 10 2006, 11 2006, 12 2006, 5 2006, 6 etc... I need it to return: 2006 5 2006 6 2006 7 2006 8 2006 9 2006 10 2006 11 2006 12 Is this possible....and how?? TIA Regards, SDerix
View Replies !
Order By Partial Field
How can I "Order By" the second + third characters of a 7 char field ? Sample data looks like: LCA - L LCB - L LCF - M LCE - M LCE - A LCA - A LCB - A If I order by the whole field I get: LCA - A LCA - L LCB - A LCB - L LCE - A LCE - M LCF - L LCF - M What I want is: LCA - L LCB - L LCF - L LCA - A LCB - A LCE - A LCE - M LCF - M I'm still at the stage in this project were I can 'split' the field (if I have to) into first 3 and last 1. But the sort order of the last 1 is not alphabetic (I want 'L', 'A', "M"). Can I substitute a custom SortOrder some way ? (I've done that with mainframe Cobol). All suggestions appreciated. Thanks Roger
View Replies !
Order Of Nvarchar(50) Field
In my SQL 2005 database table Records, I have 3 fields, field1, field2, and field3 which are all nvarchar(50) fields. The value of field2 is something like this, MDB006-MD002-0004-3-2007. I would like to order this field but only use the 0004-3-2007 part of the field to order it. Is it possible to put the last 11 charachters (0004-3-2007) in another field and then order it using this new field?
View Replies !
Use Case To Dtermine Which Field
have a select statement which needs to apply some logic to determine which fields to use. If the modify_timestamp = create_timestamp, then I want to use a location field from patient_medication table. If the modify_timestamp and create_timestamp are not equal, I need to use the location field from a location table. So my select would be select account_id,ndc,startdate -- and then my logic. Am I correct that you can't use a case for this? Is there a better approach?
View Replies !
Case - Sensitive Field
I want to specify the data in columns are both upper & lower case(i.e. one column data having all lowercase data or uppercase data ). Ex:- if i have one column - col1 and its 1st value is RAKESH JHA 2nd value is rakesh jha then how i can find that, how much row are in upper case. respectively lower case
View Replies !
Add Text To Db Field - CASE
Hello, I have the following problem. Presum I have a field called contact_mail. I want to check if the field is empty. If that is true then i want to get the faxnumber from a field called contact_fax . And then i want to put @faxmaker.com behind it in a new field called faxmail. Something like this contact_fax:123456 - faxmail:123456@faxmaker.com. I've made the following case. CASE contact_mail WHEN 'NULL' THEN 'contact_fax@faxmaker.com' END AS faxmail But this doesn't work, does anyone got a hint?
View Replies !
Case Statement
Hi I have some question regarding the sql case statment.Can i use the case statement with the where clause.Example: SELECT FirstName, IDFROM myTablewhere case when ID= '123' then id = '123' and id='124' endorder by idBut the above code does not work.
View Replies !
Case Statement In Sql Help
I am trying to use a case statement in one of my stored proc but I am stuck a little bit.Here is a example, something like:declare @id int set @id =1case @id When 1 then select * from contactsend case but this keeps on giving me error: Incorrect syntax near the keyword 'case'. Any help is appreciated!
View Replies !
What Is The Best SQL Statement For This Case ?
Hi !!!i hope one of the sql specialists answer me about the best and most effeceint way to acheive what i am looking for Scenario:-------------i have a 3 tables related to each other Addresses, Groups and GroupAddressthe relation is for both addresses and groups is one to many in the GroupAddress.the behaviour in the application : user can add addresses to his address list and from the address list a user can add an address to many groups like if you have Group name "Freinds" and you add me in it and you have Football team group and you add me to it like that !!!not i have another function called "copy group"in the GroupAddress i have this data as example GroupID AddressID1 41 61 21 441 72 82 62 93 133 73 10and the group ID called "Freinds"i want to copy the group so i can have another group that has the same addresses by one click rather than collectiong them again one by one ...by the way the new copy will have a new group name ( as this is thebusiness logic so user can not have dupicate group name )so what is the best SQL statement that i need to copy the group ???i hope that clear enough!
View Replies !
Sql Case Statement Help
Hi all, I was wondering if there is any way in an sql statement to check whether the data your trying to get out of the DB is of a particular type, ie. Int, char etc. I was thinking about a case statement such as <code> CASE WHEN (MyNum <> INT) then 0 end AS MyNum </code> This has to be included in the sql statement cause I need this field to get other data. Any thoughts on how to achieve this would be greatly appreciated. If I’m in the wrong thread section please advise of best one to get help in.
View Replies !
Case Statement
i am working on a sql statement that is kind of weird i have a table but it only links to another table if that type of activity exists, for instance i have an activity information in the CloseCall table if the call is closed, but if it is a followup activity it is in the followup table i would like to display the close call notes from the closecall table if there is a close call activity in the activity table T_Followup T_activity< T_CloseCall here is the sql statement that i am trying to make work SELECT t_Activity.ActivityDate, tlkup_ActivityType.ActivityTypeName, t_Activity.LocationID, tlkup_Rep.RepFName + ' ' + tlkup_Rep.RepLName AS RepName, tlkup_InterestLevel.InterestLevel, CASE activitytypeid WHEN 6 THEN SELECT Closecallnotes FROM t_closecall WHERE t_Closecall.activityid = t_activity.activityid ELSE t_Activity.ActivityNotes END AS ActivityNotes FROM t_Activity INNER JOIN tlkup_ActivityType ON t_Activity.ActivityTypeID = tlkup_ActivityType.ActivityTypeID INNER JOIN tlkup_Rep ON t_Activity.RepID = tlkup_Rep.RepID INNER JOIN tlkup_InterestLevel ON t_Activity.InterestLevelID = tlkup_InterestLevel.InterestLevelID WHERE (t_Activity.LocationID = 93) ORDER BY t_Activity.ActivityDate DESC is this possible this way if not how do i make it work, right now it is just telling me there is a syntax problem, which is why i thought i would post it here because i am not too sure on the syntax of a case command and how it can be used
View Replies !
Using LIKE In A CASE Statement
I have a unique situation. My data looks something like:Test 1Test 2Test 3Test 2Test 1Test 300Test 200Test 1Test 300Test 200I want to display all of the above like:Test 1Test 2Test 3Test 2MetalsTest 1Test 1Notice that I have everything displayed except for 'Test 200' and'Test 300'. This have been replaced with the word 'Metals' and I'monly displaying it one time, no matter how often Test 200 shows up, orTest 300.My code looks like the following. It works good, except if I have toadd 'Test 400' etc...I would have to hard code them and it will buildup real quick. I looked at GROUPs to see if that would help, but Idon't think it would because it has to be displayed with a differentname such as 'Metals' for all the tests...Test 100, 200, etc... Isthere a better way without having to add each number in it? I thinkthe best way is to use a LIKE statment where the WHEN is being usedbut I keep getting errors if I use the LIKE sytnax where the WHEN isbeing used.SELECT sampleFROM mysamplesWHERE (sample <'TEST 200') AND (sample <'TEST 300')UNION ALLSELECT DISTINCTSAMPLE = CASE Sample WHEN 'test 200' THEN 'Metals' WHEN 'Test 300'THEN 'Metals'ENDFROM MYSAMPLESWHERE (sample = 'TEST 200') OR(sample = 'TEST 300')Any help is appreciated...
View Replies !
SQL CASE Statement
Hi Ive got a simple query where I want to calculate an average of one number divided by the other ie: avg(x / y) Im trying to use a case statement to return 0 in the event that y is 0, to avoid a division by zero error. My query is still returning a division by zero error anyway can anybody help? SELECT CCode, CASE WHEN BS_TOTAL_ASSETS = 0 THEN 0 ELSE AVG(BSCLTradeCreditors / BS_TOTAL_ASSETS) END AS myaverage FROM [Company/Year] GROUP BY CCode, BS_TOTAL_ASSETS Thanks
View Replies !
CASE Statement Help
Hello everyone again, now I want to take my SP a step further. As in using a CASE towards my 'taxable' field. It's value is 1 if no tax, and null if taxed. I have two seperate formulas I want to run depending on the 'taxable' value. Below is what i'm starting to chew on... CASE when tbl_products.ptaxable = '1' then ((1+(.02))*((tbl_products.pprice)*(tbl_material_us ed.mqty))) else 0 end How would I integrate my other formula for when the 'taxable' value is null? After the "else 0" in my CASE? CASE when 'taxable' = 1 THEN run_formula1 ELSE run_formula2 ??? These CASE statments will go in my WHERE clause correct? Thanks!!!
View Replies !
AVG(CASE) Statement - Help Please
I am trying to get avg score by site, by call type. Columns are Site(varchar), Calltype(varchar), totalscore(float). Calltypes are A, B, C, D. Sites are 1, 2, 3, 4. I can do a straight average statement and only get one calltype. I want to do a CASE statement to get all average scores for all calltypes. Select Site, avg(totalscore) as [Avg Score] FROM DB WHERE calltype = 'A' GROUP BY Site Results Site Avg Score (for A) 1 85 2 75.5 3 85.33 SELECT Site, AVG(CASE WHEN TotalScore > 0 AND CallType = 'A' THEN Totalscore ELSE 0 END) AS [Avg Score For A] FROM DB GROUP BY Site Results Site Avg Score For A 1 i get 8.5 2 i get 37.75 3 i get 36.57 Why am I getting a difference? Any help is greatly appreciated - thank you
View Replies !
|
TRACKER
