MS SQL Server :: Column Aliases In Case Statement In Order By

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 !

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 !

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 Replies !

Can You Use Column Order Value In Select Statement
Is there a shortcut to spelling out column names when you are doing a select statement?

For instance could you write Select 1, 5, 6 from table where whatever...

I tried this but didn't get any results so if you can I must be using wrong syntax.

Thanks
!

View Replies !

SELECT Column Aliases: Refer To Alias In Another Column?
Using SQL Server 2000. How can I refer to one alias in another column?E.g., (this a contrived example but you get the idea)SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM dataNote how the speed alias is used in the definition of acceleration alias but this doesn't seem to work.

View Replies !

SELECT Column Aliases: Refer To Alias In Another Column?
Using SQL Server 2000. How can I refer to one alias in another column?

E.g., (this a contrived example but you get the idea)

SELECT time, distance, (distance / time) AS speed, (speed / time) AS acceleration FROM data

Note how the "speed" alias is used in the definition of "acceleration" alias but this doesn't work.

View Replies !

Using The ORDER BY Clause When The Ordered Column Is Not Needed In The SELECT Statement
Greetings,

I have a C# application that calls a stored procedure to query the database (MSSQL 2005). I only have one field/column returned from the query but I need that column ordered.

How do I use the ORDER BY clause without returning the index column which does the sorting? The first example is NOT what I want. I want something that works like the second example which only returns the 'Name' column.

ALTER PROCEDURE [dbo].[MyProcedure]

AS

BEGIN

SELECT DISTINCT A.Name, A.index

FROM
...
...
ORDER BY A.[Index], A.Name ASC

END

ALTER PROCEDURE [dbo].[MyProcedure]

AS

BEGIN

SELECT DISTINCT A.Name
FROM
...
...
ORDER BY A.[Index]

END

Thanks

View Replies !

Using Data As Column Aliases
Hi,

I’m working with a really old design migrated to SQL 2005, in which I basically have two tables…

Table 1 contains all the “proper” data, and has columns called: col_1, col_2, col_3

Table 1’s data is something like:

col_1, col_2, col_3
Jack,jack@yahoo.ca,Toronto
Jill,jill@hotmail.com,Montreal

Table 2 contains meta-data for Table 1, specifically, it has two columns: column, meta-data

Table 2’s data is something like:

column,metadata
col_1,name
col_2,email
col_3,city

(Hopefully, my description of the design makes sense….basically; Table 2’s data describes what’s in each column of Table 1).

So, the question, if I want to write a ‘SELECT’ on Table 1, how can I use the data in Table 2 as aliases (or column) headers.

I’m currently going down the path of building dynamic SQL statements in T-SQL….but before I get too far, wanted to vet this idea here (it’s always been a fantastic resource for me)

Thanks in advance!

View Replies !

ADO MD Column Names / Aliases
I'm working on an application to display sales data using sql server
2000 reporting services.
The data is in an MS Analysis Services cube, so I'm using MDX queries
in the data set.

The queries all have a similar format of the form:

SELECT
{[Measures].Members} ON COLUMNS,
{ MyDimension.members} ON ROWS
FROM MyCube

where the items in the COLUMNS clause are allways the same and
MyDimension might be SalesBranch, ProductCategory, etc.

When Reporting services works out the fields for the returned data set
it appears to be flattening out the MyDimension structure and using the

dimension level names for field names.
eg
<Fields>
<Field Name="Manufacturer">

<DataField>[Manufacturer_Model].[Manu].[MEMBER_CAPTION]</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Measures_Oe_Id">
<DataField>[Measures].[Oe Id]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
<Field Name="Measures_Oe_Realval_A">
<DataField>[Measures].[Oe Realval A]</DataField>
<rd:TypeName>System.Object</rd:TypeName>
</Field>
</Fields>
In the report layout I can select these fields and display them as
expected.
However the drawback is that since dimension level names are encoded
into report field names I have to have a separate report for each
dimension I want to put on the ROWS. ie one .RDL file for SalesBranch,
one for ProductCategory etc.

If this was SQL it would not be a problem because in the SQL SELECT
statement the column names could be fixed using "SELECT colname AS
othercolname ", but, being new to MDX, I can't find how to do this.

Does anyone know how to achieve this in MDX / ADO MD ? ie how to give
an MDX column an alias

Thanks
Steve

View Replies !

Multi Aliases With The Same Column?
TABLE1
======================

PriceList
---------
1
2
3
1
2
3
1
2
3

Price
-----
777
888
999
777
888
999
777
888
999
(pretend these columns are side by side)
======================

I need to make a query to:
SELECT PRICE AS 'PRICE1' WHERE PRICELIST = 1
AND SELECT PRICE AS 'PRICE2' WHERE PRICELIST = 2
AND SELECT PRICE AS 'PRICE3' WHERE PRICELIST = 3

the output that i want is:

PRICE1
------
777
777
777

PRICE2
------
888
888
888

PRICE3
-----
999
999
999
(pretend these columns are also side by side)

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 !

Default Sort Order When Order By Column Value Are All The Same
Hi,
We got a problem.
supposing we have a table like this:

CREATE TABLE a (
aId int IDENTITY(1,1) NOT NULL,
aName string2 NOT NULL
)
go
ALTER TABLE a ADD
CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

select top 10 * from a order by aName
Result is:
11Bank of abcde
10Bank of abcde
9Bank of abcde
8Bank of abcde
7Bank of abcde
6Bank of abcde
5Bank of abcde
4Bank of abcde
3Bank of abcde
2Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users. :eek:

Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.

So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

View Replies !

Default Sort Order When The Order By Column Value Are All The Same
Hi,
   We got a problem.
   supposing we have a table like this:

CREATE TABLE a (
    aId             int         IDENTITY(1,1) NOT NULL,
    aName           string2     NOT NULL
)
go
ALTER TABLE a ADD
    CONSTRAINT PK_a PRIMARY KEY CLUSTERED (aId)
go

insert into a values ('bank of abcde');
insert into a values ('bank of abcde');
...
... (20 times)

select top 5 * from a order by aName
Result is:
6 Bank of abcde
5 Bank of abcde
4 Bank of abcde
3 Bank of abcde
2 Bank of abcde

select top 10 * from a order by aName
Result is:
11 Bank of abcde
10 Bank of abcde
9    Bank of abcde
8    Bank of abcde
7    Bank of abcde
6    Bank of abcde
5    Bank of abcde
4    Bank of abcde
3    Bank of abcde
2    Bank of abcde

According to this result, user see the first 5 records with id 6, 5, 4, 3, 2 in page 1, but when he tries to view page 2, he still see the records with id 6, 5, 4, 3, 2. This is not correct for users.
Of course we can add order by aid also, but there are tons of sqls like this, we can't update our application in one shot.
So I ask for your advice here, is there any settings can tell the db use default sort order when the order by column value are the same? Or is there any other solution to resolve this problem in one shot?

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 !

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 !

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 !

Case Statement
Have a procedure called Collections_Cats. It runs fine but l want to modify it but l'm finding it difficult
to get the desired output. The case statement gives an example of what l'm trying to achieve. l would like to build it into my procedure.

l have a table called

paysheet

customer_no loan_no payday payfrequency etc
0000000000 000000 22 3
111111111122222212 4
3333333333 333333 21 2
5555555555 555555 6 1

payfrequency 1= daily
2= weekly
3= fortnightly
4= monthly

l would like to get all the records that satisfy my select criteria. Which is select all records and fields as
specified in my procedure that have a payday thats today only?

How can l build my case statement into the procedure?Is ther a better way of doing it?

l also want to do a count of all the records that have been processed by the procedure ?

Declare @Today int
Set @Today = (SELECT Day(GETDATE()))
SELECT
CASE
WHEN Pay_frequency = 4 And payday =@Today THEN @Today
WHEN Pay_frequency = 3 And payday =@Today THEN @Today
WHEN Pay_frequency = 2 And payday =@Today THEN @Today
WHEN Pay_frequency = 1 And payday =@Today THEN @Today
ELSE 0
END AS "Pay_day",Customer_No,Pay_Frequency
FROM Pay_Sheet
Order by Pay_frequency Desc

************************************************** *************

************************************************** *************

CREATE Procedure Collections_Cats
AS
BEGIN

Declare @today int
Set @today = (SELECT Day(GETDATE()))

DECLARE Collections_Cats_Cursor
CURSOR
FOR
SELECT n.loan_No AS Loan_No,
n.customer_No AS Customer_No,
c.first_name AS First_name,
c.second_name AS Second_name,
c.surname AS Surname,
c.initials AS Initials,
b.Bank_name AS Bank_name,
br.branch_code AS Branch_code,
d.bank_acc_type AS Bank_acc_type,
pay_sheet.pay_frequency AS Pay_Frequency,
n.monthly_Payment AS monthly_Payment,
pay_sheet.payday AS payday
FROM Transaction_Record tr

INNER JOIN
Loan n ON tr.loan_No = n.loan_No
INNER JOIN
Customer c ON n.customer_No = c.customer_no
INNER JOIN
Bank_detail d ON c.customer_no = d.customer_no
INNER JOIN
Branch br ON d.Branch = br.Branch
INNER JOIN
Bank b ON br.Bank = b.Bank
INNER JOIN
pay_sheet ON c.customer_no = pay_sheet.customer_no
WHERE Pay_sheet.Payday = @today

OPEN Collections_Cats_Cursor

-- Perform the first fetch.
FETCH NEXT FROM Collections_Cats_Cursor

-- Check @@FETCH_STATUS to see if there are any more rows to fetch.
WHILE @@FETCH_STATUS = 0
BEGIN
-- This is executed as long as the previous fetch succeeds.
FETCH NEXT FROM Collections_Cats_Cursor
END

CLOSE Collections_Cats_Cursor
DEALLOCATE Collections_Cats_Cursor
END;
GO

View Replies !

Case Statement
How do l use the case statement to cater for these updates.

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = ('AB')
WHERE AB_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + ' | SB'
WHERE SB_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'SB'
WHERE SB_CLIENT = 1 And entity is null
COMMIT
go

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | CI')
WHERE CI_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'CI'
WHERE CI_CLIENT = 1 And entity is null
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = Entity + '' + (' | GEMS')
WHERE GEMS_CLIENT = 1
COMMIT
GO

BEGIN TRANSACTION
UPDATE TBL_DEV_OL_NEW1
SET Entity = 'GEMS'
WHERE GEMS_CLIENT = 1 And entity is null
COMMIT
GO

View Replies !

IF Else Within A Case Statement
In my query below i have the results ,The thing to observe in the result set it for the name "Acevedo" , "Abeyta" its not doing a group by and populating the results in the following column.Rather its addind a new row and adding it as 1 in the next row.
I have to populate the counts in one row for common names.Shall i use a if condition within a case block.If yes how?any other work arounds would be appriciated.
Please help
Thanks

select
isnull(replace(Ltrim(Rtrim(P.Lastname)),',',''),'' ) Lastname
, case ProductID
WHEN 22 then count(S.Product)
Else 0
END AS Builders
, case ProductID
WHEN 23 then count(S.Product)
Else 0
END AS Associates
, case ProductID
WHEN 24 then count(S.Product)
Else 0
END AS Affiliates
FROM vwpersons p with (nolock)
join vwSubscriptions S with (nolock)
on S.RecipientID = P.ID
where P.Lastname in (select Ltrim(Rtrim(H.name)) from externaldata.dbo.Hispanicnames H)
group by P.Lastname, S.ProductID
having count(P.LastName)>=1
order by 1

Result set :

Firstname Builders AssociatesAffiliates

Abarca 010
Abascal200
Abelar 100
Abeyta100
Abeyta010
Abreu 100
Abreu 010
Acevedo100
Acevedo050

View Replies !

Case Statement
Hi
Can anybody tell me how to execute store procedure in the case statement.

Thanks

View Replies !

CASE Statement
I am trying determine if I can do something like the code below. I have done a left join on a table. In the select statement there are three possible values. Yes, No, or NULL. I could like to use a Case statement to determine if there is Null. If so, then output N/A in place of the Null. So then my possible valus are Yes, No, and N/A.

Any clues?

Thanks,
John

SELECT TOP 100
OfferDressRoomYN.yesno as OfferDressRoom
= CASE
WHEN offerDressRoomYN.yesno IS NULL THEN 'N/A'
END,
FROM dataquestionnaire dq
LEFT OUTER JOIN yesno OfferDressRoomYN ON dq.c3_1 = OfferDressRoomYN.yesnoid

View Replies !

CASE Statement
Hello All,

I have a condition for which I am trying to write a case statement as follows..

SELECT @Segment_Field = 'Acct_Status_' + CASE @Public_record_Type
WHEN NULL THEN ' '
WHEN 'BP' THEN 'BP' + ((CASE @Bankruptcy_Type WHEN NULL THEN ' ' ELSE '1' END) OR (CASE @Acct_Status WHEN NULL THEN '' ELSE '2' END))
END

But this is not working.The condition is actually tht I have to set the value for the variable @Segment_Field acc to the value of variables @Bankruptcy_Type and @Acct_Status. It should first check for @Bankruptcy_Type and if it is not null then the value should be Acct_Status_BP1.Then it should check for @Acct_Status and if it is not sull then the vlaue should be set to Acct_Status_BP2.

Can somebody please help..

Thanks

View Replies !

CASE Statement With IN/OR
Hello,

I'm trying to write a query with case statement.

the condition is

when project_ref =393 then select qtn_ref in (7070000,7060000))
and when project_ref =391 and select q.qtn_ref=8700000

I need this condition in 'WHERE' statement.

I can use 2 SELECT queries using 'IF ELSE', but I woud like to find out if there's any way to use CASE so I can write 1 query.

Tring to do something like this but it doesn't work.

SELECT *

FROM table

WHERE qtn_ref = case when project_ref =393 then 7070000 or 7060000
when project_ref =391 then 8700000

Anyone can help, please?

View Replies !

Case Statement
Hi!

      I need a case that returns the result of a select if it is not null, and -1 if it is null. I did it this way:

select

    case

       when(select column from table where conditions) is null then -1

       else(select column from table where conditions)

But it doesn't seem very clever to repeat the select statement. Is there any way I can do it without repeating the "select column from table where conditions"???

            Thank you!

View Replies !

SQL CASE Statement
Hi,

The below CASE statement should check if the @Period variable is set to a monday date.
If it is then I want to get data where r.ReceivedDate is set to a Friday date.

What is wrong with the below syntax?

Get this error...
Incorrect syntax near '='.

Code Snippet

