Use 'select Case' Statment In Sql Query.
Mar 18, 2005hi,friend
is it possible to use 'select case' statment in sql query.
give any idea or solution.
thanks in advance.
ADVERTISEMENT
Using Case Statment
Apr 7, 2008Hi There i have a query that uses conditional Select statment my query is:
Select A,B,C case
when '1' then 'one'
when '2' then 'two'
end
from table
what if i want to include addional condition in the case condition like C case
when '1' and A is null then 'C is one and A is null'
Thank you :D
Case Statment - Not Sure If I Should Use It Or Not
Oct 30, 2007I am trying to have all these calulcation int one column under AccessFeeFinal, but it is not working. It was working when I just had the one case statement, but we needed to add another one in because we were getting some blank info in the column. Below are the two statement then I added a third on to show you how I think it should be, but it is not working for me. Please help me out if you can. I was thinking maybe I needed to use an IF Statement.
CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,
CASE clm_att1
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,
CASE clm_att1
WHEN 'NG' THEN (clm_H30)*(clio_fee04/100)
WHEN 'NG' THEN (clm_sppo)*(clio_fee04/100)
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN (clm_H30)*(clio_fee04/100)*.65
WHEN 'AF' THEN (clm_sppo)*(clio_fee04/100)*.65
ELSE Null
END as AccessFeeFinal,
Thanks
Wendy
CASE Statment
Oct 18, 2007Any ideas why i am getting an error
set dateformat YMD
set datefirst 7
CREATE TABLE #ChildSessions (
siteid integer null
,childid integer null
,sessionid integer null
,sun integer default 0
,mon integer default 0
,tue integer default 0
,wed integer default 0
,thr integer default 0
,fri integer default 0
,sat integer default 0)
declare @firstofweek as datetime
declare @lastofweek as datetime
--select datepart(dw,getdate())
select @firstofweek=cast(floor(cast(dateadd(day,(-1*datepart(dw,getdate())+1),getdate()) as float)) as datetime)
select @lastofweek=dateadd(minute,-1,dateadd(day,7,@firstofweek))
declare @myday integer
set @myday=0
while @myday<7
BEGIN
INSERT INTO #childSessions
SELECT
c.siteid
,c.childid
,sg.sessionid
,case @myday WHEN 1 THEN 1 ELSE 0 end
,case @myday WHEN 2 THEN 1 ELSE 0 end
,case @myday WHEN 3 THEN 1 ELSE 0 end
,case @myday WHEN 4 THEN 1 ELSE 0 end
,case @myday WHEN 5 THEN 1 ELSE 0 end
,case @myday WHEN 6 THEN 1 ELSE 0 end
,case @myday WHEN 7 THEN 1 ELSE 0 end
FROM
child c
,sessionAttendance sa
,session s
,sessiongroup sg
WHERE
c.childID = sa.childid
AND c.siteid = sa.siteid
AND c.active = 1
AND c.potential = 0
AND s.identityid = sa.identityid
AND s.siteid = sa.siteid
AND sg.sessionid = s.sessionID
AND sg.siteid = s.siteid
AND s.dayofweek = @myday
AND @firstofweek <= sa.dateTo
AND @lastofweek >= sa.dateFrom
SET @myday=@myday+1
END
SELECT
c.forename,
c.surname,
sg.sessionname,
CASE (sum(sun)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(mon)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(tue)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(wed)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(thr)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(fri)) WHEN 0 THEN ' ' ELSE 'X',
CASE (sum(sat)) WHEN 0 THEN ' ' ELSE 'X'
FROM child c,sessiongroup sg,#childsessions cs
WHERE c.childid=cs.childid
AND c.siteid=cs.siteid
AND sg.sessionid=cs.sessionid
AND sg.siteid=cs.siteid
GROUP BY c.forename,c.surname,sg.sessionname
ORDER BY sg.sessionname,c.forename,c.surname
DROP TABLE #childsessions
Msg 102, Level 15, State 1, Line 66
Incorrect syntax near ','.
How To Use CASE Statment?
May 13, 2008HI,
I have a problem with using CASE in my database,
Here is the code that I am using but not working
SELECT Computers.[Computer ID], Computers.Model, Computers.[Dell Service Tag], Users.[First Name] CASE WHEN Users.[first name] is NULL THEN Users.[user ID] ELSE Users.[First Name]+' '+Users.[Last Name] AS NAME ENDFROM Computers INNER JOIN Users ON Computers.[Computer ID]=Users.[Computer ID]ORDER BY Users.[First Name];
Could you please tell me where is wrong?Thanks in advance,Azi
Case Statment Need Rounded
Feb 6, 2008I have this case statement below it is for an access fee. My problem that I am having is that now I am getting a number that is huge. Example, 8.2350000000. I need this to be rounded up and numbers srinked to 8.24, how can I add that in my case statement below? Please help if you can.
Thanks
CASE clm_att1
WHEN 'NG' THEN CASE clm_h30 WHEN 0 THEN cast(clm_sppo as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
ELSE cast(clm_H30 as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
END
WHEN 'NA' THEN '0.00'
WHEN 'AF' THEN CASE clm_h30 WHEN 0 THEN cast(clm_sppo as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)
ELSE cast(clm_H30 as decimal(12,2))*(cast(clio_fee04 as decimal (12,2))/100)*.65
END
ELSE Null
END AS AccessFeeFinal,
Local Variables In Case Statment
Nov 11, 2004Hi
I am trying to use a global variable in a case when and am not getting the correct results. If I use static data, it works fine.
Here is a tableless example, which should return Shipper. Any ideas are appreciated.
Code:
declare @shipperGBS varchar(3000)
declare @sQuote char
set @sQuote = char(39)
set @shipperGBS = 'ACI,ADO,ALD,AMS,AWB'
set @shipperGBS = Replace(@shipperGBS, ',', @sQuote + ',' + @sQuote)
set @shipperGBS = @sQuote + @shipperGBS + @sQuote
select case when ('ACI' IN (@shipperGBS)) then 'Shipper' else 'Consignee' end as ClientCharge
Can I Set A Variable Equal To A Case Statment?
Feb 25, 2008I have a case statement I need to refer to several times in my select statement and it's quite long. I would like to just refer to if by name. I tried to create a variable for the Case statement (see below) but I get an error that says, "
Sub query returned more than 1 value. This is not permitted when the sub query follows =, !=, <, <= , >, >= or when the sub query is used as an expression."
USE GP05
GO
DECLARE @EmpID nvarchar (6)
SET @EmpID = (SELECT CASE WHEN NOT(dbo.BE010130.EMPID_I IS NULL) THEN dbo.BE010130.EMPID_I
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND dbo.UPR00600.EMPLOYID IS NULL THEN dbo.UPR00500.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND dbo.UPR00500.EMPLOYID IS NULL AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00600.EMPLOYID
WHEN dbo.BE010130.EMPID_I IS NULL AND NOT(dbo.UPR00500.EMPLOYID IS NULL) AND NOT(dbo.UPR00600.EMPLOYID IS NULL) THEN dbo.UPR00500.EMPLOYID
END FROM dbo.BE010130 FULL OUTER JOIN dbo.UPR00500 ON dbo.BE010130.EMPID_I = dbo.UPR00500.EMPLOYID AND dbo.BE010130.BENEFIT = dbo.UPR00500.DEDUCTON
FULL OUTER JOIN dbo.UPR00600 ON dbo.BE010130.BENEFIT = dbo.UPR00600.BENEFIT AND dbo.BE010130.EMPID_I = dbo.UPR00600.EMPLOYID)
SELECT @EmpID
I can get around this by simply retyping the case statement when I refer to it in the where clause, but I would prefer not to do this. Can someone point a newbie in the right direction? I have 5 case statements in this query and it starts to look pretty ugly when you have to retype them multiple times.
Thanks!
SELECT QUERY Without USING CASE
May 29, 2006I have a select query
select col1-tbl2,col2-tbl3,col3-tbl4 ,col4-tbl1,col5-tbl1 from tbl1where condition,
The above quey col1-tbl2,col2-tbl3,col3-tbl4 are from table1 only, but it have only id(ie) the foreign key references of tables 2,3,4
I want the column with their values to join with the tables 2,3,4.
Except USING CASE
Except USING CASE
Please Guide me
Can Anyone Tell Me How To Use 'Case' Statement In Select Query In SQL ?
Mar 14, 2008I need to pull one field from one table and one field from another table that is i need to pull 'eGroupName' field from 'Exception' Table and 'eGroup Description' field from 'eGroup' Table but there is no connection between these two tables means there is no forign key relationship between these two tables but i need to pull both fields . If i use INNER JOIN i need to mention relationship between both tables right? so how to write query for this , and one more thing is i need to add an extra column as "Location"which is not there in either of tables for that i need to use CASE Statement as if DataSource = 1 then "ABC" else "BCD" . pls help me out in writing SQL Statement???
is this correct ?? its showing me errors
Select Exception.eGroupName, eGroup.eGroupDescription from Exception Inner Join eGroup ON ???
(case when 'DataSource =1' then 'ABC' then 'BCD' endcase)
Where .....
Pls correct me
Thanks
Using A CASE Statement Within A Select Query
Jul 25, 2006Hi folks,
Hope you are all well.
I am using a CASE statement within a SELECT query to sum up values for different customers.
SELECT CR_CUST.Customer_Code,
'General_01' = CASE WHEN CR_PROD.Part_Class_Code = '01' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
'General_07' = CASE WHEN CR_PROD.Part_Class_Code = '07' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END,
'General_08' = CASE WHEN CR_PROD.Part_Class_Code = '08' THEN SUM(CR_INVOICE.Line_Value) ELSE 0 END
FROM CR_CUST
INNER JOIN CR_INVOICE ON CR_CUST.Customer_Code = CR_INVOICE.Customer_Code
INNER JOIN CR_PROD ON CR_INVOICE.Product_Code = CR_PROD.Product_Code
WHERE (CR_PROD.Part_Class_Code = 1 OR
CR_PROD.Part_Class_Code = 7 OR
CR_PROD.Part_Class_Code = 8)
GROUP BY CR_CUST.Customer_Code,
CR_PROD.Part_Class_Code
The above query produces the following results...
Customer_Code General_01 General_07 General_08
------------- ---------------- ---------------- ----------------
02210 10074.30 .00 .00
02347 7606.49 .00 .00
02210 .00 12618.42 .00
02347 .00 13131.63 .00
02210 .00 .00 4505.44
02347 .00 .00 5018.03
My question is this - is it possible to expand my SQL Query into a Sub Query so that each customers data appears on the same line of the results?, like so...
Customer_Code General_01 General_07 General_08
------------- ---------------- ---------------- ----------------
02210 10074.30 12618.42 4505.44
02347 7606.49 13131.63 5018.03
I can achieve this by writing my results into a temporary table and extracting the data with the following SQL Query, but I just thought it would be really cool if I could do it in one SQL Statement without using a temporary table.
SELECT Customer_Code,
SUM(General_01),
SUM(General_07),
SUM(General_08)
FROM #MyTempTable
GROUP BY Customer_Code
Thanks in advance,
Kev
T-SQL CASE Statement.. NOT In A SELECT Query - ??
Feb 4, 2008How come SQL Server doesn't like the following?
CREATE PROCEDURE sp_myproc
(@myvar int)
AS
CASE @myvar
WHEN 1
EXEC sp1
WHEN 2
EXEC sp2
WHEN 3
EXEC sp3
DEFAULT
EXEC sp3
END
How can I code something like this legally in T-SQL?
Thanks
Jason
Using SQL Query Columns In Select Case Statements
Jun 5, 2006I am using Visual Web Developer Express 2005 as a test environment. I have it connected to a SQL 2000 server. I would like to use a Select Case Statement with the name of a column from a SQL Query as the Case Trigger. Assuming the SQLDataSource is named tCOTSSoftware and the column I want to use is Type, it would look like the following in classic ASP:
Select Case tCOTSSoftware("Type")
Case 1
execute an SQL Update Command
Case 2
execute a different SQL Update Command
End Select
What would a comparable ASP.Net (Visual Basic) statement look like? How would I access the column name used in the SQLDataSource?
Need Help Converting A Select Query Into A Case Statement
Nov 24, 2004I have the following query:
(SELECT MIN(CFGDates.AccountPdEnd)
FROM CFGDates LEFT JOIN
AR ON AR.Period = CFGDates.Period
WHERE AR.Period = '200408')
I need to convert this into a case statement.
I tried various ways but did not get the result that I was after
Thanks,
Laura
SELECT Query Including JOIN And CASE Expression
Aug 12, 2012An error is entered into the table, across two tables - tblErrors_ER and tblPolicyNumbers_ER - each error generates a PK (ErrorID) and can have any number of policy numbers which will be referenced by its own PK but linked to each error by its FK (ErrorID).I want to display each error in a Gridview in ASP.Net - columns included will be ErrorID, ErrorType, DateLogged from tblErrors_ER and PolicyNumber from tblPolicyNumbers_ER.If an Error has more than one policy number I only want to show the error once in the GridView with the word MULTIPLE under policy number.
tblErrors_ER
---------------
CREATE TABLE tblErrors_ER
{
ErrorID int,
ErrorType varchar(255),
DateLogged datetime,
[code]...
I have changed the Count(*) to Count(tblPolicyNumbers_ER.POlicyNUmber) which gives me the same undesired result as above. I have also left it as Count(*) and the entire CASE expression within the GROUP BY statement as suggest above which generated an error saying I can not use an expression in a group by clause.
If I leave Count(*) = 1 where it is in the original SELECT statement but swap the = for > then something happens, close to what I require but not as intended. It returns:
ErrorID ErrorType DateLogged PolicyNumber
---------------------------------------------------------------
1 Test 08/08/2012 Multiple
2 Test 08/08/2012 Multiple
this would suggest the original syntax is close to being accurate but I can not get it to work.
SELECT Statment
Mar 20, 2007Hello, Is there an SELECT statement to just return the last 100 row in my tables? I have about 500 rows in my tables and I only need the info on the last 100 rows.
Thanks
Steve
A SELECT Statment, Please Help Me
Oct 2, 2007hello,
i have a page "Picture.aspx?PictureID=4"
i have a FormView witch shows details about that picture and uses a stored procedure with input parameter the "@PictureID" token from query string
the Pictures table has among other rows "PictureID", "UserID" - uniqueidentifier - from witch user the picture belongs to
i have a second FormView on the same page, witch should show "other pictures from the same user" and uses a Stored Procedure
how should i write that stored procedure...frist to take the UserID from the picture with PictureID=4, then to pass it as input parameter and select the pictures witch has as owner the user with that UserID, and if can be done, to avoid showing the PictureID=4 again
a solution should be to add at querry the UserID too, but i want to avoid that
any sugestion is welcomed, please help me
THANKS
Help On Sql Select Statment
Apr 20, 2007Okay here is the deal. I need to take all data from tbl 1 and match it to data in 3 other tbls. I need to have it return everything back to me even if it is null....
IE tbl 1 I match the invoice_num to tbl2 site_id and then tbl2 marekt to tbl3 market. however even if tbl1 invoice_num dose not match tb2 site_id I still need to have it retun to a null value to the site_id. Here is what I have so far. This will return everything where the invoice_num and site_id match.
Code:
Select distinct t1.ID,t1.Deposit_date,t1.Ref_Num,t1.Company,t1.check_num,t1.Check_Date,t1.Check_Date,t1.Check_Total, t1.Individual_PMT,t1.Invoice_Num,t1.Invoice_Desc,
t2.site_id,t3.CompanyCode,t3.CostCenter
From( PMTK_tbl as t1
Left Join Leaseinfo as t2 on t2.site_id = t1.Invoice_Num)
inner Join CostCenters AS t3
on t2.market = t3.market and t2.market_region = t3.RegionCode
Help With Select Statment
May 23, 2008whats wrong with this statment?
declare @alpha as nvarchar(50)
set @alpha = select top 1 monthyear from dbo.Performance_Indicators_Rolling order by recordkey
I get an error
Incorrect syntax near the keyword 'select'.
Can anyone please help?
Help With A SELECT Statment
Jul 17, 2007Hi all,
i'm new to SQL but i've been asked to write an SQL statement to select the latest numeric version value(in this case version 3) from this table, any help?
Name Version Episode
John 4c 60
John 4b 50
John 4a 40
John 3 30
John 2 20
John 1 10
Regards
Help With Select Statment
Mar 8, 2008Im trying to get a count of all user logins to display in a report - I have a column, count, which has a value of 1 for each record entered.
Select firstname, lastname, count(count) as TotalLogins Order by TotalLogins. But the count is always appearing as 1.
Report should look like:
John Smith 132
Jane Doe 101
Doris Day 99
Question:select Where Another Select Statment
Jan 18, 2008Hello all
I create sp
--------------------code----------------------
ALTER procedure [dbo].[uspInviteGroup] --uspInviteGroup 'fdi'
@strUserId nvarchar(50)=null
as
select GroupName as 'strGroupName',GroupFounder as 'strGroupFounder'
from SITE_MemberGroupswhere GroupId=
(select GroupId from SITE_GroupMember
where userId=@strUserId)
--------------------code----------------------
but when I tested the above sp --uspInviteGroup 'fdi'return this error
------------------error---------------------
Msg 512, Level 16, State 1, Procedure uspInviteGroup, Line 6
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
------------------error---------------------
in my case the second select statment return 2 value,I need the first select statment return two row
how can I do that?
thank you
Custom Select Statment
Feb 18, 2007Hello,
Why when I make a custom select statment with multiple tables, that when I test my query I'm shown the same rolls multiple times. When I make a custom query with just one table everything works just fine. I don't have any primary keys or restraints or whatever.
SELECT * FROM table1, table2, table3 Does not work(shows each row multiple times)
SELECT * FROM table1 works just fine (shows each row just one time)
Thanks
Steve
Add Cases To Select Statment
Jun 8, 2006I need to add some cases to the select statment for cpeorderstatus:
Here is my Select statement:
"SELECT O.ORDERID, C.FIRSTNAME, C.LASTNAME, O.CLIENTORDERID AS CRMORDERID, TO_CHAR(O.ORDERDATE, 'YYYYMMDD') AS CPEORDERDATE, TO_CHAR(O.SHIPDATE, 'YYYYMMDD') AS SHIPDATE, O.TRACKINGNBR AS TRACKINGNUMBER, O.SHIPNAME AS CARRIER, OI.ITEM AS CPEORDERTYPE, OI.QTY, O.STATUS AS CPEORDERSTATUS, OSN.ORD_SERIAL_NO AS SERIALNUMBER, C.BTN AS BTN, C.FIRSTNAME AS FIRST, C.LASTNAME AS LAST, C.SHIPADDR1 AS ADDRESSLINE1, C.SHIPADDR2 AS ADDRESSLINE2, C.CITY AS CITY, C.STATE AS STATE, C.ZIP AS ZIP, TO_CHAR(R.ISSUEDATE, 'YYYYMMDD') AS ISSUEDATE, R.RMA_ID AS RMANUMBER, R.RMA_REASON AS REASON, TO_CHAR(R.RETURNDATE, 'YYYYMMDD') AS RETURNDATE FROM SELF.ORDERS O, SELF.CUSTOMER C, SELF.ORDERITEM OI, SELF.ORD_SERIAL_NUMBER OSN, SELF.RMA R WHERE O.CUSTID = C.CUSTID AND O.ORDERID = OI.ORDERID AND O.ORDERID = OSN.ORDER_ID (+) AND O.ORDERID = R.ORDER_ID (+) AND (C.CUSTID IN (SELECT C.CUSTID FROM SELF.CUSTOMER C WHERE C.BTN='{0}')) ORDER BY O.ORDERDATE DESC"
I need to add multiple cases to cpeorderstatus, five different cases. Cane anyonye HELP
Using A Select Statment For The Name Of The Field?
Jul 20, 2005Do any of you know if it's possible to get the name of your field fromanother table?E.g.Select FYear as [Select description from YearTable where category=1]I need the name of the fields to be user defined and the only way Ican see this happening is through a table. But now...how do I get thefield name from the table?Any help would be appreciated!!!!Thank you,Susan
View 2 Replies View Related&& Character In Select Statment
Sep 21, 2006Hi Folks
If I wrote this in QA what does it mean?
Declare @X INT
Set @X = 2
Select (@X & 8)
What & do in this select statment
Thank you
Simple Select Statment
May 1, 2008can someone read this to me thanks.
SELECT DISTINCT PlanNumber FROM tbmembers WHERE ISNULL(PlanID, '') <> ''
SQL Command Select Statment Problem VB
Feb 19, 2008Hi,
I have a Command that I can not get to work. I am trying to take an AVERAGE of 1 Columb and display it in a label.
ALSO what statement do I add when the Lable that is supposed to display that output is empty. Right now It gives me an Error.comm = New SqlCommand("SELECT * AVG Rating FROM Reviews WHERE CID = " & Request.QueryString("CID"), conn)
Dim SqlDataAdapter1 As New SqlDataAdapter("SELECT AVG Rating FROM Company WHERE CID = " & Request.QueryString("CID"), conn)Dim objReader As SqlDataReader
conn.Open()
objReader = comm.ExecuteReader()
objReader.Read()lblRanking.Text = objReader("Rating")
objReader.Close()
Setting The Value Of A Variable In A Select Statment...
Oct 19, 2001I want to be able to have a single select statment:
SELECT TOP 1 Call.JobNum, Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID
but with this select statment I also want to set a variable:
declare @variable int
SELECT TOP 1 Call.JobNum, @variable = Call.CallID, Call.Company, Call.LastCallTime
FROM ClientJob INNER JOIN Client ON ClientJob.ClientID = Client.ClientID
INNER JOIN Call INNER JOIN Login ON Call.JobNum = Login.JobNum ON ClientJob.JobNum = Login.JobNum
WHERE (Login.LoginID = 3) AND (Call.Status = 0) AND (DATEDIFF(hh, Call.LastCallTime, getdate()) > 10)
ORDER BY Call.CallID
Now SQL Server does not like this, can not set a variable in a multiple select statment. I NEED to do this all in one step if possible. Any suggestions?
pat
Seting A Variable Value From A Select Statment
Aug 1, 2006In my sproc this is wrong :
SELECT @DetailItems= Count(idDetail)
FROM Details
WHERE CheckNum=@CheckNumber
How should i do this?
Create PROC voidCks
@CheckNumbervarchar(30)
AS
DECLARE @DetailItemsint,
@DetailTotalMONEY,
@CheckAmountMONEY,
SELECT @DetailItems= Count(idDetail)
FROM Details
WHERE CheckNum=@CheckNumber
How This Select Statment Works Please It Is Urgent?
Apr 21, 2007hey friends!
pleaze help, i am using sql server 2000.i have tried to fix my problem for more than five weeks just to solve for one problem and just still now it is unsolved, opps my due date is almost approaching, i don't know what to do more than what i did, i have search through the net, but still i did not get the correct answer to my problem, friends please just forward your answer to me, it may be best answer to my question .
create table mytable( english varchar(120), tigrigna Nvarchar(120))
insert into mytable values('peace',N'sälam')
insert into mytable values('kiss',N'samä ')
insert into mytable values('to kiss each other',N'täsasamä ')
then
select * from mytable where english='peace'; this works fine
but
select * from mytable where tigrigna=N'sälam'; this doesnot work;
select * from mytable where tigrigna='sälam'; this also doesnot work,
so what should i do to select this unicode select statement?
tigrigna is one of the spoken language in east africa(ethiopia).
Hopefully, i have joined this forum today, and just looking for your reply
I am Looking for your reply !
Two Join Types In One Select Statment
Mar 7, 2007Will writes "I have an employees table, which gives all emp. ids. I have a second table, time_log with tasks each employee has logged:
empID date log_time duration etc
===================================
and a 3rd table - a pivot table with a single column named "i" containing consecutive integers from 0 - 1000
I need to know for each date in a series, e.g seven consecutive days, how much time each has logged. easy if everyone has logged a task for every day, but I need to include every day where they have not logged a task.
so, a cartesian join on all the dates in a series(produced using dateadd on p.i and the pivot table)
SELECT dateadd('d',p.i, #02/19/2007#), e.empID
FROM pivot1 p, employees e
WHERE i<no_dates
However I need to do a left join with time log where the date and employee ids are the same, and I have summed the durations for each date. The following query does this, but does not include dates and times where nothing has been logged.
SELECT empID, log_date, sum(duration)
FROM time_log
GROUP BY empEIN, log_date
GIVING, EVENTUALLY, ALL DATES AND EMPIDS AND THE TOTAL AMOUNT OF TIME THEY HAVE LOGGED FOR EACH DAY."
Hierarchical Selection Within A Select Statment
Jun 7, 2007CREATE TABLE RS_A(ColA char(1), ColB varchar(10))INSERT INTO RS_AVALUES ('S', 'shakespeare')INSERT INTO RS_AVALUES ('B', 'shakespeare')INSERT INTO RS_AVALUES ('P', 'shakespeare')INSERT INTO RS_AVALUES ('S', 'milton')INSERT INTO RS_AVALUES ('P', 'milton')INSERT INTO RS_AVALUES ('B', 'shelley')INSERT INTO RS_AVALUES ('B', 'kafka')INSERT INTO RS_AVALUES ('S', 'kafka')INSERT INTO RS_AVALUES ('P', 'tennyson')SELECT * FROM RS_ANow i need a select which selects based on hierarchyif ColA = 'S', then select only that rowelse if ColA = 'B' then select only that rowelse if colA = 'P' then select only that rowSo my results should look likeS shakespeareS miltonB shelleyS kafkaP tennysonIs there a way to do this within a select statementI tried using a CASE in WHERE CLAUSE but it put out all rows whichexisted/If any of you can help me with this right away, its is greatlyappreciatedThanks in advance
View 2 Replies View Related