What Is Wrong With My SQL Having Equals (=) ?
Aug 8, 2006
I have a table called "Inc_Exp_Accounts". The table has ledger account numbers and names. It also has a third column called "IsExpense". The IsExpense column is a bit field = 0 (false) if the account is an income account and = -1 (true) if the account is an expense account. I only have income and expense accounts. I do not have other accounts such as asset accounts. The distinguishing thing about income accounts is that the account number (an nvarchar(11) type) is a '3'. Anything else makes it an expense account.
I would think I can use the following SQL statement to correctly set the IsExpense column:
UPDATE Inc_Exp_Accounts
SET IsExpense = IIF(LEFT(Account_Number, 1) = '3', False, True)
But nooo, I cannot! I cannot even run it because Visual Studio SQL Syntax checker rejects it. It complains about the second '='. If I change the second '=' to '>', then it complains about '>'. Therefore, I think I have the particular character about which it complains.
Ths specific complaint (error message) is: "Error in list of function arguments: '=' not recognized.
Unable to parse query text.".
So what is wrong?
View 3 Replies
ADVERTISEMENT
Jun 2, 2004
I get this error when I attempt to read from a datareader using the following sql statement:
Dim mysql As String = "SELECT PayrollTrans.PayrollID, Employees.[EmpFirstName] & ' ' & " _
& " Employees.[emplastname] AS FullName, Employees.[City] & ', ' & Employees.[State] & ' ' & Employees.[zip] AS CityState " _
& " , PayrollTrans.Date, PayrollTrans.EmployeeID, PayrollTrans.RegHours, " _
& " PayrollTrans.OTHours , PayrollTrans.RegPay, PayrollTrans.OTPay, " _
& " PayrollTrans.FedTax, PayrollTrans.FICATax, PayrollTrans.MedicareTax, " _
& " PayrollTrans.ESCTax, PayrollTrans.StateTax, PayrollTrans.ESCEMPTax, " _
& " PayrollTrans.FUTATax, PayrollTrans.NetPay, Employees.EmployeeID, " _
& " Employees.Address1, Employees.Address2, Employees.SSAN, " _
& " Employees.PayType, Employees.RegPayRate, Employees.OTPayRate, " _
& " Employees.MaritalStatus, Employees.FedExemption, Employees.StateExemption, " _
& " Employees.Active, Employees.SelectforPay, Employees.PayDate " _
& " FROM PayrollTrans, Employees where PayrollTrans.EmployeeID = Employees.EmployeeID;"
my reader command list as follows:
Dim objCM As New SqlClient.SqlCommand(mysql, SqlConnection1)
Dim objDR As SqlClient.SqlDataReader
objDR = objCM.ExecuteReader
Any ideas on where I am going wrong?
Thanks in advance
View 3 Replies
View Related
May 18, 2004
I am getting a error message saying: Invalid operator for data type. Operator equals boolean AND, type equals datetime.
I traced the pointer to @gdo and @gd, they are both dates!
INSERT INTO AdminAlerts values (CURRENT_USER, 'UPDATE', getDate(), @biui, 'Updated booking, ID of booking updated: ' & @biui & ', Booking date and time before/after update: ' & @gdo & '/' & @gd & ', Room number before/after update: ' & @rno & '/' & @rn & ' and Customer ID before/after update: ' & @cio & '/' & @ci)
If I cut that two dates out it works fine.
Could someone tell me the syntax to include a date in a string :confused:
View 3 Replies
View Related
Mar 7, 2006
hi,In MySql,we can run below query
select appid,dvbpath,business from ocgxmldbinfo group by appid
but in MSSql,it can't!so i want to know whether the following query equals it?
select distinct appid,dvbpath,business from ocgxmldbinfo order by appid
thanks!
View 6 Replies
View Related
May 27, 2008
why following happens? can anyone explain the logic?
if 'a ' = 'a'
select 'equal'
else
select 'not equal'
--------
result: equal
View 13 Replies
View Related
Jul 6, 2006
Is it possible to use wildcards with an equals statement? Such asSELECT * FROM Table WHERE City = '%' AND State='Ca'Bascially just stating where city equals anything...I know you can do it with a LIKE statement such as...SELECT * FROM Table WHERE City LIKE '%' AND State='Ca'but is that very efficient?The reason I want to do this is because I want to programmitcally set the city, so just ommiting it won't work
Also, using City LIKE '%' seems to not include NULL...is there anywayto include NULL as well as anything else?
Thanks for your help!
View 2 Replies
View Related
Jan 14, 2008
Hello,
I perform queries on tables with 500,000 plus records, when I use the like clause or the = for record names with large numbers I get different counts with each run. As an example if I was searching retail stores and k-mart was one I get counts like the following
1st run: k-mart 2200
2nd run: k-mart 2240
3rd run: k-mart 2197 etc. etc.
The records may be k-mart inc or kmart inc or k-mart etc.
If anyone has a better understanding of how the items are searched for and knows a better way please respond.
Thank you in advance.
View 9 Replies
View Related
May 30, 2006
Hi,
How do I round when number is ending with 5 for example:
When the number next to number 5 is an even number
24.5 = 24.0 round down
0.245 = .24 round down
1265 = 1260 round down
when the number next to number 5 is an odd number
23.5 = 24.0 round up
0.235 = .24 round up
1755 = 1760 round up
Thanks in advance!
View 5 Replies
View Related
Nov 13, 2014
I have a table with a column of type date. I want to select all records where the date column = today. I tried
Select * from myTable where DateSent = GetDate()
but it returns no records.
Do I really have to use this slow method?
select datesent from Messages where convert(varchar,DateSent,103) = CONVERT(varchar, GetDate(), 103)
View 2 Replies
View Related
Feb 11, 2008
This seems to be a rather old problem (http://www.themssforum.com/SQLServer/Does-empty/) but I couldn't find an answer yet.
The problem is: I have two tables t1 and t2 where t1 is a staging area of t2.
t1: (id int not null, phone varchar(30))
t2: (id int not null, phone varchar(30))
Data in t1: (1, '') <- empty string
Data in t2: (1, ' ') <- a blank
Comparing t1.phone with t2.phone results in equality which in my opinion isn't correct.
The question ist: How can I change the behaviour of SQL-Server to result in inequality so that the change in my staging table is detected correctly?
Thanks in advance
Fridtjof
View 4 Replies
View Related
Feb 19, 2006
Im trying to insert a record in my sql server 2005 express database.The following function tries that and without an error returns true.However, no data is inserted into the database...Im not sure whether my insert statement is correct: I saw other example with syntax: insert into table values(@value1,@value2)....so not sure about thatAlso, I havent defined the parameter type (eg varchar) but I reckoned that could not make the difference....Here's my code: Function CreateNewUser(ByVal UserName As String, ByVal Password As String, _ ByVal Email As String, ByVal Gender As Integer, _ ByVal FirstName As String, ByVal LastName As String, _ ByVal CellPhone As String, ByVal Street As String, _ ByVal StreetNumber As String, ByVal StreetAddon As String, _ ByVal Zipcode As String, ByVal City As String, _ ByVal Organization As String _ ) As Boolean 'returns true with success, false with failure Dim MyConnection As SqlConnection = GetConnection() Dim bResult As Boolean Dim MyCommand As New SqlCommand("INSERT INTO tblUsers(UserName,Password,Email,Gender,FirstName,LastName,CellPhone,Street,StreetNumber,StreetAddon,Zipcode,City,Organization) VALUES(@UserName,@Password,@Email,@Gender,@FirstName,@LastName,@CellPhone,@Street,@StreetNumber,@StreetAddon,@Zipcode,@City,@Organization)", MyConnection) MyCommand.Parameters.Add(New SqlParameter("@UserName", SqlDbType.NChar, UserName)) MyCommand.Parameters.Add(New SqlParameter("@Password", Password)) MyCommand.Parameters.Add(New SqlParameter("@Email", Email)) MyCommand.Parameters.Add(New SqlParameter("@Gender", Gender)) MyCommand.Parameters.Add(New SqlParameter("@FirstName", FirstName)) MyCommand.Parameters.Add(New SqlParameter("@LastName", LastName)) MyCommand.Parameters.Add(New SqlParameter("@CellPhone", CellPhone)) MyCommand.Parameters.Add(New SqlParameter("@Street", Street)) MyCommand.Parameters.Add(New SqlParameter("@StreetNumber", StreetNumber)) MyCommand.Parameters.Add(New SqlParameter("@StreetAddon", StreetAddon)) MyCommand.Parameters.Add(New SqlParameter("@Zipcode", Zipcode)) MyCommand.Parameters.Add(New SqlParameter("@City", City)) MyCommand.Parameters.Add(New SqlParameter("@Organization", Organization)) Try MyConnection.Open() MyCommand.ExecuteNonQuery() bResult = True Catch ex As Exception bResult = False Finally MyConnection.Close() End Try Return bResult End FunctionThanks!
View 1 Replies
View Related
Aug 2, 2007
Need help to create a query to count how many times over a specified time the tag(s) equal the value of 1.
Thanks
Gary
View 3 Replies
View Related
Jun 27, 2007
Hello,I currently have Table1 and View1.View1 is a query from 2 or 3 tables that works fine on its own.However in my current query if I try to use it...something like...SELECT a.col1, a.col2, a.col3, b.col1, b.col2, b.col3FROM View1 a JOIN Table1 b on a.col1 = b.col1WHERE a.col2 <b.col2 OR a.col3 <b.col3It throws an error "Server: Msg 446, Level 16, State 9, Line 1 Cannotresolve collation conflict for not equal to operation."Clearly I need to use collation between Table1 and View1, But I dontknow where I need to use "COLLATE SQL_Latin1_General_CP850_CI_AI" andhow? this is the collation set on Table1.Thank you!Yas
View 1 Replies
View Related
Sep 3, 2015
Using this IIF statement:
=CountDistinct(IIF(Fields!Released_DT.Value = Fields!Date2.Value, Fields!Name.Value,
Nothing))
Released_DT = a date - 09/03/2015 or 09/02/2015
Date2 = returns another date value in this case 09/03/2015
What I'm trying to do is: count distinct number of people (Fields!Name.Value) if the Relased_DT = Date2.My IIF statement is returning a zero value.
View 4 Replies
View Related
May 1, 2008
i can't seem to get this query to work, it just keep returning nulls with ever values i set . 1 SELECT Bedrooms, Description, Image,
2 (SELECT Location
3 FROM Location_Table
4 WHERE (Property_Table.LocationID = LocationID)) AS Location, LocationID, Price, Price AS PriceMax, PropertyID, Title, TypeID,
5 (SELECT TypeOfProperty
6 FROM Type_Table
7 WHERE (Property_Table.LocationID = TypeID)) AS TypeOfProperty
8 FROM Property_Table
9 WHERE (TypeID = @TypeID OR
10 TypeID IS NULL) AND (LocationID = @LocationID OR
11 LocationID IS NULL) AND (Price >= @MinPrice OR
12 Price IS NULL) AND (PriceMax <= @MaxPrice OR
13 PriceMax IS NULL)
View 7 Replies
View Related
May 14, 2004
This is working:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
"MONTH(Some_Date) >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
This is NOT:
SELECT...
"CAST(MONTH(Some_Date) as int) as Month, " &_
"CAST(DAY(Some_Date) as int) as Day " &_
"FROM Deceased " &_
"WHERE Active = 1 AND " &_
Month >= MONTH(GETDATE()) " &_
"ORDER BY Month, Day DESC"
it says - Invalid column name 'Month'
Why ? Why ? Why ?
View 3 Replies
View Related
Oct 5, 2004
I'm just learning SQL after using it for about a year now and I'm trying to add a Check constraint to a Social Security Field (See Below) and I can't figure out what is wrong with the syntax. In QA it errors out stating: Line 4: Incorrect syntax near '0-9'.
use Accounting
Alter Table Employees
Add Constraint CK_SNN
Check (SSN Like [0-9][0-9][0-9]-[0-9][0-9]-[0-9][0-9][0-9][0-9])
Any help would be nice. Thanks in advance.
View 2 Replies
View Related
Jan 3, 2005
Hello !! I have just createt a simple login page and reg page, login is working when I make one useraccound directly on to MsSql server, I can login successfully, but the problem is REGISTER PAGE with INSERT code. Here down is the code ov the login.aspx page
Function AddUser(ByVal userID As Integer, ByVal userName As String, ByVal userPassword As String, ByVal name As String, ByVal email As String) As Integer
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='music'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)
Dim queryString As String = "INSERT INTO [users] ([UserID], [UserName], [UserPassword], [Name], [Email]) VALUE"& _
"S (@UserID, @UserName, @UserPassword, @Name, @Email)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection
Dim dbParam_userID As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userID.ParameterName = "@UserID"
dbParam_userID.Value = userID
dbParam_userID.DbType = System.Data.DbType.Int32
dbCommand.Parameters.Add(dbParam_userID)
Dim dbParam_userName As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userName.ParameterName = "@UserName"
dbParam_userName.Value = userName
dbParam_userName.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userName)
Dim dbParam_userPassword As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_userPassword.ParameterName = "@UserPassword"
dbParam_userPassword.Value = userPassword
dbParam_userPassword.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_userPassword)
Dim dbParam_name As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_name.ParameterName = "@Name"
dbParam_name.Value = name
dbParam_name.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_name)
Dim dbParam_email As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_email.ParameterName = "@Email"
dbParam_email.Value = email
dbParam_email.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_email)
Dim rowsAffected As Integer = 0
dbConnection.Open
Try
rowsAffected = dbCommand.ExecuteNonQuery
Finally
dbConnection.Close
End Try
Return rowsAffected
End Function
Sub LoginBtn_Click(sender As Object, e As EventArgs)
If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Message.Text = "Register Successed, click on the link WebCam for login"
Else
Message.Text = "Failure"
End If
End Sub
and here is the error I receive when I try to open this register.aspx page:
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.
Compiler Error Message: BC30455: Argument not specified for parameter 'email' of 'Public Function AddUser(userID As Integer, userName As String, userPassword As String, name As String, email As String) As Integer'.
Source Error:
Line 52: Sub LoginBtn_Click(sender As Object, e As EventArgs)
Line 53:
Line 54: If AddUser(txtUserName.Text, txtUserPassword.Text, txtName.Text, txtEmail.Text) > 0
Line 55: Message.Text = "Register Successed, click on the link WebCam for login"
Line 56:
Source File: c:inetpubwwwrootweb_sitemusic
egister.aspx Line: 54
In the DB the fields are added as :
UserID as int
UserName as varchar
UserPassword as varchar
Name as varchar
Email as varchar
and I have TRYED to change from "varchar" on to "text" but I receive same error message.
PLEASE HELP !!!!!!!! this is not first time I get the same errors on the all reg pages :( WHY ? WHAT LINE I HAVE TO EDIT ?
Thank You !!!
Regards
View 2 Replies
View Related
Mar 18, 2005
Hi please lok at this SP I have written and point where am I commiting mistake.
The PROD_ID_NUM field is a varchar field in the actual database.
Any help appreciated.
CREATE PROCEDURE [cp_nafta_dws].[spMXGetProductDetails]
(
@ProductCode Int = null
)
AS
DECLARE @sqlString AS nvarchar(2000)
SET @sqlString = 'SELECT Master.PROD_ID_NUM AS ProductCode,
Master.PROD_DESC_TEXT AS ProductName,
Detail.PiecesPerBox, Detail.Price
FROM cp_nafta_dws.PRODUCT AS Master
INNER JOIN cp_nafta_dws.PRODUCT_MEXICO AS Detail
ON Master.PROD_ID_NUM = Detail.PROD_ID_NUM'
BEGIN
IF NOT (@ProductCode = NULL)
BEGIN
SET @sqlString = @sqlString + ' WHERE Master.PROD_ID_NUM = ' + @ProductCode
END
END
EXEC @sqlString
GO
Thanks
View 11 Replies
View Related
Jul 7, 2005
Hello,
SELECT dbo.tSp.pID, dbo.tLo.oS
FROM dbo.tSp INNER JOIN
dbo.tLo ON dbo.tSp.SpID = dbo.tLo.SpID
WHERE (dbo.tLo.oS = N'[MyText]')
This works without Where and I see MyText available in oS column. Why does it not bring anything when Where is there?
Thanks,
View 3 Replies
View Related
Oct 3, 2005
This code is not updating the database, please help me
SqlCommand UpdCmd; SqlCommand SelCmd; SqlDataAdapter da; DataSet ds = new DataSet();
da = new SqlDataAdapter(); if (!(Conn.State == ConnectionState.Open)) { Conn.Open(); }
SelCmd = null; SelCmd = new SqlCommand("sp_SelectUserInfo",Conn); SelCmd.CommandType = CommandType.StoredProcedure;
oSelCmd.Parameters.Add("@UserID",userid);
da.SelectCommand = oSelCmd;
da.Fill(ds,"UserTab"); oUpdCmd = null; UpdCmd = new SqlCommand("sp_UpdateUserInfo",Conn); UpdCmd.CommandType = CommandType.StoredProcedure;
UpdCmd.Parameters.Add("@UserID",userid); UpdCmd.Parameters.Add("@FirstName",firstName); UpdCmd.Parameters.Add("@LastName",lastName); UpdCmd.Parameters.Add("@Region",region); da.UpdateCommand = UpdCmd; da.Update(ds,"UserTab");
Conn.Close();
View 1 Replies
View Related
Mar 16, 2006
Here is the codeLine 84: Line 85: searchDataAdapter = New System.data.sqlclient.sqldataadapter("SELECT * FROM Inventory Where title=" & title, searchConnection)Line 86: searchDataAdapter.Fill(objItemInfo, "ItemInfo")Line 87: Line 88: Return objItemInfohere is the errorLine 1: Incorrect syntax near '='. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.Source Error:
View 3 Replies
View Related
Mar 29, 2006
I have a SQL query in my asp.net & c# application. im trying to retrieve the data from two tables where the ID's of the tables match. once this is found i am obtaining the value associated with one of the keys. e.g. my two tables
Event EventCategoryTypeField Type Example Field Type ExampleEventID Int(4) 1 CategoryID(PK)int(4) 1CategoryID(FK)int(4) 1 Type varchar(50) ExerciseType varchar (200) Exercise Color varchar(50) Brown
The CategoryID is a 1:n relationship. my SQL query will retrive the values where the CategoryIDs match and the Tyoe matches as well. once this is found it will apply the associated color with that categoryID (each unique category has its own Color).
my application will read all the data correctly (ive checked it with a breakpoint too and it reads all the different colors for the different ID's) but it wont display the text in the right color from the table. it will just display everything in the first color it comes across.
Im including my code if it helps. can anyone tell me where i am going wrong please?? (the procedures are called on the On_Page_Load method)
private void Load_Events(){///<summary>///Loads the events added from the NewEvent from into a dataset///and then just as with the Holidyas, the events are wriiten to///the appropriate calendar cell by comparing the date. only the ///title and time will be displayed in the cell. other event details///such as, Objective, owner will be shown in a dialog box syle when ///the user hovers over the event.///</summary>
mycn = new SqlConnection(strConn);myda = new SqlDataAdapter("SELECT * FROM Event, EventTypeCategory WHERE Event.CategoryID = EventTypeCategory.CategoryID AND Event.Type = EventTypeCategory.CategoryType", mycn);myda.Fill(ds2, "Events");}
private void Populate_Events(object sender, System.Web.UI.WebControls.DayRenderEventArgs e){///<summary>///This procedure will read all the data from the dataset - Events and then///write each event to the appropriate calendar cell by comparing the date of ///the EventStartDate. if an event is found, the title and time are written///to the cell, other details are shown by hovering over the cell to bring///up another function that will display the data in a dialogBox. once the///event is written, the appropriate color is applied to the text.///</summary>if (!e.Day.IsOtherMonth){foreach (DataRow dr in ds2.Tables[0].Rows){if ((dr["EventStartDate"].ToString() != DBNull.Value.ToString())){DateTime evStDate = (DateTime)dr["EventStartDate"];string evTitle = (string)dr["Title"];string evStTime = (string)dr["EventStartTime"];string evEnTime = (string)dr["EventEndTime"];string evColor = (string)dr["CategoryColor"];
if(evStDate.Equals(e.Day.Date)){e.Cell.Controls.Add(new LiteralControl("<br>"));e.Cell.Controls.Add(new LiteralControl("<FONT COLOR = evColor>"));e.Cell.Controls.Add(new LiteralControl(evTitle + " " + evStTime + " - " + evEnTime));}}}}else{e.Cell.Text = "";}}
View 2 Replies
View Related
Mar 29, 2006
Hi,
Just a quickie... Can anyone see anything wrong with this SQL. I'm using Microsoft SQL Server 2000.
Code:
SELECT * FROM PFP_UserProfiles, Users, PFP_UserSkills, PFP_UserIndustries WHERE PFP_UserSkills.SkillID IN ( '222', '221', '182') AND PFP_UserProfiles.IsPublic = 1;
Cheers
Chris
View 2 Replies
View Related
Mar 11, 2008
Hi everyone.
I'm working on an assignment and I am about to give up. I can't figure out what I'm doing wrong. It seems like I have everything worked out, but when I run the SQL query i've come up with, I get errors regarding INVALID tables.
As far as I can tell, all my tables are valid. Can anyone give me any pointers on what i'm doing wrong **read: I'm not asking for my assignment to be done for me, just asking for help because I am so close to getting the right answer....i think**
Here is what was provided:
http://i47.photobucket.com/albums/f152/hmarandi/problem.gif
Here is the Query I have come up with which gives me errors!
Code:
-- START --
CREATE TABLE dept
(deptnameCHAR(15),
empid CHAR(8),
CONSTRAINT PKdeptname PRIMARY KEY (deptname));
ALTER TABLE dept
ADD CONSTRAINT FKempid FOREIGN KEY (empid) REFERENCES Employee(empid);
-- START --
CREATE TABLE Employee
( empid CHAR(8),
deptname CHAR(15),
empfname VARCHAR(10) NOT NULL,
emplname VARCHAR(10) NOT NULL,
empphone CHAR(15),
empemail VARCHAR(20) NOT NULL,
bossid CHAR(8),
empsalary DECIMAL(9,2) CONSTRAINT CHKEmpsalay Check (empsalary > 5),
CONSTRAINT PKempid PRIMARY KEY (empid),
CONSTRAINT uniqueEmail UNIQUE(empemail) );
-- Add constraint
ALTER TABLE Employee
ADD CONSTRAINT FKdeptname FOREIGN KEY (deptname) REFERENCES dept(deptname);
-- START --
CREATE TABLE POrder
(OrderID CHAR(8),
OrderDatedatetime,
CustIDCHAR(8),
EmpIDCHAR(8)
CONSTRAINT PKOrderIDPRIMARY KEY (OrderID));
-- Add constraint
ALTER TABLE POrder
ADD CONSTRAINT FKCustID FOREIGN KEY (CustID) REFERENCES Customer (CustID);
ALTER TABLE POrder
ADD CONSTRAINT FKEmpID FOREIGN KEY (EmpID) REFERENCES Employee (EmpID);
-- START --
CREATE TABLE Customer
(CustID CHAR(8),
CustNameVARCHAR(15),
BalanceCHAR(8)
CONSTRAINT PKCustIDPRIMARY KEY (CustID));
-- START --
CREATE TABLE OrderItem
(OrderIDCHAR(8),
ProdIDCHAR(8),
QtyCHAR(8)
CONSTRAINT PKOrderIDPRIMARY KEY (OrderID));
ALTER TABLE OrderItem
ADD CONSTRAINT PKProdIDPRIMARY KEY (ProdID)
ALTER TABLE OrderItem
ADD CONSTRAINT FKOrderID FOREIGN KEY (OrderID) REFERENCES POrder (OrderID);
ALTER TABLE OrderItem
ADD CONSTRAINT FKProdID FOREIGN KEY (ProdID) REFERENCES Product(ProdID);
-- START --
CREATE TABLE Product
(ProdIDCHAR(8),
ProdNameVARCHAR(15),
MakerVARCHAR(15),
StockSizeCHAR (6),
PriceCHAR (10)
CONSTRAINT PKProdIDPRIMARY KEY (ProdID));
Any HELP would be greatly appreciated.
Thanks!
View 14 Replies
View Related
Sep 10, 2004
it gives an error saying
"incorrect syntax near update" . can anyone tell me why?
EXEC("update "+@sTable+"
set status='A'
where breakdate < datediff(day,'08/12/1960',getdate())
and clientid=12059
and status ='F'")
View 2 Replies
View Related
Jul 19, 2004
I am trying to created a view and have a need for conditional logic:
Here is what I presently have (not working):
----------------------------------------------------------------------------
IF (ISDATE(COMPLETIONDATE) = 1)
BEGIN
CASE
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, COMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
ELSE
IF (ISDATE(COMPLETIONDATE) = 0)
BEGIN
CASE
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') THEN 'GREEN'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < (SELECT GREEN FROM RESET.THRESHOLDS WHERE TYPE = 'SCHEDULE') AND DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) > 0 THEN 'YELLOW'
WHEN DATEDIFF(d, TARGETCOMPLETIONDATE, RESET.RESET_UNIT.MCD) < 0 THEN 'RED'
END AS THRESHOLDSTATUS
END
--------------------------------------------------------------------------
Can someone tell me what I am doing wrong?
Basically I am trying to test to see if "completiondate" is a date and if it is then perform a case operation using it, if it is not a date then I want to perform the case operation using "targetcompletiondate".
Thanks...
View 4 Replies
View Related
May 4, 2008
select * from dbo.Advertisement_Search '%'
Advertisement_Search is a store procedure and i am trying this in ms sql.
Thanks in advance.
View 8 Replies
View Related
Jun 5, 2008
Hello all.
Ive written the following code to check if a foreign key exists, and if it doesnt to add it to a table. The code i have is:
IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_tblProductStockNote_tblLookup]') AND parent_object_id = OBJECT_ID(N'[dbo].[tblProductStock]'))
BEGIN
PRINT N'Adding foreign keys to [dbo].[tblProductStock]'
ALTER TABLE [dbo].[tblProductStock] ADD
CONSTRAINT [FK_tblProductStockNote_tblLookup] FOREIGN KEY ([ProductStockNoteType]) REFERENCES [dbo].[tblLookup] ([ID])
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
END
However i keep getting the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '?'.
Thanks for reading.
View 14 Replies
View Related
Jan 14, 2006
INSERT INTO IS_REGISTERED VALUES
(54907,2715,'I-2001')
Server: Msg 2627, Level 14, State 1, Line 1
Violation of PRIMARY KEY constraint 'PK__IS_REGISTERED__629A9179'. Cannot insert duplicate key in object 'IS_REGISTERED'.
The statement has been terminated.
How do I fix this? I am trying to insert this into the IS_REGISTERED Table.
Student_ID Section_ID Semester
38214 2714 I-2001
54907 2714 I-2001
54907 2715 I-2001
66324 2713 I-2001
IS_REGISTERED (Student_ID, Section_ID, Semester)
albanie
View 7 Replies
View Related
Nov 5, 2007
Hi everybody,
I posted this issue a couple of days ago, but got no solution yet. Anyhow, here is the thing:
"A limited user account is able to see items on the report manager with no permission?"
I have created a local user account on the domain machine where the reports are deployed. This user is not a member of administrator group. It is just a user under the User Group. This user doesn't even have a permission on the Report Manager, not even a browser role. However, this user is able to see the contents of the report manager and also can make changes role assignment under the properties tab in the report manager.
Accourding to my observation so far, any user account created on this domain machine is acting like an admin on the Report Manager, with out being given a permission on the Report Manager. I know something is wrong
Please somebody advise on this
View 5 Replies
View Related
Feb 29, 2008
here i am trying to get the count of both match ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) + (AECPRDA_1.product_id = z.vendorcode)... probably AND won't do the trick.
i believe my below query (3) is wrong. what changes should i make to get both the match and continue further.
(3) should be atleast greater than (1) or (2)
1. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on AECPRDA_1.product_id = z.vendorcode
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (only the first match)
2. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11) and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 2183 (only the second match)
3. select count (*) FROM ((select * from aecprda where
AECPRDA.sales_cat_cd in ('02','10') and
(create_dt > '2008-02-17 18:01:38.000' or price_chg_dt > '2008-02-17 18:01:38.000')
) AS AECPRDA_1
left join (zfmt z inner join muzealbums on z.muzenbr=muzealbums.muzenbr) on ((substring(aecprda_1.upc_1,1,11) =
substring(z.upc,1,11)) and (AECPRDA_1.product_id = z.vendorcode) )
and z.Vendorname = N'Alliance'
LEFT OUTER JOIN AECMCAT aecmcat_c3
ON AECPRDA_1.mcat_cd3 = aecmcat_c3.Mcat_cd) --- count is 1811 (1st & 2nd match.. i expect the count to be higher than 2)
what i am doing wrong here...
View 6 Replies
View Related
Jun 2, 2006
[Column 9] == "00000000" ? NULL(DT_WSTR,10) : RIGHT([Column 9],2) + "/" + SUBSTRING([Column 9],5,2) + "/" + SUBSTRING([Column 9],1,4)
Where destination sql table own as datatype smalldatetime and accept NULLS.
Where Column 9 is defined on the pipeline as [DT_WSTR] with lenght of 10.
Otherwise Column 0 is exactly the same than Column 9 and works fine with this expression:
RIGHT([Column 0],2) + "/" + SUBSTRING([Column 0],5,2) + "/" + SUBSTRING([Column 0],1,4)
[Flat File Source [1]] Error: Data conversion failed. The data conversion for column "Column 9" returned status value 4 and status text "Text was truncated or one or more characters had no match in the target code page.".
View 1 Replies
View Related
