Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to rewrite this basic query for SQL CE ? Expand / Collapse
Author
Message
Posted Monday, January 17, 2011 10:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 2, 2011 2:56 PM
Points: 23, Visits: 117

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.
Post #1048847
Posted Thursday, February 17, 2011 4:54 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 7:36 AM
Points: 377, Visits: 3,063
Will you be executing this code from a .Net application?
Post #1065561
Posted Thursday, February 17, 2011 6:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, August 2, 2011 2:56 PM
Points: 23, Visits: 117
yes
Post #1065597
Posted Thursday, February 17, 2011 6:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:03 PM
Points: 36,983, Visits: 31,508
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1065622
Posted Thursday, February 17, 2011 8:41 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Tuesday, August 5, 2014 7:36 AM
Points: 377, Visits: 3,063
OK

We have two issues here. First, you can't use TSQL variables in SQL CE. Secondly, SQL CE processes single commands through a connection. So you need to do the following through your .NET application:

As I haven't got a copy of your database or schema to work against, the following is to demonstrate the principle of what you need to do. It is not tested or working code.

Firstly declare a recordset or datareader object and populate it with the result of the first query:

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


With the value now in that object, you are ready for the next part of the query which can be executed as follows:

using (SqlCeConnection connect = new SqlCeConnection("Data Source=C:\[YourDataFileHere].sdf"))     
{ connect.Open();
int location = [value from first aprt of the query];
int num = 0;
using (SqlCeCommand command = new SqlCeCommand("UPDATE JURISDICTION_COUNTRY_INPUT
SET FOREIGN_RESIDENCE_LOCATION_CODE = @tmp_RESIDENCE_LOCATION
WHERE COUNTRY_TYPE = 1 AND COST_PROJECTION_ID = 2", connect))
{ command.Parameters.Add("@tmp_RESIDENCE_LOCATION", SqlDbType.[Int or whatever datatype it is], location);

command.ExecuteNonQuery();
}
}

HTH
Post #1065728
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse