Home Forums SQL Server 2005 SQL Server 2005 Compact Edition How to rewrite this basic query for SQL CE ? RE: How to rewrite this basic query for SQL CE ?

  • February 17, 2011 at 6:51 am

    #1288194

    akhandels (1/17/2011)

    Hi,

    I have this piece of code written for SQL Server 2008. With all the limitations of SQLCE can anybody please help to rewrite this query?

    It is a basic update statement, which gets the value from a pre-query.

    declare @tmp_RESIDENCE_LOCATION NVARCHAR(50)

    SET @tmp_RESIDENCE_LOCATION = ((SELECT TOP(1) FOREIGN_RESIDENCE_LOCATION_CODE FROM FOREIGN_RESIDENCE_LOCATION WHERE COUNTRY_ID

    IN

    (SELECT HOST_COUNTRY_PLACE_ID FROM COST_PROJECTION CP

    WHERE CP.COST_PROJECTION_ID = 2)

    )

    ORDER BY SORT_INDEX)

    UPDATE JURISDICTION_COUNTRY_INPUT

    SET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATION

    WHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2

    Thanks.

    The only thing I see that may cause CE some heartburn is the SELECT TOP (1). I believe you'll need to change it to just SELECT TOP 1 (ie. without the parenthesis).

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)