Setting The Value Of A Variable In A Select Statment...
Oct 19, 2001
I 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
View 1 Replies
ADVERTISEMENT
Aug 1, 2006
In 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
View 3 Replies
View Related
Jun 21, 2002
Good morning;
My Problem is :im my transaction i use insert code like the following :
" Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in
(Select ORDN from OTAB where USER_ID = 'MIKE')"
In order to optimise my code ,
instead of using a subquery in my select
(I have different insert in my transaction with the same subquery).
I would like to DECLARE a varibale which will contain the select of the Subquery.
and then use it im my different insert. some thing like this.
" BEGIN TRANSACTION
DECLARE @OrdSelect int
Set @OrdSelect = (Select ORDN from OTAB where USER_ID = 'MIKE')
Insert into TAB1 (F1,F2,F3)
select a.F1,b.F2,b.F3 from TAB2 a,TAB3 b
where a.KY1= b.KY1 and b.ORDN in @OrdSelect
COMMIT Tran "
I know that the @OrdSelect will receive the last value of the select not an array of values. which will make my transaction incorrect.
I dont want to use Cursor to resolve this issue Too.
Thinks.
View 1 Replies
View Related
Apr 20, 2001
Using SQL Server 7 I am trying to modify an existing stored proc and make it more flexible. The below example represents the first part of that proc. The temp table that it should return is then used by another part of the proc (this query represents the foundation of my procedure). I need to figure a way to change the SQL Select statement, choosing between C.CONTRACTCODE and CB.EMPLOYERCODE on the fly. The query below will run but no records are returned. I am starting to believe/understand that I may not be able to use the @option variable the way I am currently.
I've tried creating two SQL statements, assigning them as strings to the @option variable, and using EXEC(@option). The only problem with this is that my temp table (#savingsdata1) goes out of scope as soon as the EXEC command is complete (which means I can not utilize the results for the rest of the procedure). Does anyone know how I can modify my procedure and incorporate the flexibility I've described?
Thanks,
Oliver
CREATE PROCEDURE test
@ContractCode varchar(10),
@dtFrom datetime,
@dtTo datetime,
@Umbrella int
AS
declare @option varchar(900)
if @umbrella = 0
set @option = 'c.contractcode'
else
set @option = 'cb.employercode'
select
c.claimsno,
c.attenddoctor,
c.patientcode,
p.sex,
cb.employercode
into #SavingsData1
from claimsa c inner join Patient p
on c.patientcode = p.patientcode
inner join claimsb cb on c.claimsno = cb.claimno
where
@option = @ContractCode and c.dateentered between @dtFrom and @dtTo
and c.claimsno like 'P%' and p.sex in('M','F') and c.attenddoctor <> 'ZZZZ'
select * from #SavingsData1
View 1 Replies
View Related
Feb 25, 2008
I 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!
View 9 Replies
View Related
Mar 20, 2007
Hello, 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
View 4 Replies
View Related
Oct 2, 2007
hello,
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
View 5 Replies
View Related
Apr 20, 2007
Okay 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
View 4 Replies
View Related
May 23, 2008
whats 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?
View 3 Replies
View Related
Jul 17, 2007
Hi 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
View 7 Replies
View Related
Mar 8, 2008
Im 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
View 19 Replies
View Related
Jan 18, 2008
Hello 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
View 3 Replies
View Related
Feb 18, 2007
Hello,
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
View 1 Replies
View Related
Jun 8, 2006
I 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
View 1 Replies
View Related
Jul 20, 2005
Do 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
Sep 21, 2006
Hi 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
View 5 Replies
View Related
May 1, 2008
can someone read this to me thanks.
SELECT DISTINCT PlanNumber FROM tbmembers WHERE ISNULL(PlanID, '') <> ''
View 4 Replies
View Related
Feb 19, 2008
Hi,
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()
View 3 Replies
View Related
Mar 18, 2005
hi,friend
is it possible to use 'select case' statment in sql query.
give any idea or solution.
thanks in advance.
View 2 Replies
View Related
Apr 21, 2007
hey 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 !
View 1 Replies
View Related
Mar 7, 2007
Will 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."
View 1 Replies
View Related
Jun 7, 2007
CREATE 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
Dec 4, 2007
I'll try to explane what I am looking for.
I'd like to select from a table, let say with 2 variables. One with a currency and one with integer. The integer value is a number between 0 and 255. The integer value represents a value of what kind of BIT is turned on in one BYTE.
Down to business.
I am looking for all BYTES with the fifth BIT = 1 as in example 00001000.This represents the value of 16. This value is an integer in my table. But as you see, the BYTE has a number of different values with the fifth=1 as the rest of the BITS can be 1 as in example 01011000 = 26.
Down to code.
Tablename = myMoney
Column Name = mCurrency; Data Type = decimal(18,2)
Column Name = bit5; Data Type = int
Code Block
SELECT sum(mCurrency)
FROM myMoney
WHERE somefunction(bit5) = 1
I have made the code in VB some time ago but this time I need to do the aclculation in the SQL server.
Hopefully Robin M
View 1 Replies
View Related
Feb 20, 2008
Hi I have a table where I am searching for the values between certain numbers. But then I want to exclude a record to be part of the search
so how I could do that.
This is what I am trying to do.
Select * from mytable Where Value Between 10000 and 200000 --this gives me the result
But then I want to exclude a record from the select statment.
For example this is what I really want to do
Select * from mytable Where Value Between 10000 and 20000 and where value != '100o'
It is not a typo '100o' and this not let me test the select statment so I want to exclude that particular record.
The type was in the table and I want to exclude that record so I can get my statment running.
View 5 Replies
View Related
Feb 6, 2007
Hi all,
I created a stored procedure which perfoms a simple select like this:
CREATE PROCEDURE Reports_Category
( @loc varchar(255), @pnum varchar(255) )
AS declare @vSQL nvarchar(1000) set @vSQL = 'SELECT ' + @loc + ', ' + @pnum + ' FROM Category ' exec sp_executesql @vSQL
RETURNGO
It takes field names as parameters. What happens is that when I supply value to only one parameter, the procedure gives error as it is expecting both values. Is there a way to make these parameters work like an 'OR' so that the procedure returns a dataset even if there is only one value supllied.
Please help,
Thanks,
bullpit
View 7 Replies
View Related
Aug 27, 2001
hI
DOES ANY BODY KNOW HOW TO EXECUTE STORE PROCEDURE IN SELECT STATMENT
THANKS
jk
View 1 Replies
View Related
Sep 13, 2007
Hi,
I have a little bit of a problem I cannot seem to figure it out. Is it possible to write a Select statement that contains a WHERE column_name > desired_numeric_value
The tricky part it that the column is of CHAR type and can contain numeric grades ranging from 0-100 or the letter I for Incomplete.
My SQL was working perfect when this column contained only numbers as soon as a record with I was added I get the following error:
Character to numeric conversion error
This report will be used to find students who have failing grades. Thanks for any help!
View 5 Replies
View Related
Oct 25, 2004
Newbie to the SQL Server.
UPDATE GOLDIE
SET GOLDIE_ID = (SELECT *,
SUBSTRING(GOLDIE_ID,1,
CASE WHEN PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)= 0
THEN 0
ELSE PATINDEX('%[A-Z,a-z]%',GOLDIE_ID)-1
end) STRIPPED_COL
FROM GOLDIE_ID)
Here is the explaination of the above query, I have a column which has the values like '23462Golden Gate' or '348New York'. Above query is stripping all the characters and keeping only numbers. So I need to update the same column with only numbers which is the output of abover query.
Immd help will be greatly appreciated.
Pam
View 8 Replies
View Related
Apr 29, 2007
ok, I am on Day 2 of being brain dead.I have a database with a table with 2 varchar(25) columns I have a btton click event that gets the value of the userName, and a text box.I NEED to insert a new row in a sql database, with the 2 variables.Ive used a sqldatasource object, and tried to midify the insert parameters, tried to set it at the button click event, and NOTHING is working. Anyone have a good source for sql 101/ASP.Net/Braindead where I can find this out, or better yet, give me an example. this is what I got <%@ Page Language="C#" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server"> protected void runit_Click(object sender, EventArgs e) { //SqlDataSource ID = "InsertExtraInfo".Insert(); //SqlDataSource1.Insert(); } protected void Button1_Click1(object sender, EventArgs e) { SqlDataSource newsql; newsql.InsertParameters.Add("@name", "Dan"); newsql.InsertParameters.Add("@color", "rose"); String t_c = "purple"; string tempname = Page.User.Identity.Name; Label1.Text = tempname; Label2.Text = t_c; newsql.Insert(); }</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"> <title>mini update</title></head><body> <form id="form1" runat="server"> name<asp:TextBox ID="name" runat="server" OnTextChanged="TextBox2_TextChanged"></asp:TextBox><br /> color <asp:TextBox ID="color" runat="server"></asp:TextBox><br /> <br /> <asp:Button ID="Button1" runat="server" OnClick="Button1_Click1" Text="Button" /> <br /> set lable =><asp:Label ID="Label1" runat="server" Text="Label" Width="135px" Visible="False"></asp:Label><br /> Lable 2 => <asp:Label ID="Label2" runat="server" Text="Label"></asp:Label><br /> Usernmae=><asp:LoginName ID="LoginName1" runat="server" /> <br /> <br /> <br /> <br /> <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConflictDetection="CompareAllValues" ConnectionString="<%$ ConnectionStrings:newstring %>" DeleteCommand="DELETE FROM [favcolor] WHERE [name] = @original_name AND [color] = @original_color" InsertCommand="INSERT INTO [favcolor] ([name], [color]) VALUES (@name, @color)" OldValuesParameterFormatString="original_{0}" SelectCommand="SELECT [name], [color] FROM [favcolor]" UpdateCommand="UPDATE [favcolor] SET [color] = @color WHERE [name] = @original_name AND [color] = @original_color"> <DeleteParameters> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </DeleteParameters> <UpdateParameters> <asp:Parameter Name="color" Type="String" /> <asp:Parameter Name="original_name" Type="String" /> <asp:Parameter Name="original_color" Type="String" /> </UpdateParameters> <InsertParameters> <asp:InsertParameter("@name", "Dan", Type="String" /> <asp:InsertParameter("@color", "rose") Type="String"/> </InsertParameters> </asp:SqlDataSource> <asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" DataKeyNames="name" DataSourceID="SqlDataSource1"> <Columns> <asp:CommandField ShowDeleteButton="True" ShowEditButton="True" ShowSelectButton="True" /> <asp:BoundField DataField="color" HeaderText="color" SortExpression="color" /> <asp:BoundField DataField="name" HeaderText="name" ReadOnly="True" SortExpression="name" /> </Columns> </asp:GridView> </form></body></html>
View 1 Replies
View Related
Sep 18, 2007
Hi guys/ladies I'm still having some trouble formatting a select statement correctly.
I am using a sqldatasource control on an aspx page. It is connecting via odbc string to an Informix database.
Here is my select statement cut down to the most basic elements.
SELECT commentFROM informix.ipr_stucomWHERE (comment > 70)
The column "comment" contains student grades ranging from 0-100 and the letters I, EE, P, F, etc. Therefore the column is of a char type. This is a problem because I cannot run the above statement without hitting an alpha record and getting the following error
"Character to numeric conversion error"
How can I write this statement where it will work in the datasource control and have it only look at numeric values and skip the alpha values?
I have tried case with cast and isnumeric... I don't think that I have the formating correct.
I have also used:
WHERE (NOT (comment = ' I' OR comment = ' EE' OR comment = ' NG' OR comment = ' WP' OR comment = ' WF' OR comment = ' P' OR comment = ' F'))
This works but is very clunky and could possibly break if other letters are input in the future. There has to be a better way.I am sorry for my ignorance and thanks again for your help.
View 2 Replies
View Related
Aug 27, 2006
sry im an idiotbut how do u take like "SELECTbla FROM blar WHERE blam=blfda" and make the thing it "selects" into a variable thnx
View 5 Replies
View Related
Jul 6, 2004
Hi, I have this query basically im trying to set the @db to the current database depending on the quarter. This will be used in a package to change the database that the package will use.
DECLARE @db nVarchar(4000)
SET @db = N'TESTDB' + RIGHT(DATEPART(yy, GETDATE()), 2) + '_' + CASE WHEN DATEPART(m, GETDATE()) IN ('11', '12', '1')
THEN 'Q1' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('2', '3', '4') THEN 'Q2' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('5', '6', '7')
THEN 'Q3' ELSE CASE WHEN DATEPART(m, GETDATE()) IN ('8', '9', '10')
THEN 'Q4' END END END END
EXECUTE sp_executesql @db, N'@level tinyint', @level = 35
I get the error Incorrect Syntax near 'TESTDB04_Q3'.
View 3 Replies
View Related
Jun 21, 2006
hi !
I am trying to set the variables value through Execute SQL task and create directory through the FileSystemTask by setting the user variable as source. The User variable has the Directory path to create the directory, but when i set the source as the user variable it complains that its value is empty.
Execute SQL TAsk happens to work correctly when i check the watch window the user variable is set correctly . but value is not setting in the SSIS/Variable window Value.
What is wrong an what method should i use to set the variables value to create directory.
Thanks,
aaks
View 5 Replies
View Related
