Conversion of code from oracle to sql server

hi all
I've already written the following code which works fine in oracle.
can somebody help me out for sql server as we have migrated to sql
server 2000.
select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_D
EPARTMENT from EMPLOYEE
WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
connect by prior EMP_CODE = EMP_REP_AUTH
thanxHello,
You can write the recursive queries from SQL 2005 onwards.
http://www.sqlservercentral.com/col...00
5.asp
http://www.sqlservercentral.com/col...lserver2005.asp
Thanks
Hari
<jefftim@.gmail.com> wrote in message
news:1170159273.454099.318510@.p10g2000cwp.googlegroups.com...
> hi all
> I've already written the following code which works fine in oracle.
> can somebody help me out for sql server as we have migrated to sql
> server 2000.
> select EMP_ID,EMP_CODE,EMP_NAME,EMP_DESIG,EMP_D
EPARTMENT from EMPLOYEE
> WHERE RECORD_DELETED='0' START WITH EMP_CODE='" & objUser.Id & "'
> connect by prior EMP_CODE = EMP_REP_AUTH
>
> thanx
>sqlsql

Controlling fields in a select statement by use of parameters

Hi to all

I wish to be able to have a standard select statement which has
additional fields added to it at run-time based on supplied
parameter(s).

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

select
p_full_name
if @.theTest1='TRUE'
BEGIN
other field1,
END
if @.theTest2='TRUE'
BEGIN
other field2
END

from dbo.tbl_GIS_person
where record_id < 20

I do not wish to use an IF statement to test the parameter for a
condition and then repeat the entire select statement particularly as
it is a UNIONed query for three different statement

ie
declare @.theTest1 nvarchar(10)
set @.theTest1='TRUE'

declare @.theTest2 nvarchar(10)
set @.theTest2='TRUE'

if @.theTest1='TRUE' AND @.theTest2='TRUE'
BEGIN
select
p_full_name,
other field1,
other field2
from dbo.tbl_GIS_person
where record_id < 20
END

if @.theTest1='TRUE' AND @.theTest2='FALSE'
BEGIN
select
p_full_name,
other field1
from dbo.tbl_GIS_person
where record_id < 20
END
..
..
..
if @.theTest<>'TRUE'
BEGIN
select
p_full_name
from dbo.tbl_GIS_person
where record_id < 20
END

Make sense? So the select is standard in the most part but with small
variations depending on the user's choice. I want to avoid risk of
breakage by having only one spot that the FROM, JOIN and WHERE
statements need to be defined.

The query will end up being used in an XML template query.

Any help would be much appreciated

Regards

GIS AnalystIf you don't want to write three separate queries, then you'll probably
have to use dynamic SQL and build up the query string dynamically:

http://www.sommarskog.se/dyn-search.html
http://www.sommarskog.se/dynamic_sql.html

Alternatively, you could simply return all the columns all the time
(perhaps using CASE to return empty values for the unwanted columns so
as to minimize the data volume) and let the client decide which ones to
present/process, but in a more complex case it might not be workable.

Simon|||Hi Simon

thanks for the ideas. I did think about genearting the statement within
a stored procedure but thought I would check to see if there were
standard sql statement to do this first.
One reason for not returning all columns all the time is to avoid
record duplication when the optional fields are included. (Duplicates
apart from the optional field)

Regards

GIS Analyst

controlling database access

Hi All
I have a scenario where I have several processes (web-farm) that try to
process data in a certain table. I'd like to control access such that only 1
row from the table can be processed at a time regardless of how many
external processes try to access it. I guess I need to lock access so the
right sort of lock is required. Is it possible to lock a row/table based on
a read or does it have to be written to?
Currently using Serializable but it seems overkill.
Also is there a good recommendation for SQL server book that relates to
common SQL server tasks problems/solutions - A sort of patterns book?
Thanks
ShaunShaun Wilde wrote:
> Hi All
> I have a scenario where I have several processes (web-farm) that try
> to process data in a certain table. I'd like to control access such
> that only 1 row from the table can be processed at a time regardless
> of how many external processes try to access it. I guess I need to
> lock access so the right sort of lock is required. Is it possible to
> lock a row/table based on a read or does it have to be written to?
> Currently using Serializable but it seems overkill.
> Also is there a good recommendation for SQL server book that relates
> to common SQL server tasks problems/solutions - A sort of patterns
> book?
> Thanks
> Shaun
You could use an Application Lock. See sp_getapplock and
sp_releaseapplock in BOL.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||Did you mean to say only one row to be processed at a time OR a row can be
processed by one and only one process?
Regarding books, this might interest you:
SQL Server 2000 Fast Answers for DBAs and Developers:
http://vyaskn.tripod.com/sql_server...ast_answers.htm
--
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Shaun Wilde" <shaun_wilde@.nospam.nospam> wrote in message
news:OFGDayhZFHA.3152@.TK2MSFTNGP14.phx.gbl...
> Hi All
> I have a scenario where I have several processes (web-farm) that try to
> process data in a certain table. I'd like to control access such that only
1
> row from the table can be processed at a time regardless of how many
> external processes try to access it. I guess I need to lock access so the
> right sort of lock is required. Is it possible to lock a row/table based
on
> a read or does it have to be written to?
> Currently using Serializable but it seems overkill.
> Also is there a good recommendation for SQL server book that relates to
> common SQL server tasks problems/solutions - A sort of patterns book?
> Thanks
> Shaun
>|||Hi Vyas
I mean that only one row can be processed at a time by only one process.
Process A - works on row 1
Process B - works on row 2
Process C sees there is nothing to do and waits (polling)
Shaun
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:u$#Y64hZFHA.3280@.TK2MSFTNGP09.phx.gbl...
> Did you mean to say only one row to be processed at a time OR a row can be
> processed by one and only one process?
> Regarding books, this might interest you:
> SQL Server 2000 Fast Answers for DBAs and Developers:
> http://vyaskn.tripod.com/sql_server...ast_answers.htm
> --
> Vyas, MVP (SQL Server)
> SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
>
> "Shaun Wilde" <shaun_wilde@.nospam.nospam> wrote in message
> news:OFGDayhZFHA.3152@.TK2MSFTNGP14.phx.gbl...
only
> 1
the
> on
>|||Hi Shaun,
It seems you want the row be modified / updated only by one process while
other is not able to do anything? I am afraid we do not have exactly same
funcation as you needed.
You may check the topic "Isolation Levels" in BOL for more reference,
different isolation level will provide different data access privilege.
However you will have to customize your project manually. For example
1. Add a new column named IsAccess and defaultly set to zero
2. When one process want to use the row, it will have to use a transaction
to update IsAccess column
3. If the column is updated to 1, which means some process is working on
this row and others will have to wait.
Something like realize a lock system yourself, but more complex. You may
refer lock mechanism in operating systems.
Sincerely yours,
Michael Cheng
Microsoft Online Partner Support
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.

Control number of options selected in a multi select parameter

Hi All

I have a report which has a multi-value parameter. Problem is, it can contain up to 100 options.

Is there a way to limit the number of options that is passed to the SQL statement?. EG list has 100 options, user selects 10 but only the first 4 selected options are passed to the SQL statement.


Many Thanks
Delli
I will try an expression for the query parameter (Parameters tab in the dataset properties) which passes your parameter to code-behind function that in turn filters out the parameter values accordingly.|||

After looking into this some more, I’ve found a split

function for MS SQL server, and in PL/SQL. Have system in both databases

:( grrrrrrr

The split function takes in the multi select parameter as a comma separated

list, and creates a virtual table of the results.

By using SQL code similar to the following: select top 10 element

dbo.split('string,split,code',',') I could stop the SQL engine running

for too many selected parameters. A search on Google or msn search ;) will find

codes examples for these functions. Keywords: SQL split function or PL/SQL

split function.

Also helps to inform your users on the front page of the report you have

done this!!!