WHERE (p.Name = 'test')
AND
CASE WHEN
LEFT(DATENAME(dw, @Period), 3) = "MON"
THEN
(CONVERT(VARCHAR(10), r.ReceivedDate, 103) = DATEADD(day, -3, @Period)
ELSE
(CONVERT(VARCHAR(10), r.ReceivedDate, 103) = @Period)

View Replies !

T-SQL - CASE Statement

I need to execute a simple Case Statement. Strangely, if I include a comparison operator I receive an error message showing a syntax error (Message 102, Level 15). But the same statement without the operator works jsut fine.

Works:
Select LastName, Group =
Case Age
When 15 Then '1'
When 18 Then '2'
Else '20'
From tblAgeGroup
End

Does Not Work:

Select LastName, Group =
Case Age
When <15 Then '1'
When <18 Then '2'
Else '20'
From tblAgeGroup
End

Also, I noticed that this does not work in any other combination of the "When.." clause.

Any help is appreciated.

Thanks

View Replies !

Is Case Statement The Only Way

Hi

I need to generate a SQL report like below,its basically calculating the count of students

For

District Level

then for

Region Level

then for

Each School under a Region

Like the Display Below

District summary

Total

Male

Femal

Indian

White

Asian

--

--

--

Type1

22

33

22

11

11

11

23

11

13

Type2

2

€¦6

€¦7

;;;;

13

14

Region 1

Region 2...................Region 8

Each School

School1....School 15

Do I have to have

case for each Type for each Race

and then

for Each Levels

of

District

Region

And 500 Schools??

Please Help

Thanks

View Replies !

Need Help Using Case Statement
I've these following table
SEL
Date_Taken | Main_ID | Time | Daily_Rainfall
---------------------------------------------------
...
...
...
4/3/2005 | 202 | 1015 | 2
4/3/2005 | 202 | 1045 | 2
4/3/2005 | 202 | 1215 | 7
4/3/2005 | 203 | 715 | 2
4/3/2005 | 203 | 1345 | 2
4/3/2005 | 203 | 1530 | 7
...
...
...
5/29/2005 | 203 | 1100 | 56
5/29/2005 | 203 | 1130 | 156
5/29/2005 | 203 | 1145 | 256
...
...
...

Station_Info
State | Main_ID
--------------------
SEL | 202
SEL | 203
SEL | 204
SEL | 205
SEL | 209

Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
------------------------------------------------------------------------------
202| 4/30/2005 | 525.8 | 683.11 | 356.33 | 754.33
203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34
204| 4/30/2005 | 265.53 | 453.21 | 543.66 | 753.24
205| 4/30/2005 | 251.38 | 754.33 | 478.34 | 785.22
209| 4/30/2005 | 259.5 | 356.34 | 894.33 | 354.78
202| 5/30/2005 | 565.8 | 383.11 | 756.33 | 254.33
203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34

This query only display Max(Daily_Rainfall) per day from SEL

SELECT t1.MAIN_ID,t1.DATE_TAKEN,t1.TIME,
t1.DAILY_RAINFALL,
CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3
THEN t3.[3MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=6
THEN t3.[6MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=9
THEN t3.[9MthCumRf]
WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=12
THEN t3.[12MthCumRf]
END AS CumRf
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY DATE_TAKEN,MAIN_ID
ORDER BY DAILY_RAINFALL DESC) AS RowNo,* FROM dbo.SEL)t1
INNER JOIN dbo.STATION_INFO t2 ON t2.MAIN_ID=t1.MAIN_ID AND
t1.RowNo=1 INNER JOIN dbo.LT t3 ON t3.STATION_ID =t2.STATION_ID
AND t1.DATE_TAKEN>=CONVERT(VARCHAR(10),DATEADD(m,-3,GETDATE()),101)
ANDt1.DATE_TAKEN<CONVERT(VARCHAR(10), GETDATE(), 101)
WHERE t2.STATE='SEL'
ORDER BY MAIN_ID,DATE_TAKEN,TIME

In my query, i still can query if last3month using CASE WHEN DATEDIFF(mm,t1.DATE_TAKEN,GETDATE()) <=3 THEN t3.[3MthCumRf]

How to adjust my CASE statement
if Main_ID=203, Date_Taken=5/5/2005,

i should pickup following rows
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
-------------------------------------------------------------------
203| 5/30/2005 | 485.5 | 444.9 | 744.34 | 755.34

if Main_ID=203, Date_Taken=4/5/2005,
i should pickup following rows
Main_ID | Month_Year | 3MthCumRf | 6MthCumRf | 9MthCumRf | 12MthCumRf
203| 4/30/2005 | 435.5 | 400.9 | 290.34 | 234.34

Need someone help... plz

View Replies !

Case Statement
Im running the following sql statement but I dont see the
expected output. There are few differences between acc & cl1, mcc & cl2 , ncr & cl3 but I dont see either 'ONE' or 'TWO' or 'THREE'.
There is even a case where cl3 is null but the sql is not filling in either one or two or three. Query simply returns id & rest as null values.

SELECT P1.id,
CASE
WHEN p1.acc!= p1.cl1 then 'ONE'
WHEN p1.mcc!= p1.cl2 then 'TWO'
when p1.ncr!= p1.cl3 then 'THREE'
Else NULL END
As NOnMatchingColumn
from
(select id, acc, cl1,mcc,cl2,ncr,cl3 from dbo.ml)P1

View Replies !

Case Statement
I have a simple query with a case statement:
Select
Case
when HR.PREM_CUR_QUOTED_AM <> 0 and HR.PREM_LOST_AUTO_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0 and HR.PREMIUM_BOUND_AM <> 0
THen 0
else 1
end Cur_check
from dbo.THIT_RATIO_DETL HR

In the where clause i want to to put
where cur_check = 1

but i'm getting an error that says :
Invalid column name 'Cur_check'

Can you use a case statement column in the where clause?

View Replies !

Case Statement
I am very new at SQL and I need some help if you can. A lot of my case statements have not been working for me. When I run my query the new names do post into the PRO_XTYP field. But when I want to add a criteria into my where clause it does not recognize the change. I have been trying to figure this out. My where clause would beâ€¦â€¦ Where Pro_XTYP = â€˜Ancilaryâ€™ (nothing shows), but when I put Pro_XTYP = â€˜ANCIâ€™ then data pops up. Why isnâ€™t my labels working? Do I need to write a IF Clause? Below is my case statement.

Pro_XTYP =
Case
When Pro_XTYP = 'PRIM' then 'Physician'
When Pro_XTYP = 'PROV' then 'Physician'
When Pro_XTYP = 'SPEC' then 'Physician'
When Pro_XTYP = 'ANCI' then 'Ancillary'
When Pro_XTYP = 'HOSP' then 'Hospital'
else Pro_XTYP
End,

View Replies !

Case Statement Help
select * into #t1
from pCodes

update #t1
set postcode = left(postcode,(charindex(' ', postcode)+1) + ' ' + right(postcode,3)) from #t1
where len(postcode) = 6

update #t1
set postcode = left(postcode,(4)-1) + ' ' + right(postcode,3) from #t1
where len(postcode) = 7

whats wrong with my case statment below cant seem to get it working!

SELECT
CASE POSTCODE WHEN LEN(POSTCODE) = 6 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3),
CASE POSTCODE WHEN LEN(POSTCODE) = 7 THEN left(postcode,(charindex(' ', postcode) -1 )) + ' ' + right(postcode,3),
END
FROM #T1

View Replies !