IF EXIST (SET SELECT ) <> NULL -- Suggestions ??

[inarobis]

Hello Guys,

I have question concerning IF EXISTS (this statement is not correct but I would like to test if this select statement is true, if it is I qould like to insert the @DATE, @VALUE_, @S, @T of table @QT1 into the table QT)

I would like to do a test:

DECLARE @DATE_ datetime
DECLARE @VALUE_ varchar(100)
DECLARE @T_ varchar(30)
DECLARE @S_ varchar(30)
DECLARE @Max_ID int

/* the max of SEEDID */
SET SELECT TOP 1 @Max_ID = SEEDID from QT order by SEEDID desc

SET @Max_ID = @Max_ID + 1

/* if this condition is not null */
IF EXISTS (
SET SELECT @DATE_ =DATE, @VALUE_ = VALUE_, @S_ = S, @T_ = T
FROM
WHERE QT1.SC = 7 and QT1.code= 1476 and QT1.DATE not in (
SELECT QT.DATE
FROMWHERE QT.SC = 7 and QT.code = 1476) IS NOT NULL)

/* insert into QT */
INSERT INTO QT (SEEDID, CODE, S, DATE, VALUE_, SC. T)
VALUES ( @Max_ID, 1476, 'HS', @S_, @DATE_, @VALUE_, 7, @T_ )


any suggestion how to do that?

Thanks on advance

Ina

 

[bborissov]

try this:

DECLARE @DATE_ datetime
DECLARE @VALUE_ varchar(100)
DECLARE @T_ varchar(30)
DECLARE @S_ varchar(30)
DECLARE @Max_ID int

/* the max of SEEDID */
SELECT TOP 1  @Max_ID = SEEDID+1 from QT order by SEEDID desc

/* if this condition is not null */
IF EXISTS (SELECT DATE, VALUE_, S, T
                  FROM WHERE QT1.SC = 7     and
                             QT1.code= 1476 and
                             QT1.DATE not in (SELECT QT.DATE FROM
                                                     WHERE QT.SC = 7 and QT.code =  1476))
   BEGIN
        INSERT INTO QT (SEEDID, CODE, S, DATE, VALUE_, SC, T)
        SELECT @Max_ID, 1476, 'HS', S, DATE, VALUE_, 7, T
                  FROM WHERE QT1.SC = 7     and
                             QT1.code= 1476 and
                             QT1.DATE not in (SELECT QT.DATE FROM
                                                     WHERE QT.SC = 7 and QT.code =  1476))
   END

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[bborissov]

Oops, remove all declared variables except @Max_Id, you don't need them.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[inarobis]

Thanks it is works if the code = 1476 but when I put this code in a Loop for each @CODEId do this TEST doesn't insert into.


Why?

Thanks again

Ina

 

[bborissov]

You don't need to put this in a loop, just change WHERE clause to match all records you needed (in both SELECTs).

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[inarobis]

One more question how can I do something like this

/* if this condition is not null */
IF EXISTS (SELECT DATE, VALUE_, S, T
                  FROM WHERE QT1.SC = 7     and
                             QT1.code= 1476 and
                             (QT1.DATE and QT1.T ) not in (SELECT QT.DATE FROM
                                                     WHERE QT.SC = 7 and QT.code =  1476))
   BEGIN
        INSERT INTO QT (SEEDID, CODE, S, DATE, VALUE_, SC, T)
        SELECT @Max_ID, 1476, 'HS', S, DATE, VALUE_, 7, T
                  FROM WHERE QT1.SC = 7     and
                             QT1.code= 1476 and
                             (QT1.DATE and QT1.T ) not in (SELECT QT.DATE FROM
                                                     WHERE QT.SC = 7 and QT.code =  1476))
   END

How Can I aggregate this condition in the NOT IN

Ina

 

[bborissov]

Can you post full select, becuase you didn't set Tables after FROM clauses?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[inarobis]

This is my new select with a join

what I would like to do it is to select all the difference between #QT1 and QT and if in the table QT doesn't exist I would like to insert into it (sometimes the date doesn't exist in QT so I need to insert into the two record depends on the type)


SO I would like to aggregate DATE_, T_

I can have one row

DATE_ T_ (Type)

10-10-2006 L
10-10-2006 S


#QT1 is tempDB

SELECT  distinct #QT1.DATE_,  #QT1.VALUE_, #QT1.SC, #QT1.CODE , #QT1.T_
FROM QT  right join QT
on #QT.CODE = QT.CODE
WHERE #QT1.SC = 7 and #QT1.CODE = 1476 
and QT.DATE_ = #QT1.DATE_ and #QT1.T_ not in (
SELECT T_FROM QT
WHERE SC = 7 and CODE =  1476)
order by #QT.DATE_ desc

But I do not know how to do because if the date doesn't exist it will insert into

Ina

 

[bborissov]

Ina, there is something wrong here. You join the table to itself: FROM QT right join QT and use #QT in join condition.
As I said post some data from both tables and write desired result. I am not so good in English so it is hard to me to understand what are your exact needs.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP