comparing a value, my sql statement fails at the last bit i.e. )<>0I'm trying to compare the last three characters i.e. not equal to 0? error message
Msg 102, Level 15, State 1, Procedure StoredProcedure, Line 137
Incorrect syntax near '<'.
Basically I'm trying to validate the field name Code to have a letter followed by 3 numbers..
if LEFT (@Code, 1) NOT LIKE '[a-Z]%' OR ISNUMERIC (RIGHT(@Code,3)<> 0
Im doing a report on total sales, however my statement below will return values that are equal to both fields ONLY.For example I want to do a query using two text boxes 'from' and 'to 'and count the total sales between the product dates 'Veh_Tyres_Date' and Veh_Parts_Date and 'Veh_Tyres Price' and Veh_ Parts Price'. however it works but if for example I do a search for 01/05/2015 from 31/05/2015 it will not return anything if the second field doesnt contain a sales date between that period.
SELECT tblVehicles.Veh_Parts, tblVehicles.Veh_Parts_Date, tblVehicles.Veh_Tyres, tblVehicles.Veh_Tyres_Date
FROM tblVehicles
WHERE (((tblVehicles.Veh_Parts_Date) Between [Enter From Date] And [Enter To])
AND ((tblVehicles.Veh_Tyres_Date) Between [Enter From Date] And [Enter To]));
I have two tables and a CTE table.
-Shop : list of all shops with an ID_SHOP
-Contract : list of all contract with a FK to the shop FK_SHOP. A shop can have multiple contracts
I have a cte where I calculate for each FK_SHOP the maximum contract start date. I now want to join my shop table with the contract table but only for the contracts >= their maximum contract start date.
WITH cteContracts AS
(
SELECT
FK_SHOP
,ID_CONTRACT
,MAX(cont.DTT_START_DATE) DT_STARTDATE
[Code] ....
Dear Friends,
In my report, I am having Listbox for users to choose Country, City & Company.
The user can choose Country. Based on the country selection, cities will be listed out.
Based on the city selection, Companies will be listed out.
They can choose companies.
Now, I have to show two set of results.
A. List of Companies as per selection ( dataset with equal to selection )
B. List of Companies which are not selected ( ie dataset with not equal to selection )
I have created a dataset with all companies and filter it by selection. When I tried with the filter option in the Dataset, I am able to check for only one value and not for multiple value. If the selection is one company, then I can filter it. But if they choose 5 companies, I am not not able to filter it. Is there any other option I can try out.
Please advice. Thanks.
warm regards
Rakin
Singapore.
Trying to do a simple query in SQL Server 7.0 to return data where not equal and get a cartesian.
Select person.person_id
from person, psnl
where person.person_id <> prsnl.person_id
Could this be an error with a SQL server setting?
Got this query and I need the following result;
declare @NumberToCompareTo int
set @NumberToCompareTo = 8
declare @table table
(
number int
)
insert into @table
select 4
[Code] ....
The query selects 4 and 5 of course. Now what I'm looking for is to retrieve the number less or equal to @NumberToCompareTo, I mean the most immediate less number than the parameter. So in this case 5
I want to set a column to 0 if it is set to a certain number. There are several columns to check though, so I am wondering if I can do it all in one query, or if I have to do it in single queries?
here is an example:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 67 | 23 | 67
so I want to set any contact_id_N = 0 where contact_id_N is = 67
so in the end, the table will look like this:
account | contact_id_1 | contact_id_2 | contact_id_3
433 | 0 | 23 | 0
is there a way to do it in one statement?
I have a query that is behaving a little a weird. here is the example:
i have 1 table in this table i have 2 columns wich are date and time
DATE_DEBUT_PERIODE_FISCALE DATE_FIN_PERIODE_FISCALE
------------------------------ ---------------------------
1/27/1997 2/27/1997
1/1/2005 2/6/2005
here is my query:
BEGIN
declare @datefin_flag datetime, @strip datetime
SELECT @strip = dateadd(d,datediff(d,0,getdate()),0)
SELECT @datefin_flag = DATE_FIN_PERIODE_FISCALE FROM DM_LKP_CALENDRIER_PERIODE_F
WHERE DATE_DEBUT_PERIODE_FISCALE < @strip AND DATE_FIN_PERIODE_FISCALE = @strip
--select @datefin_flag
--select @strip
IF(@datefin_flag != @strip)
RAISERROR('You cant run this',16,1)
END
Well this Query should return the raiserror it returns completes successfuly
since todays date is not the same as the date in the database.
if you select @datefin_flag it returns NULL and if you select @strip it brings back todays date how can NULL be equal to to todays date assuming that todays date is equal to NULL. ?
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB 123
colA XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
I am trying to optimize a stored procedure in SQL 2008. When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? I can't just change the query script because the table in question isn't there.
Can someone tell me why these do not equal? The rec_day is 10 ?
WHERE (CAST(DATEPART(Q, GETDATE()) * 3 - 2 AS VARCHAR(2)) + '/' + CAST(rec_day AS VARCHAR(2)) + '/' + CAST(YEAR(GETDATE()) AS VARCHAR(4))) = GETDATE()
on sql server
!= and <> are the same?
cause i cant see != on books online.
hi
does http://www.wrox.com/WileyCDA/WroxTitle/productCd-0764584332.html = http://www.wrox.com/WileyCDA/WroxTitle/productCd-076457955X.html ??
and what is the difference ??
and which one of these you advice me to read for beginner ??
thank you
Assume ID is an Integer field, which is faster (or is there any difference at all)?
Code Snippet
select * from <MyTable> where ID < 51
--or
select * from <MyTable> where ID <=50
SELECT website
FROM dbo.wce_contact
WHERE (NOT (Mail1Date IS NULL))
AND (Mail2Date IS NULL)
AND (Mail3Date IS NULL)
AND (Mail4Date IS NULL)
AND (Mail5Date IS NULL)
AND (Mail6Date IS NULL)
AND (Mail7Date IS NULL)
AND (Mail8Date IS NULL)
AND (Mail9Date IS NULL)
AND (Mail10Date IS NULL)
AND (Mail11Date IS NULL)
AND (Mail12Date IS NULL)
AND (Mail13Date IS NULL)
AND (Mail14Date IS NULL)
AND (Mail15Date IS NULL)
AND (Mail16Date IS NULL)
AND (IDStatus IS NULL OR IDStatus = '')
AND (NOT (Task LIKE '%x%') OR Task IS NULL)
AND (NOT (ExpressEmail IS NULL OR ExpressEmail = ''))
AND (NOT (WebSite IS NULL OR WebSite = ''))
AND (Mail17Date IS NULL OR Mail17Date = '')
AND (Mail18Date IS NULL)
AND (Mail1Date <= '20080421')
AND (NOT (RECORDOWNER = 'lbm') OR RECORDOWNER IS NULL)
How would i get the part in red to say mail1date less than or equal to 42 days back from todays date?
Hi,
I need to select all database names that are not contained in table tsm.dbo.DB_NOT_to_Backup, but are present in master..sysdatabases.
Can this be done using not equal joins?
I tried the query below, but it got me nowehere:
select a.name
from master..sysdatabases a inner join tsm.dbo.DB_NOT_to_Backup c on a.name<>c.DBname
order by a.name
G
I have table 'A' containing Company information including the company zip code. I have table 'B' which is a table of ZipCodes. How can I get a result set of all the Zip Codes that exist in 'A' but not in 'B'?
Thank you
Hi,
I have a stored procedure that returns a string. How do I assign it to a variable?
I have currently
exec myStoredProc '2'
and I want something like
declare @myVar varchar(25)
set myVar = exec myStoredProc '2'
But I can' seem to figure out the syntax for assignment.
Thanks
I'm having a problem with what should be a simple TSQL statement. I have a
table which has a datetime field updated. After the update if I type
select * from patient_medication where rec_status_date = '11/10/2000'
it returns the rows I want.(All the dates have a time of 00:00:00.000.
But if I type
select * from patient_medication where rec_status_date <> '11/10/2000'
or select * from patient_medication where rec_status_date != '11/10/2000'
The rows that have a value are returned, but none of the null values
are returned. Will nulls not work with this comparison?
Thanks
Sorry if this is a total n00bie question but...
I have table A and table B
I want a field in table B to be equl to the primary key in table A, and i'm not sure how to do that.
Thanks
Hi there,
I am trying to update one table on server A/database A with new records from a server B/database B.
Both databases use the same collation. The key-fields of both table do use the same collation, but not the server.
Help: I was thinking, when both databases and the fields inside the tables do both use the same collation, then there will not upcome any conflict ?
Do I have to adapt the database collation, too ? This would'nt be nice as I am retreiving data from different servers with different server collations...
Anybody else got the same problem ?
Thx in advance
dajm
The SUBSTRING function returns @domain.com. I don't want any results to be displayed from my sql statement if it equals @myemail.com. I have other cases too that are not relevant to my question. I'm getting an error that says invalid syntax near <
SELECT DISTINCT CASE CUST_EMLAD_TX
WHEN SUBSTRING(CUST_EMLAD_TX, CHARINDEX('@',CUST_EMLAD_TX), LEN(CUST_EMLAD_TX)) <> '@myemail.com' THEN CUST_EMLAD_TX
END
FROM ...
Good day!
I have the qry, which is suppose to assigned records to active user (almost 15 users) equal, but it doesn’t- sometime it assigned more to some users and less to others. How could I modify my qry to make sure it assigns the records equals to each user? Please, see the qry below. I will appreciate any help
Thk
UPDATE
TBLFRAUDFINDER
SET
DATE_ASSIGNED=GETDATE(),
FRAUDANALYSTASSIGNED=@FRAUDANALYST
WHERE
FRAUDID=@FRAUDID OR
(FRAUDANALYSTASSIGNED IS NULL AND
((INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER) OR
(CUST_NM=@CUST_NM) OR
(ALERT_IDENTIFIER=@ALERT_IDENTIFIER) OR
(ACCT IN (SELECT
ACCT
FROM
TBLFRAUDFINDER
WHERE
INQUIRY_GOVT_NUMBER=@INQUIRY_GOVT_NUMBER OR
CUST_NM=@CUST_NM OR
ALERT_IDENTIFIER=@ALERT_IDENTIFIER))
I have two procedures (old and new) that return queries and have to check that for every possible parameter they return the same data. Any hints?
Thanks, Pablo
Here is my stored proc: InsertAccount
Create PROCEDURE dbo.InsertAccount
@AccRisAsse bit,
@AccHldBll bit,
@Acctaxexm bit
AS
insert into TblAccount(AccRisAsse,AccHldBll,Acctaxexm)
values(@AccRisAsse,@AccHldBll,@Acctaxexm)
return scope_identity()
GO
And here is the code in .Net to run the proc and get back the scope_identity:
I delcare my sqlCommand sqlcmdInsNeAcc and connection from
system.data.Sqlclient, I open my connection. all goes well then I do:
sqlcmdInsNeAcc.type=commandtype.storedprocedure
sqlcmdInsNeAcc.CommandText = "InsertAccount"
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccountNumber",
SqlDbType.BigInt))
pm.Direction = ParameterDirection.ReturnValue
Dim kAccountNumber as integer
kAccountNumber = sqlcmdInsNeAcc.Parameters("@AccountNumber").Value
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccRisAsse",
SqlDbType.Bit))
pm.Value = myvalue1
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@AccHldBll",
SqlDbType.Bit))
pm.Value = myvalue2
pm =
sqlcmdInsNeAcc.Parameters.Add(New SqlParameter("@Acctaxexm",
SqlDbType.Bit))
pm.Value = myvalue3
sqlcmdInsNeAcc.executenonquery
then when I try to display the value of my returned scope identity from the variable: kAccountNumber
ALL I GET IS ZERO although my row is created in the table with the right Account number
Thanks
I
have a if statement and a table with 2 atributes. They are description
and date. I will check if there is in the table some date like
01-01-2008.String test "01-01-2008";if (test.Equals(here I will a SELECT query like SELECT date FROM appointmentTable)){
textbox1.text = "The description on this date is: " + Here I will the descripton of the selected date
Hi,
I'm using SQL SERVER 7.0.
I'm driving myself crazy on this one. I have 2 tables that look like this:
T1T2
C1C2C3C1C2C3
JOE1OTTAWAJOE1TORONTO
MARC1OTTAWAMARC4OTTAWA
GAVINNULLHALIFAXGAVIN3HALIFAX
DARRINNULLHALIFAXDARRIN3HALIFAX
DENISENULLPITTSBURGHDENISENULLPITTSBURGH
LOUISENULLRUSSELLLOUISE2RUSSELL
ANDREA3STITTSVILLEANDREANULLSTITTSVILLE
MARIO66PITTSBURGHGEORGE6KINCARDINE
LARRY6KINCARDINE
MARIO66PITTSBURGH
What I need to do is get all of the records from T2 that don't match EXACTLY to a record in T1. So I figured a LEFT OUTER JOIN should work:
SELECTT2.*
FROMT2 LEFT OUTER JOIN T1
ONT2.C1 = T1.C1
ANDT2.C2 = T1.C2
ANDT2.C3 = T1.C3
WHERET1.C1 IS NULL
But this statement returns the DENISE record when I do this (which has an EXACT match).
So, my thoughts took me to the NULL values in T1.C2 and T2.C2 for this record and I thought that, perhaps, the NULL values aren't being recognized as being equal (as they are UNKNOWN).
So I started digging around and found SET ANSI_NULLS OFF. I tried it but with no luck. Can you offer any insight on this one? What can I do to have NULL values recognized as being equal?
This is the result set that I would like to have returned in this example:
JOE1TORONTO
MARC4OTTAWA
GAVIN3HALIFAX
DARRIN3HALIFAX
LOUISE2RUSSELL
ANDREANULLSTITTSVILLE
GEORGE6KINCARDINE
LARRY6KINCARDINE
I've included a script to build and populate the tables below.
Any help on this will be greatly appreciated.
Thanks in advance,
Darrin
------------------------------------------------
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T1'
)
DROP TABLE T1
GO
IF EXISTS(
SELECT*
FROMSYSOBJECTS
WHERENAME = 'T2'
)
DROP TABLE T2
GO
CREATE TABLE [dbo].[T1] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[T2] (
[C1] [varchar] (50) NULL ,
[C2] [varchar] (50) NULL ,
[C3] [varchar] (50) NULL
) ON [PRIMARY]
GO
INSERT T1 VALUES('JOE', '1', 'OTTAWA')
INSERT T1 VALUES('MARC', '1', 'OTTAWA')
INSERT T1 VALUES('GAVIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DARRIN', NULL, 'HALIFAX')
INSERT T1 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T1 VALUES('LOUISE', NULL, 'RUSSELL')
INSERT T1 VALUES('ANDREA', '3', 'STITTSVILLE')
INSERT T1 VALUES('MARIO', '66', 'PITTSBURGH')
GO
INSERT T2 VALUES('JOE', '1', 'TORONTO')
INSERT T2 VALUES('MARC', '4', 'OTTAWA')
INSERT T2 VALUES('GAVIN', '3', 'HALIFAX')
INSERT T2 VALUES('DARRIN', '3', 'HALIFAX')
INSERT T2 VALUES('DENISE', NULL, 'PITTSBURGH')
INSERT T2 VALUES('LOUISE', '2', 'RUSSELL')
INSERT T2 VALUES('ANDREA', NULL, 'STITTSVILLE')
INSERT T2 VALUES('GEORGE', NULL, 'KINCARDINE')
INSERT T2 VALUES('LARRY', NULL, 'KINCARDINE')
INSERT T2 VALUES('MARIO', '66', 'PITTSBURGH')
GO
I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.
p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975
left side coming from p.item = right side coming from c.item.
Here are my statements.
--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num
[Code] ....
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!
I have created and deployed a report model by using BI development Studio. When trying to build a tabular report with Report Buider I have to use the "not equal" condition as a filter. The query does not give the expected results.
let's suppose the values for a filed I want to filter on are: 1,2,3,4,5
If i run the filter as: field NotEqual to 6 then the results are not correct
But if instead of using the NotEqual condition I use
field equals to 1
or
field equals to 2
or
field equals to 3
or
field equals to 4
or
field equals to 5
Then it gives the correct results. Ok I now it sounds weird but anyone with similar experience using ther NotEqual condition in a filter?
Thanks,
Alberto
Hi group,
This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.
Let's assume the following values:
Table1:
hello
Hello
Table2:
hello
This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda
It returns:
hello
Hello
????????????????????????????????????????????????????????
What the $#@#$
Why doesn't it return 1 value:
hello
Any ideas?
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)
What if I wanted to return all EXACT matches? Then what? Change collation?