Help With NOT EXISTS Query

Jul 20, 2005

I am having trouble with what will surely be a simple query for you experts.

I have 2 tables with inventory data.
IMITMIDX contains the master item info
IMINVLOC contains location specific data such as quantity on hand at that
location.

These tables have 2 commons fields, ITEM_NO and LOC

I need to search the IMINVLOC table for any records where ITEM_NO and LOC do
not match that in the IMITMIDX table.

The following query give me zero records even though I can manually find
some records:

SELECT *
FROM IMINVLOC_SQL INNER JOIN
IMITMIDX_SQL ON IMITMIDX_SQL.item_no = IMINVLOC_SQL.item_no
where not exists (select loc from iminvloc_sql where IMITMIDX_SQL.loc =
IMINVLOC_SQL.loc)

Any ideas?
Thanks.

View 5 Replies

NOT EXISTS Query

Dec 6, 2007

I have a NOT EXISTS Query that works, but I have been giving a new requirement from MGMT. They initially wanted to know if a record existed in the system. Now they want to know if one exists over certain time periods (i.e. last 24 hours, 48 hours, etc...)

The System:

This is an ASP.NET application that uses table1 and table2 as list items in a drop down list. Once a record is created in the system it puts a time date stamp on the record along with other fields. For reporting I only care about reporting on two fields. This will be used to show activity in the system. MGMT expects at bare minimum 3 entries per 24 hour period.

SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2))

The above returns records where there are no entries in Table3 for table1.Department AND Table2.field2. I now have the requirement to do the same thing, but also to include records where table3.insert_date was in the last 24 hours, 48 hours, etc...

I have tried adding:

Table3.insert_date >= GetDate() - 1)

but it still only returns records with no entry at all.

SELECT table1.Department, Table2.field2
FROM table1 INNER JOIN
Table2 ON Table1.DeptID = Table2.DeptID
WHERE (NOT EXISTS
(SELECT Table3.Department, Table3.Field2
FROM Table3
WHERE Table1.Department = Table3.Department AND Table2.Field2 = Table3.Field2 AND Table3.insert_date >= GetDate() - 1))

I have moved it all over the place in this query changed AND to OR and cannot get the desired result.

View 4 Replies View Related

MS Sql Query Problem Mit Not Exists , Help

May 9, 2007

Hello,
i hope that somebody can help me. I have a problem with ms sql query.
i have two tabels(wtmenSends and T1) in a database. i need pick up all email addresses from table wtmenSends but without those email addresses which are in table T1.
I have written this sql query to get these Emails, but i get nothing by this query. Can somebody tell me, where is the problem? Thanks.
select wtmenSends.Email
from wtmenSends
where not exists(
select T1.*
from(
SELECT wtmenSends.Id, wtmenSends.Email, wtmenSends.IDMailing, wtmenSends.Title, wtmenSends.Firstname, wtmenSends.Lastname, wtmenSends.IDUser,
wtmenSends.IdStatus, wtmenSends.IsSent, wtmenSends.DateSent, wtmenSends.wtobjIDClass, wtmenSends.wtobjDateCreated,
wtmenSends.wtobjDateChanged, wtmenSends.wtobjUserCreated, wtmenSends.wtobjUserChanged
FROM wtmenRobinsons INNER JOIN
wtmenSends ON wtmenSends.Email NOT LIKE '%' + wtmenRobinsons.Filter AND wtmenRobinsons.IsDomain = 1) as T1 inner join
wtmenRobinsons on wtmenRobinsons.Filter = T1.Email)

View 2 Replies View Related

Help Replacing 'where Not Exists' In A Query

Jul 20, 2007

I am trying to clean up an ugly query that's based on trying to find items that exist in one table but not the other.
My tables are like this:
ITEMSitemID,itemName,itemDescriptionetc...
ORDERITEMSOrderLineID,OrderID,itemID
ORDERSOrderID,OrderCompleted
Currently my query looks something like this:
Select Items.* from ITEMS where not exists (select 1 from ORDERITEMS inner join ORDERS on ORDERITEMS.OrderID = ORDERS.OrderID where ORDERITEMS.itemID=ITEMS.itemID and (ORDERS.OrderCompleted=1))
So this query is looking for ITEMS what don't have a corresponding entry in the ORDERITEMS table. As I understand it this is pretty inefficient as it is going to be executing the sub query in the Not Exists statement for each entry in the ITEMS table. Is the preferred method to do something along the lines of somehow making the sub query into a derived table and doing a left or right join?
Thanks for reading!
Ryan

View 7 Replies View Related

Nested Exists Query

Nov 15, 2001

I am trying to write a query that does not use inner joins to see if it returns rows faster than a query using inner joins.
My trouble is that I don't know how to write the syntax for the 3rd table that I am comparing against.
Can I nest Exists in a Where clause?
If so how is the 2nd exists statement added to the query?
see my sample query below
================================================== ===
'With 2 tables

SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
ORDER BY SITE

'with 3 tables this doesn't work

SELECT DISTINCT s1.ID, s1.SITE
FROM SITE_TBL s1
WHERE EXISTS (SELECT *
FROM DEVICE_TBL d1
WHERE s1.ID = d1.SITE_ID AND d1.DELETEFLAG <> 'D')
AND
(SELECT *
FROM BIG_TBL b1
WHERE d1.fqdn = b1.xyz)
ORDER BY SITE

================================================== ===
Thanks for the help
Jim

View 1 Replies View Related

Exists Query With Priorities

Jul 23, 2005

I've been presented with a task to do a query similar to the followingand I was curious as to what the quickest query would look like.Anyone have any ideas??Some_Id Value1 A1 B1 C2 C2 A2 B3 B3 C4 C5 Q5 C5 R6 T7 P7 BThe problem is that I want to select one record for each ID. If arecord with the value of 'A' exists, then I want to select that recordfor that ID. If not, I want to select the record with the value 'B'for that ID if it exists. Otherwise, just give me the first record forthat ID that exists. The result set would look like this:Some_ID Value1 A2 A3 B4 C5 Q6 T7 BThanks for your input!

View 12 Replies View Related

Using EXISTS With Dynamic Query

Sep 21, 2007

In a SP I need to know if certain records already exist
but the query is parameter dependent so I can't code
IF EXISTS (SELECT ...)
because the proper select must be calculated.
Using
EXEC (@CalculatedQuery)
IF @@ROWCOUNT = 0
Puts the results of @CalculatedQuery into my SP result set.
This is highly undesireable.

View 5 Replies View Related

Transact SQL :: If Exists In Query

Nov 18, 2015

I am using SQL Server 2008 - and what I want to do is set my variable @dh. If the @startDate and @endDate falls into the criteria for my if exists statement, I want to set @dh equal to datediff(h, logontime, logofftime) BUT if that criteria is not true, I want to set @dh = 24. How can I do that?

Declare @startDate date, @endDate date, @employeeID varchar(100), @userid varchar(100), @dh int

Set @startDate = '11/09/2015'
Set @endDate = '11/14/2015'
Set @employeeID = 'ab12345'
Set @userid = '162489'

[Code] .....

View 3 Replies View Related

IF EXISTS SQL Question - Using Query Analyzer

Aug 3, 2007

I want to only go after Distinct email addressess that contain an @ symbol.
But then I want to return all fields. How do you correctly do that for Microsoft SQL Server?IF EXISTS (SELECT DISTINCT user_username FROM usr WHERE(user_username LIKE N'%@%'))

SELECT *
FROM usr Order By user_username

View 2 Replies View Related

Checking Whether Record ID Exists In Another Query

Jan 10, 2014

I'm trying to check which price grids are in use using the price grid_id, and seeing whether this grid_id exists in another query that checks all active contracts. If the grid_id is present (active) I want to return 'Yes', if not I want it to return 'No'.

There are 385 price grids, but my query is only returning the 315 that are active, and ignoring any that are not used. My code is below, how I can see all the records whether Yes or No:

Select distinct
pg.grid_id [Price Grid],
pg.grid_name [Grid Name],
case when exists
(Select
c.grid_id from
customers c
inner join deltickhdr dh on dh.acct = c.custnum and dh.stage <5
where
c.type = 'C' and
dh.dticket is not null) then 'Yes' else 'No' end [Active]

From gridhdr pg inner join customers c on c.grid_id = pg.grid_id

Order by pg.grid_id

View 4 Replies View Related

Transact SQL :: Working Of Aggregate In Sub-query Using Exists?

Aug 4, 2015

When i am running below snippet execution plan is showing constant scan instead of referring subquery table.

I want to know how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.

select count(*) from A where exists (select count(1) from B where A.a=B.a)

execution plan has to show scan or seek for subquery. Surprisingly, output is coming as expected.

View 8 Replies View Related

SQL 2012 :: Not Exists Linked Server Object Query

Aug 10, 2015

So, I've got this query running and it works great providing there is a record in both DataBases. Now, I need to get all of those that have a record in DBServer1 but not in TranscendDB. I assume i'd use an If not exists, but can't figure out the syntax when using the Linked Object...

select Portfolio
from [TranscendDB].[dbo].[CMContactEvents] as CM
inner join
(
select N.SSN, A.ACCOUNTNUMBER
from [DBServer1].[DB1].[dbo].[Account] AS A,

[Code] ......

View 4 Replies View Related

Transact SQL :: Query Works Even If Column Not Exists In Subquery

Jul 23, 2015

When I execute the below queries it works perfectly where as my expectation is, it should break.

Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1)
In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break -
Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)

Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.

create table TestDepartment
(
DeptId int identity(1,1) primary key,
name varchar(50)
)
create table ChildDepartment
(
Id int identity(1,1) primary key,

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Select Query - Get Result As Month And Values For All Months Whether Or Not Data Exists

Jul 27, 2015

I have a table with dates and values and other columns. In a proc i need to get the result as Month and the values for all the months whether or not the data exists for the month.

The Similar table would be-

create table testing(
DepDate datetime,
val int)
insert into testing values ('2014-01-10 00:00:00.000', 1)
insert into testing values ('2014-05-19 00:00:00.000', 10)
insert into testing values ('2014-08-15 00:00:00.000', 20)
insert into testing values ('2014-11-20 00:00:00.000', 30)

But in result i want the table as -

Month Value

Jan1
Febnull
Marnull
Aprnull
May10
Junnull
Julnull
Aug20
Sepnull
Octnull
Nov30
Decnull

View 9 Replies View Related

Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?

May 22, 2007

Hello to all,
I have a problem with ms sql query. I hope that somebody can help me.
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)). Example Datas for Table Relationships:                               IDMember     Relationships              .
                                                                                                                3387            (2345, 2388,4567,....)
                                                                                                                4567           (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query:
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))

But I get nothing by this query.
Can Someone tell me where is the problem? Thanks

Best Regards
Pinsha

View 9 Replies View Related

IF NOT EXISTS (... - EXISTS TABLE : Nested Iteration. Table Scan.Forward Scan.

Sep 20, 2006

Hi,

This is on Sybase but I'm guessing that the same situation would happen on SQL Server. (Please confirm if you know).

I'm looking at these new databases and I'm seeing code similar to this all over the place:

if not exists (select 1 from dbo.t1 where f1 = @p1)
begin
select @errno = @errno | 1
end

There's a unique clustered in dex on t1.f1.

The execution plan shows this for this statement:

FROM TABLE
dbo.t1
EXISTS TABLE : nested iteration.
Table Scan.
Forward scan.
Positioning at start of table.

It's not using my index!!!!!

It seems to be the case with EXISTS statements. Can anybody confirm?

I also hinted to use the index but it still didn't use it.

If the existence check really doesn't use the index, what's a good code alternative to this check?

I did this and it's working great but I wonder if there's a better alternative. I don't really like doing the SET ROWCOUNT 1 and then SET ROWCOUNT 0 thing. SELECT TOP 1 won't work on Sybase, :-(.

SET ROWCOUNT 1
SELECT @cnt = (SELECT 1 FROM dbo.t1 (index ix01)
WHERE f1 = @p1
)
SET ROWCOUNT 0

Appreciate your help.

View 3 Replies View Related

IF NOT EXISTS

Jul 7, 2006

Hello,
I am trying to create a table if one with the same name does not exists. My code is:
Dim connectionString As String = "Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|PensionDistrict4.mdf;Integrated Security=True;User Instance=True"
Dim sqlConnection As SqlConnection = New SqlConnection(connectionString)
Dim newTable As String = "CREATE TABLE [" + titleString + "Comments" + "] (ID int NOT NULL PRIMARY KEY IDENTITY, Title varchar(100) NOT NULL, Name varchar(100) NOT NULL, Comment varchar(MAX) NOT NULL, Date datetime NOT NULL)"
sqlConnection.Open()
Dim sqlExists As String = "IF EXISTS (SELECT * FROM PensionDistrict4 WHERE name = '" + titleString + "Comments" + "')"
Dim sqlCommand As New SqlCommand(newTable, sqlConnection)
If sqlExists = True Then
sqlCommand.Cancel()
Else
sqlCommand.ExecuteNonQuery()
sqlConnection.Close()
End If
I keep getting a "Input String was incorrect format" for sqlExists? I am new to Transact-SQL statements, any help would be appreciated.
Thanks Matt

View 1 Replies View Related

Where Not Exists---help

May 27, 2007

ok i have 2 tables---one table is name CourseInformation with a field named Instructor and the data in there looks like this 'John Doe'.
My other table Instructors has 3 fields InstructorName, LastName, FirstName.
I am grabbing the Instructor field from CourseInformation and breaking up the names and inserting them into my instructors table as follows..
Insert into Instructors(InstructorName, LastName, FirstName)
(Select Distinct
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor)))+', '+
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1),
ltrim(SUBSTRING(Instructor,CHARINDEX(' ',Instructor)+1,len(Instructor))) as LName,
SUBSTRING(Instructor,1,CHARINDEX(' ',Instructor)-1) as FNamefrom CourseInformation
where NOT EXISTS(Select * from instructors where LastName = LName and FirstName = FName)
AND Instructor is not null)

Only problem is, I cant get the where not exists clause to work right(of course that wont work what i have cuz the LName and FName columns dont exist, i just did that for demo purposes). I dont want duplicate instructors in there..how can i accomplish this..is there a better way to rewrite my query? Any help is appreciated.

View 3 Replies View Related

NOT EXISTS Vs NOT IN

Feb 7, 2008

HI All,Which is best among the two 1) NOT IN or 2) NOT EXISTS .If the query is Select col1 from tab1 where col2 NOT IN (Select col 3 from tab2 where cl3=0) OR Select col1 from tab1 where col2 NOT EXISTS (Select col 3 from tab2 where cl3=0)

View 2 Replies View Related

If Not Exists

Oct 12, 2001

Hi,

I have a question on the if not exists. Can you do something like this? If request not exists in table one then insert values into table two?

Thank you in advance,
Anne

View 3 Replies View Related

If Exists..

Jul 15, 2004

Hi,
Is there any way to check whether a column is there in the table, if it is there i need to drop it through script.

i'm looking for the script, something like this..

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Tbl_Product_Tbl_Products]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Tbl_Product] DROP CONSTRAINT FK_Tbl_Product_Tbl_Products
GO

In the same way i need to check for a column and drop it through script.
Any help would be greatly appreciated.
Thanks in advance.

View 2 Replies View Related

Help With EXISTS

Jun 15, 2007

hi guys! is there anyway to retrieve the row from the result of EXISTS function aside from using the code below?

if EXISTS (select * from rcps_useraccount where User_login = 'daimous')
BEGIN
select * from rcps_useraccount where User_login = 'daimous'
END

View 3 Replies View Related

Exists

May 16, 2008

Im having a problem with the following can anyone spot how i can fix it? I dont think it likes the begin statement but without it, it has a declare issue.

IF EXISTS (SELECT 1
FROM snapevent.INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE='BASE TABLE'
AND TABLE_NAME='FastEp_Snap_OD_Acc' + preports.dbo.f_filedate(getdate())
begin
declare @CMD nvarchar(300)
Set @CMD = 'drop table Snap_OD_Acc_' + preports.dbo.f_filedate(getdate())
--print @CMD
exec (@CMD)
end

View 1 Replies View Related

If Exists - SP

Jun 20, 2008

Hi

Please help me

I have to create a SP.

The secenario is that,
A application calls the SP with a parameter(login), and a string of datas ( Acctid level1 level2; Acctid level1 level2; .......)

UserID AcctID Level1 level2
test testee N Y

the SP have to get the first string of data and check if the Acctid exists or not. If yes then update else insert.Then get then the second string of data and check if the Acctid exists or not. If yes then update else insert.

After checking all the strings ,
it have to check if any Acctids other than acctid mentioned in the string exists in the table for that login, then delete those rows

View 1 Replies View Related

Where Exists

Feb 18, 2006

I'm trying to perform an insert query on a table, but I also want to check to see if the record exists already in the table. It should be fairly simple, but I'm having a time of it. Should be something like:

select * from users u
inner join miamiherald m
on u.emailaddress = m.advertiseremail
where not u.emailaddress not exists <<< (???)

If it does exist, I then want to retrieve two columns from it. HELP!!

View 1 Replies View Related

IF EXISTS

May 28, 2007

Hi,

The following works in SQL 2005 but NOT SQL 2005 Compact Edition:

IF EXISTS (SELECT ID FROM Court2 WHERE BookingDate = '2007-05-28')
UPDATE Court2 SET T1100 = 52 WHERE (BookingDate = '2007-05-28')
ELSE
INSERT INTO Court2 (BookingDate,T1100) VALUES ('2007-05-28',52)

In CE I get the following error:

There was an error parsing the query. [ Token line number = 1,Token line offset = 1,Token in error = IF ]

I can't find where the problem is - can someone help.

Thanks,

View 5 Replies View Related

Which One Is Better In Or Exists

Dec 6, 2007

Dear all,
i am trying to improve the performance of stored procedures and functions in that in key word is there i have to replace with exists.which one will give better performance.

Thanks&Regards,

Msrs

View 2 Replies View Related

From In To Exists

Dec 6, 2007

Dear all,
i'm trying to replace in with exists...
i've changed the query but still i'm getting error.
please let me know where i'm missing

DELETE FROM table25 WHERE Col6='SET' AND Col1 IN (SELECT s.Col1 FROM Table24 s
WHERE Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

DELETE FROM table25 r WHERE r.Col6='SET' AND exists (SELECT s.Col1 FROM Table24 s
WHERE r.col1=s.col2 and Col2='some_data1' AND Col3='some_data2' AND Col4='somedata3')

thank you very much

Vinod
Even you learn 1%, Learn it with 100% confidence.

View 9 Replies View Related

Need Help With IF And EXISTS

Jan 15, 2008

I'm trying to search my table for a url, and if it in the table, increment the number of hits of the url. Here's my code:

tblLinks
-----------------------
URL | NumOfHits
-----------------------

ALTER PROCEDURE sproc_UpdateLink

(
@url varchar
)

AS

IF EXISTS(SELECT LinkURL
FROM tblLinks
WHERE @url = LinkURL)
BEGIN
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
END

RETURN

I've also tried:

ALTER PROCEDURE sproc_UpdateLink

(
@url varchar
)

AS

IF (SELECT LinkURL
FROM tblLinks
WHERE @url = LinkURL) IS NOT NULL
BEGIN
UPDATE tblLinks
SET NumOfHits = NumOfHits + 1
WHERE @url = LinkURL
END

RETURN

but that didn't work either.

View 7 Replies View Related

IF EXISTS

Mar 20, 2008

There's an error in this SQL statement somewhere, but I can't seem to find it. Here's what I'm trying to do:

I have a list of states, and each state is assigned an ID
For each state, I have some data
Given a state name, find the state's ID
If the state ID exists, output the data for that state
If the state ID is not in the database, output the data for all states

Here's my tables:

--------------------------
States
--------------------------
ID | Name
--------------------------

--------------------------
Data
--------------------------
StateID | StateData
--------------------------

Here's my SQL:

ALTER PROCEDURE sproc_GetStateData
(
@State varchar
)
AS
DECLARE @StateID int

IF EXISTS(SELECT @StateID = ID FROM States WHERE Name = @State)
BEGIN
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
WHERE (D.StateID = @StateID)
END
ELSE
SELECT D.StateData
FROM Data AS D INNER JOIN States AS S
ON D.StateID = S.ID
RETURN

View 6 Replies View Related

Help With Exists

Jul 23, 2005

Hi,I need some help doing a simple query. Here is what I haveSELECT Category_Id, Category_nameFROM AG_Category WHERE Auth_Logic_Id = 244And Category_ID(I want category_id to not have a '.' in it. Basically returningeverything above but checking also the category_id so it doesn't returnone where category_id have a . in it)Thanks:D

View 1 Replies View Related

Using EXISTS

Jul 23, 2005

I need to insert records into the table parSalesDetailModifier fromOLDparSalesDetailModifier where (1) those records DO NOT exit inparSalesDetailModifier and (2) those records have a parent record inparSalesDetail.When I run the below query I get the error message that I am violatingthe Primary Key Constraint for parSalesDetailModifier. In other words,it's trying to insert a record that does exist.Also posted below are create and insert startements for thte tables.If someone would be kind enough to show me what I am doing wrong, I'dreally appreciate it.Thanks,Jennifer-------------------------------- STORED PROCEDURE-------------------------------CREATE Proc LoadModifier2@S datetime,@E datetimeASINSERT INTO ParSalesDetailModifier(parSalesDetailModifierID,parSalesHdrID,parSalesDetailID,ModifierType,POSModifier,Condiment,CondimentPrice,UnitNumber,BusinessDay)SELECTOLD.parSalesDetailModifierID,OLD.parSalesHdrID,OLD.parSalesDetailID,OLD.ModifierType,OLD.POSModifier,OLD.Condiment,OLD.CondimentPrice,OLD.UnitNumber,OLD.BusinessDayFROM OldParSalesDetailModifier OLDWHEREEXISTS( SELECT DET.parSalesHdrID,DET.parSalesDetailID,DET.UnitNumber,DET.BusinessDayFROM ParSalesDetail DETWHERE OLD.parSalesHdrID = DET.parSalesHdrIDANDOLD.parSalesDetailID = DET.parSalesDetailIDANDOLD.UnitNumber = DET.UnitNumberANDOLD.BusinessDay = DET.BusinessDay)ANDNOT EXISTS( SELECT NEW.parSalesHdrID,NEW.parSalesDetailID,NEW.parSalesDetailModifierID,NEW.UnitNumber,NEW.BusinessDayFROM ParSalesDetailModifier NEWWHERE OLD.parSalesHdrID = NEW.parSalesHdrIDANDOLD.parSalesDetailID = NEW.parSalesDetailIDANDOLD.parSalesDetailModifierID = NEW.parSalesDetailModifierIDANDOLD.UnitNumber = NEW.UnitNumberANDOLD.BusinessDay = NEW.BusinessDay)AND OLD.BusinessDay between @S and @E-------------------------------- END STORED PROCEDURE--------------------------------------------------------------- CREATE TABLES-------------------------------CREATE TABLE [parSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetailModifier] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID],[ParSalesDetailModifierID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetailModifier_parSalesDetail] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) REFERENCES [parSalesDetail] ([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID])) ON [PRIMARY]GOCREATE TABLE [OLDparSalesDetailModifier] ([ParSalesDetailModifierID] [int] NOT NULL ,[parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[ModifierTYPE] [int] NULL ,[POSModifier] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,[Condiment] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[CondimentPrice] [money] NOT NULL ,[UnitNumber] [int] NOT NULL ,[BusinessDay] [datetime] NULL) ON [PRIMARY]GOCREATE TABLE [parSalesDetail] ([parSalesHdrID] [int] NOT NULL ,[parSalesDetailID] [int] NOT NULL ,[Before] [int] NOT NULL ,[Quantity] [int] NOT NULL ,[After] [int] NOT NULL ,[Promo] [money] NOT NULL ,[PromoBefore] [money] NOT NULL ,[ItemPrice] [money] NOT NULL ,[PromoAfter] [money] NOT NULL ,[POSItem] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[UnitNumber] [int] NOT NULL ,[Depleted] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,[AmountTenderTime] [datetime] NULL ,[BusinessDay] [datetime] NOT NULL ,CONSTRAINT [PK_parSalesDetail] PRIMARY KEY CLUSTERED([BusinessDay],[UnitNumber],[parSalesHdrID],[parSalesDetailID]) WITH FILLFACTOR = 70 ON [PRIMARY] ,CONSTRAINT [FK_parSalesDetail_parSalesHdr] FOREIGN KEY([BusinessDay],[UnitNumber],[parSalesHdrID]) REFERENCES [parSalesHdr] ([BusinessDay],[UnitNumber],[parSalesHdrID])) ON [PRIMARY]GO-------------------------------- END CREATE TABLES--------------------------------------------------------------- INSERT INTO TABLES-------------------------------insert into parSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917332,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917332,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917332,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917340,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917340,2,'NOONIN','NO ONIN',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917340,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(4,2298561,10917340,2,'WELL','WELL',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (1,2298561,10917341,2,'ADDG-ON','ADD G-ON',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values (2,2298561,10917341,2,'SUBMAYO','SUB MAYO',.0000,2,'2003-12-01')insert into oldparSalesDetailmodifier values(3,2298561,10917341,2,'TBBS','TBBS',.0000,2,'2003-12-01')insert into parSalesDetailvalues(2298561,10917332,0,1,0,.0000,.0000,3.4900,. 0000,'DM',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917340,0,1,0,.0000,.0000,.2500,.0 000,'JALA',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10917341,0,1,0,.0000,.0000,1.3400,.0000,'MD-DP',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928910,0,1,0,.0000,.0000,.9900,.0000,'2PIE99',2,'N','2003-12-0110:00:02.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928911,0,1,0,.0000,.0000,.5900,.0000,'DECAF',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10928912,0,1,0,.0000,.0000,1.6900,.0000,'BOB-BAC',2,'N','2003-12-0110:09:44.000','2003-12-01')insert into parSalesDetailvalues(2298561,10929376,0,1,0,.0000,.0000,.5900,.0000,'COFFEE',2,'N','2003-12-0110:00:44.000','2003-12-01')-------------------------------- END INSERT INTO TABLES-------------------------------

View 7 Replies View Related

IF EXISTS Help

Oct 31, 2007

Hi,
I'm having problems with this T-SQL:
Sorry if this is obvious, I'm very new to this...

Just trying to return the ID if the row exists and if not just return 0.
What would I need to turn this into a stored procedure too?

Code Block

declare @Return int

if exists(select id from Computer where Computer_name = 'MYCOMPUTER')
begin
select @Return = id
PRINT @Return
end
else
begin
select @Return = 0
PRINT @Return
end

I'm getting this error....

Msg 207, Level 16, State 1, Line 5Invalid column name 'id'.Msg 207, Level 16, State 1, Line 10Invalid column name 'id'.

View 5 Replies View Related