当前位置: 动力学知识库 > 问答 > 编程问答 >

How Can I Do This ORACLE Statement in SQL Server?

问题描述:

SELECT COUNT(Field1_)

FROM

(

SELECT Field1_

FROM Table1_

WHERE Field1_= @Field1

UNION ALL

SELECT Field1_

FROM Table2_

WHERE DeliveryPoint_ = @Field1

UNION ALL

SELECT Table3_

FROM GasSupplied_

WHERE DeliveryPoint_ = @Field1

UNION ALL

SELECT Table4_

FROM Gnsnominations_

WHERE DeliveryPoint_ = @Field1

UNION ALL

SELECT Table5_

FROM HourlyProfileReports_

WHERE DeliveryPoint_ = @Field1

)

And I have problems with this code. Please help me.

DECLARE

CountedRows NUMBER;

BEGIN

SELECT COUNT(*) INTO CountedRows

FROM Profiles_

WHERE Field1_ = @Param1 AND RowNum < 2;

IF(CountedRows > 0)

THEN

UPDATE Profiles_

SET

Field2_ = @Param2,

Field3_ = @Param3,

Field4_ = @Param4

WHERE Field1_ = @Param1;

ELSE

INSERT INTO Profiles_

(

Field1_,

Field2_,

Field3_,

Field4_

)

VALUES

(

@Param1,

@Param2,

@Param3,

@Param4 );

END IF;

END;

Please help me. I read many books and tutorials but I can't understand this.

网友答案:

Your first one needs a table alias

SELECT COUNT(Field1_) 
           FROM 
           (
               SELECT Field1_
               <snip>
               WHERE DeliveryPoint_ = @Field1
           ) AS foo

Your second one should begin

            DECLARE CountedRows int;


              SELECT CountedRows = COUNT(*) 
              FROM Profiles_ 
              WHERE Field1_ = @Param1 AND RowNum < 2;  

To be syntactically correct. Also use END; not END IF; However it gives you a potential race condition. As you are on SQL Server 2008 you should look into MERGE for doing this kind of UPSERT

As far as I can see the whole of the second one can be replaced with.

MERGE INTO Profiles_
USING (VALUES(@Param1_) )T(P1)
ON Field1_ = P1
WHEN MATCHED THEN
  UPDATE SET Field2_ = @Param2_,
             Field3_ = @Param3_,
             Field4_ = @Param4_
WHEN NOT MATCHED BY TARGET THEN
  INSERT
  VALUES (P1, @Param2_, @Param3_, @Param4_);  
网友答案:

Set command is preferred over the select command. So, instead you should pay homage to the select command and code it as

set countedRows = (SELECT COUNT(*) FROM Profiles_ );
分享给朋友:
您可能感兴趣的文章:
随机阅读: