T-SQL (SS2K8) :: How To Ignore Between Operator End Value If It Is 0 In Where Clause

Apr 9, 2015

create table deltavalues (planid varchar(10), baseval float, highval float)

Insert into deltavalues values ('1002', 2000,5000)
Insert into deltavalues values ('1003', 1000,0)
Insert into deltavalues values ('1004', 500,0)
Insert into deltavalues values ('1005', 1000,2000)

--I have below query , basically getting emnployee table balance between deltavalues table baseval column and highval column

select * from employee e join deltavalues d on e.planid = d.planid
where e.balance between d.baseval and d.highval

but here what i am requesting you is how can i change the highvalue to not include if it is 0

for example above example plan 1003, 1004 has highbal as 0 so i want to write a query to get employee data between 1000 and anyvalue (> 1000 - no limit)

same way for 1004 between 500 and no end values (infinity)

View 2 Replies


ADVERTISEMENT

Like Comparison Operator In An Expression To Ignore Case

Aug 31, 2007

How do you make the "Like" comparison operator ignore case in an expression??

Expression looks like this:




Code SnippetParameters!CompanyFilterOp.Value = "%",Fields!company.Value like "*" & Parameters!Company.Value & "*"





BTW, the expression above is part of a switch expression, and is at the table level.

Data contains "General Industry" in Company column from database.

User enters "indust" in Company Parameter text box, and result is no data found.

User enters "Indust" in Company Parameter text box, and data is returned.

Thanks in advance for your time in responding.

View 3 Replies View Related

Skip Or Ignore Where Clause In SP

Sep 20, 2013

I am looking for best practice when passing a parameter to stored procedure that is not needed. For example, sometime the users will want the list to list only by certain state. Other times the user want all states. How can I make the SP to ignore the where clause if users want all states.

CREATE PROCEDURE usp_Example
@State nvarchar(2)
AS
SELECT FirstName,
LastName,
State
FROM SomeTable
WHERE State = @FirstName;
GO

View 1 Replies View Related

How To Use Operator In Where Clause

Feb 13, 2014

We have a column in table which represents operator like

>,>=,<,<=, =

And we want to use it in sql query depending on the condition for each id column in table

Our table is

id int
operator nchar(10)

1,>=
2,<=
3,>
4,<
5,=
.
.
.
.

I need to use the operator from table into where clause for using condition for all the ids instead of single operator. I tried it using the dynamic query but how can we execute it for each id in single query.

View 2 Replies View Related

T-SQL (SS2K8) :: How To Ignore String Or Binary Data Would Be Truncated

Jan 16, 2015

I have a script to be used to backup a specific table in a weekly basis, here is the approach what I take:

1. script the source table's schema to a create
2. the new script:

If not exists (select * from sysobjects where name='EventlogHistory' and xtype='U')
CREATE TABLE [dbo].[EventlogHistory](
[LogID] [int] IDENTITY(1,1) NOT NULL,
[ProjectID] [int] NOT NULL,
[Description] [nvarchar](max) NOT NULL,
[EventType] [varchar](10) NOT NULL,
[IP] [varchar](50) NOT NULL,
[UserLogon] [varchar](30) NOT NULL,
[CreatedOn] [datetime] NOT NULL,
ArchivedOn datetime default getdate())
insert into EventlogHistory

It throws me the error message saying "String or binary data would be truncated" which is nonsense to me, I need to let sql ignore the error, it is ridiculous to do a max(len()) to find out as it should never happen, right?

View 9 Replies View Related

T-SQL (SS2K8) :: Ignore First Row In Data File In Bulk Insert?

Feb 17, 2015

I use bulk insert to fill a table

I use code bellow

bulk insert dbo.test
from 'c: est.txt'
with(FIRSTROW=2,FORMATFILE='c: est.xml'
go

but data inserted to table start from 3throw.

View 2 Replies View Related

Using LIKE Operator In An O BY Clause On SQL Server 2K5

Oct 3, 2007

I am migrating a classic ASP application that ran on Access to ASP.NET with SQL Server 2005 DB. I am having some trouble getting a query to work. It runs part of our courses section of the web site.
SELECT     CREDIT_DATA_LOCCODE.Loc_Code, CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Fiscal, CREDIT_DATA_COURSE.Term, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Status_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Course_Number2, CREDIT_DATA_COURSE.Section_Number, CREDIT_DATA_COURSE.Course_Title, CREDIT_DATA_COURSE.Credits, CREDIT_DATA_COURSE.Credits_Other, CREDIT_DATA_COURSE.Web_Website_link, CREDIT_DATA_COURSE.Web_Description, CREDIT_DATA_COURSE.Web_Instructor_1, CREDIT_DATA_COURSE.Web_Instructor_2, CREDIT_DATA_COURSE.Web_Location, CREDIT_DATA_COURSE.Web_LocCode, CREDIT_DATA_COURSE.Web_Dates, CREDIT_DATA_COURSE.Web_ETV_Dates_Times, CREDIT_DATA_COURSE.Web_ETN_Dates_Times, CREDIT_DATA_COURSE.Web_Times, CREDIT_DATA_COURSE.Enrollment_Limit, CREDIT_DATA_COURSE.Web_Icon_1, CREDIT_DATA_COURSE.Web_Icon_2, CREDIT_DATA_COURSE.Web_Note, CREDIT_DATA_COURSE.Web_Prereq, CREDIT_DATA_COURSE.Web_Special_Message, CREDIT_DATA_COURSE.Web_Alt_Major_Code, CREDIT_DATA_COURSE.Web_Alt_Course_Number1, CREDIT_DATA_COURSE.Web_Alt_Course_Number2, CREDIT_DATA_COURSE.Web_Alt_Section_Number, CREDIT_DATA_COURSE.Deleted, CREDIT_DATA_COURSE.Update_DateFROM  CREDIT_DATA_LOCCODE INNER JOIN CREDIT_DATA_COURSE ON CREDIT_DATA_LOCCODE.Loc_Code = CREDIT_DATA_COURSE.Web_LocCode WHERE (CREDIT_DATA_COURSE.Fiscal = N'078') AND (CREDIT_DATA_COURSE.Term = N'10') AND (CREDIT_DATA_COURSE.Status_Code <> 5) ORDER BY CREDIT_DATA_LOCCODE.Loc_Name, CREDIT_DATA_COURSE.Major_Code, CREDIT_DATA_COURSE.Course_Number1, CREDIT_DATA_COURSE.Section_Number
But when I run this query with the ORDER BY Clause in place against the SQL 5 tables Sheila dumps into I get this Error:

View 5 Replies View Related

T-SQL (SS2K8) :: Wrong Updates When Using Like Operator?

Jun 23, 2014

I am facing one issue while using LIKE operator in Update statement. Also want to understand why SQL behaving like this

Below is the scenario:

--Master INFO
Search Operator Set Value
Handling Charge Value 1
Handling Charge - PositioningValue 2
Handling Charge - IntermodalValue 3
Terminal Handling ChargeValue 4

[Code] ....

View 7 Replies View Related

T-SQL (SS2K8) :: Select Query With Multiple Where Conditions And OR Operator

Feb 6, 2015

I have the got below T SQL query to fetch the matching record

DECLARE @MappingTable TABLE
(
Productname nvarchar(10),
identification_key1 int,
identification_key2 int,
identification_key3 int

[Code] .....

-- result - 'Orange'

This is an exact matching record and straight forward

Is it possible to identify the record using T SQL query based on the following scenarios

1) return the record - If all the three where conditions match
2) if record not found check and return the record where 2 columns values in the where condition match

-- Expected Result for below query: 'Orange', because 2 of the columns in where condition have matching values

SELECT Productname
FROM @MappingTable where identification_key1=1 or identification_key2 =2 or identification_key3 =1

-- result - 'Orange'

View 4 Replies View Related

T-SQL (SS2K8) :: Varchar Datatype Field Will Ignore Leading Zeros When Compared With Numeric Datatype?

Jan 28, 2015

Need to know if the varchar datatype field will ingore leading zeros when compared with numeric datatype ?

create table #temp
(
code varchar(4) null,
id int not null
)
insert into #temp

[Code] .....

View 4 Replies View Related

T-SQL (SS2K8) :: Performance Of OR In Where Clause

Oct 17, 2014

I had a pretty simple query like

select * from table_A
where email in (select email from table_B)
or
email not in (select email from table_c)

It ran for a very long time.

But if I ran the select with either of the conditions by itself it took just a second. Combining both conditions really slowed it down.

View 4 Replies View Related

T-SQL (SS2K8) :: AND Statement In Where Clause

Nov 13, 2014

I have someone telling me that I should have put my AND statement in the join instead of the where part, so is there a difference in the where I put it. Is there a difference in the results in any way between the two here in the results in anyway?

Example:
Given a query:
But let’s take a simple one here:

Query(1)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
Where ct1.zipcode = '14124'
AND ct1.Name = 'Bob'

Query(2)
Select ct1.Name, ct1.address, ct1.city, ct1.state, ct1.zipcode
From cutTableA ct1 Left join cutTableA ct2
On ct1.ID = ct2.ID
AND ct1.zipcode = '14124'
Where ct1.Name = 'Bob'

View 4 Replies View Related

T-SQL (SS2K8) :: Add IF Condition To WHERE Clause

Feb 5, 2015

I want to add an exception to the WHERE clause below.

Something like:

IF @IDHuis = 'WH' THEN
add the extra condtion:
AND D.AfdelingZPT <> 'A01'

-- HRS VAST
INSERT INTO @T (JAAR, WEEK, HrsVast)

[Code] .....

View 9 Replies View Related

T-SQL (SS2K8) :: Join On Clause With IN Statement

Apr 25, 2014

I've come across a piece of code which i have never seen before.

ON T.CT_YEAR in
(
case
.[DBO].[FN_GET_YEAR]
(
CAST
(
C.[YEAR] AS VARCHAR(4)

[Code] ....

The CT_Year column is simply C for current year L for last year, O for Other, N for Next.

The Function simply returns the year value.

View 4 Replies View Related

T-SQL (SS2K8) :: Where Clause Integer Greater Than

Jul 21, 2014

When I use the following SQL with the variable @CutOFFTID the second select statement query takes several minutes. If i run it replacing the value in the where clause with the acutal value of the variable it runs instantly.

DECLARE @CutOffTid int
SELECT @CutOffTid = isnull(max(ccas_id),0)
FROMdbo.fbs_trans (NOLOCK)
print 'Cut Off ID is ' + cast(@CutOffTid as char)

DECLARE @MinPeriod int
SELECT @MinPeriod = Min(period)
FROM agr.dbo.atrans
WHERE agrtid >@CutOffTid

View 9 Replies View Related

T-SQL (SS2K8) :: Join Clause With BETWEEN Statement

Aug 12, 2014

I need to write a SQL script where a join condition is using date columns (effective_date, ineffective_date). The effective date columns can be slightly different (e.g. differ by a day) for some rows of data. I need the join condition to accommodate these date differences and return these rows of data as well.

I have a table which uses multiple joins to create another table but it turns out that the effective_date which is used in the join to match row together does not work all the time since some of the dates for the effective date column are out of sync meaning records that show data as missing even when the other table contains the data. I tried the SQL script below using the BETWEEN clause but it returning 6 rows instead of 3–

select t2.[entity_id]
,t2.[effective_date]
,[company_name]
,[last_accounts_date]
,[s_code]
,[s_code_description]

[Code] .....

View 2 Replies View Related

T-SQL (SS2K8) :: Two From Clause In DELETE Statement

Apr 17, 2015

I am supposed to delete all rows from USER and its child tables based on PracticeID = '55' filter condition.

I have dynamically generated queries to delete child table first followed by parent. Table [EncounterSignOff] si child and [User] is parent.

I would like to know, whether the Query 1 is valid for deleting child records?

Query:1

DELETE Top(100000) FROM [dbo].[EncounterSignOff]
FROM [dbo].[User] INNER JOIN [dbo].[EncounterSignOff] ON [User].[UserID] = [EncounterSignOff].[UserId]
WHERE [User].PracticeID = '55';

Query:2

DELETE Top(100000) FROM [dbo].[User] WHERE [User].PracticeID = '55';

View 5 Replies View Related

T-SQL (SS2K8) :: Case Statement In The Where Clause?

Jun 5, 2015

I have a population split between two vendors. One gets last names between A and R, the other the rest. Now, on a given date vendor 1 gets everybody.

I can accomplish this with a case statement on the upper range (R or Z), but it seems I should be able to do this without testing at all after the turnover date.

A small bit of the code:

declare @get_date datetime = convert(char(10),getdate(),101)
select top 10 pt.pt_id, pt.last_name
fromsmsmir.mir_acct a join smsmir.mir_pt pt on (a.src_sys_id = pt.src_sys_id
and a.pt_id = pt.pt_id
and a.from_file_ind = pt.from_file_ind

[Code] ....

Seems I should be able to not test the last name after the turnover date, but I can't figure out how.

View 2 Replies View Related

T-SQL (SS2K8) :: Get Output Of Procedure And Assign It To A Variable Used In WHERE Clause?

Mar 25, 2014

Get output of SQL Procedure and assign it to a variable used in WHERE Clause

Later I want to use this variable in my code in the WHERE Clause

Declare @ProjectNo nvarchar(10)

--Now I want to assign it to output of a storedprocedure which returns only 1 value and use it in the below SELECT query.

SELECT ID from TABLEA where Project in (@ProjectNo)

How to do it. How to assign @ProjectNo to output of storedProcedure called 'GetProjNumber'

View 1 Replies View Related

T-SQL (SS2K8) :: Pulling Incorrect Records Using Date Range In Where Clause

Apr 22, 2014

I've been experiencing difficulty with pulling records using a where clause date range. I'm using this:

select *
from dbo.ACCTING_TRANSACTION_hISTORY
where ath_postype = 'NTC' or ath_postype='NTD' and

ath_postdate >= '2013-01-01 00:00:00' and
ath_postdate <= '2013-01-05 23:59:59'

I've also tried variations of this without the time portion of the ath_postdate field (of type datetime) , but it still seems to be pulling records from 2009, etc.

View 9 Replies View Related

T-SQL (SS2K8) :: Convert Unique Identifier From String To Use In A Join / Where Clause

Jan 6, 2015

I am wanting to get the job name based on sys.sysProcesses.[Program_name] column. Why is this query not returning any results even though the 2nd substringed guids are found the the sysJobs table?

SELECTCASE
WHEN RTRIM([program_name]) LIKE 'SQLAgent - TSQL JobStep (Job %' THEN J.Name
ELSE RTRIM([program_name])
END ProgramName
, Val1.UqID
, Val1.UqIDStr

[Code] ......

View 6 Replies View Related

T-SQL (SS2K8) :: Change Set Clause Of Update Statement Dynamically Based On Some Condition

May 27, 2015

I want to change Set clause of Update Statement dynamically based on some condition.

Basically i have 2 Update statments having same FROM clause and same JOIN clause.

Only diff is SET clause and 1 Where condition.

So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.

Update t
Set CASE **WHEN Isnull(td.IsPosted, 0) = 0
THEN t.AODYD = td.ODYD**
*ELSE t.DAODYD = td.ODYD*
END
From #ReportData As t
Join @CIR AS tmp On t.RowId = tmp.Max_RowId

[Code] ....

But CASE statement is not working...

View 7 Replies View Related

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Nvarchar

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

Invalid Operator For Data Type. Operator Equals Boolean AND, Type Equals Datetime.

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

AVG... Need To Ignore 0

Apr 29, 2002

I need to average a column but ignore records that have a 0. Any thoughts?

View 1 Replies View Related

Pls Ignore

Apr 30, 2007

This was not intended here.

View 2 Replies View Related

Transact SQL :: How To Create UNION Clause With Two Queries That BOTH Have WHERE Clause

Nov 4, 2015

I have a quite big SQL query which would be nice to be used using UNION betweern two Select and Where clauses. I noticed that if both Select clauses have Where part between UNION other is ignored. How can I prevent this?

I found a article in StackOverflow saying that if UNION has e.g. two Selects with Where conditions other one will not work. [URL] ....

I have installed SQL Server 2014 and I tried to use tricks mentioned in StackOverflow's article but couldn't succeeded.

Any example how to write two Selects with own Where clauses and those Selects are joined with UNION?

View 13 Replies View Related

MS DTC (Very Very Urgent Plz Don&#39;t Ignore)

Jan 23, 2002

Hello Everyone,

When i checked the machine this morning i saw that MS DTC service was stopped and when did start it, it was good for a sec or two then it stopped auto matically i was not sure waht was goin on...

Then i checked the Eventviewer, it said the log file is full i tried out to find ms dtc log reset or increase the size but could not find out..where can i find this.

One more thing what is the purpose of DTC, i know in sql 6.5 we have everything in enterprize manager but i can't see the same either in 7.0 or 2000 is the name changed or is it linked to some other source.

Like linked server , does this depend on MS DTC..how and what is the basic purpose of this..kindly tell me what i should do now...

I got to fix it asap..big hassle..

Thanks in advance,

Kavitha

View 1 Replies View Related

Ignore Milliseconds

Mar 7, 2002

Hi
Does anybody know while loading data from text file into sql server, how
can we ignore milliseconds.

regards
JK

View 1 Replies View Related

Ignore Triggers

Jul 23, 2005

Is there a way I can perform an update on a table, but ignore thetrigger (or disable the trigger) each time I run a particular updatescript from a DTS package?I would like subsequent DTS steps to use the trigger except for my lastupdate statement. Is this possible?Thanks,Frank*** Sent via Developersdex http://www.developersdex.com ***

View 1 Replies View Related

Ignore Some Errors

Jan 25, 2006

In my stored procedure I'm calling a buggy and flaky stored procedurethat comes from a third party. When I run my stored proceure from QA,I'm getting a whole buch of errors raised inside the third party one.Is there any way I could just ignore them, so that if I run my SP fromQA, only errors from my code, if any, show up?TIA

View 2 Replies View Related

Bcp Right Truncation: How To Ignore

Mar 16, 2006

Hi,I'm trying to upload a large number of log entries currently stored astext files into a database table using bcp. For a few rows I get a"right truncation" error and the offending rows are not uploaded to thetable.I don't want to increase the size of the table varchar fields becauseit's only about a dozen out of almost million rows that have thisproblem ... I want to provide an override - i.e. if a row will resultin truncated data, truncate but still bulk copy the offending row. Isthat possible?I couldn't find such an option in the documentation.Any help is greatly appreciated.Thanks,Mudassir Latif

View 2 Replies View Related

Ignore Error In SP

Jul 20, 2005

Is there a way to make a SP ignore an error?e.g. I'm looping through each database on a server, checking of a tableexists then selecting a value from that table. Now I have a database putonto the server where the table exists but all column names aredifferent, my SP is not interested in this database so when it errorswith invalid column name I want it to move onto the next databse and notdisplay any error message.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved