Hi.
I'm a casual sql user. I have found a situation where I need to convert an
oracle statement to tsql, one I can just fire off in any sql tool against an
ms sql server database.
I studied the exists statement and I think I understand it somewhat, however I
was not sure how to get it quite right. If you have an idea and a minute or
two I'd appreciate any insight or tutorial.
insert into authorization (program, optiontitle, usergroup, authorizationid)
select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval
from allgroups where exists (select * from authorization
where authorization.USERGROUP = allgroups.USERGROUP and
authorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE')
I believe that because in my data, three values of usergroup from allgroups
return true from the exists, that this is supposed to insert three rows into
authorization.
But I can't figure out what to do about the authorization.nextval.. I tried
various max(authorization)+1
etc but nothing seemed to compile/work
thanks
Jeff Kish 6 1810
Hi
You can make the column an identity, this will not guarantee contiguous
number but it will be increasing/decreasing and unique. You can then miss it
out from the statement altogether.
These may help: http://vyaskn.tripod.com/oracle_sql_...quivalents.htm http://www.microsoft.com/technet/pro...rt2/c0761.mspx
John
"Jeff Kish" <je*******@mro.com> wrote in message
news:b5********************************@4ax.com... Hi. I'm a casual sql user. I have found a situation where I need to convert an oracle statement to tsql, one I can just fire off in any sql tool against an ms sql server database.
I studied the exists statement and I think I understand it somewhat, however I was not sure how to get it quite right. If you have an idea and a minute or two I'd appreciate any insight or tutorial.
insert into authorization (program, optiontitle, usergroup, authorizationid) select 'EVERYWHERE','NAVIGATOR',usergroup, authorizationseq.nextval from allgroups where exists (select * from authorization where authorization.USERGROUP = allgroups.USERGROUP and authorization.optiontitle = 'READ' and authorization.program = 'EVERYWHERE') I believe that because in my data, three values of usergroup from allgroups return true from the exists, that this is supposed to insert three rows into authorization.
But I can't figure out what to do about the authorization.nextval.. I tried various max(authorization)+1 etc but nothing seemed to compile/work
thanks Jeff Kish
Jeff,
authorizationseq seems to be a sequence. Sequences just don't exist in
MS SQL Server 2000/7/6.5. You might want to have an identity column.
On Sat, 4 Feb 2006 16:17:55 -0000, "John Bell" <jb************@hotmail.com>
wrote: Hi
You can make the column an identity, this will not guarantee contiguous number but it will be increasing/decreasing and unique. You can then miss it out from the statement altogether.
These may help: http://vyaskn.tripod.com/oracle_sql_...quivalents.htm http://www.microsoft.com/technet/pro...rt2/c0761.mspx
thanks. I'm still not sure of how to do something here, though.
This is directly related to the problem but re-worded because I need to
get the next value using max(authorizationid)+1 ...
Given two tables:
allgroups(usergroup, otherdata) =
{'group1',otherdata1,
'group2',otherdata2,
'group3',otherdata3,
:
:
'groupn',otherdatan}
and
authorization(program,optiontitle,
usergroup,authorizationid) =
{'pro1','title1','ug1',3,
'pro2','title2','ug2',4,
:
'pron','titlen','ugn',m}
How can I insert multiple
lines (one for each usergroup
in allgroups) using one sql statement
into authorization if this is correct for a
single insert:
insert into authorization(program,
optiontitle,usergroup,
authorizationid)
select 'proq','titleq','ug1',
max(authorizationid)+1
from authorization
bascially I'd like each usergroup
from allgroups to be used to create a
new line in authorization, having
the authorizationid increment one from
the current max.
Yes, I have no control over the design/use of
an identity column.
Is it possible?
Thanks
Jeff Kish
Jeff Kish wrote: thanks. I'm still not sure of how to do something here, though.
This is directly related to the problem but re-worded because I need to get the next value using max(authorizationid)+1 ...
Given two tables: allgroups(usergroup, otherdata) = {'group1',otherdata1, 'group2',otherdata2, 'group3',otherdata3, : : 'groupn',otherdatan}
and authorization(program,optiontitle, usergroup,authorizationid) = {'pro1','title1','ug1',3, 'pro2','title2','ug2',4, : 'pron','titlen','ugn',m}
How can I insert multiple lines (one for each usergroup in allgroups) using one sql statement into authorization if this is correct for a single insert: insert into authorization(program, optiontitle,usergroup, authorizationid) select 'proq','titleq','ug1', max(authorizationid)+1 from authorization
bascially I'd like each usergroup from allgroups to be used to create a new line in authorization, having the authorizationid increment one from the current max.
Yes, I have no control over the design/use of an identity column.
Is it possible? Thanks Jeff Kish
Assuming SQL Server 2005 (you didn't specify otherwise), use the
ROW_NUMBER function. For example:
INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT MAX(authorizationid)
FROM [authorization])
FROM
(SELECT 'proq','titleq', usergroup, ROW_NUMBER()
OVER (ORDER BY usergroup)
FROM allgroups)
AS T(program, optiontitle, usergroup, authorizationid) ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
<snip> Assuming SQL Server 2005 (you didn't specify otherwise), use the
Sql Server 2000. Really sorry I did not say it up front. Do you know
of a way using that?
Regards,
Jeff
ROW_NUMBER function. For example:
INSERT INTO [authorization] (program, optiontitle, usergroup, authorizationid) SELECT program, optiontitle, usergroup, authorizationid+ (SELECT MAX(authorizationid) FROM [authorization]) FROM (SELECT 'proq','titleq', usergroup, ROW_NUMBER() OVER (ORDER BY usergroup) FROM allgroups) AS T(program, optiontitle, usergroup, authorizationid) ;
-- David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
Jeff Kish wrote: <snip> Assuming SQL Server 2005 (you didn't specify otherwise), use the
Sql Server 2000. Really sorry I did not say it up front. Do you know of a way using that? Regards, Jeff
INSERT INTO [authorization]
(program, optiontitle, usergroup, authorizationid)
SELECT program, optiontitle, usergroup,
authorizationid+
(SELECT COALESCE(MAX(authorizationid),0)
FROM [authorization])
FROM
(SELECT 'proq','titleq', A1.usergroup, COUNT(*)
FROM allgroups AS A1
JOIN allgroups AS A2
ON A1.usergroup >= A2.usergroup
GROUP BY A1.usergroup)
AS T(program, optiontitle, usergroup, authorizationid) ;
This assumes that usergroup is unique in Allgroups. If I'm wrong then
use the key of that table in the join. You need a key in order to
generate the sequence.
Read my signature. It may help you get faster answers in future.
Hope this helps.
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: JBBHF |
last post by:
Hi i'm working on a web project, and i would like to make my oracle
query work in mysql.
select match.numero "nummatch",
to_char(match.datematch, 'yyyy-MM-dd') "datematch",
p1.numjoueur "j1",...
|
by: pierig.gueguen |
last post by:
Hello,
I would like to know if the equivalent Oracle rownum exist in
SQLServer. Here is a sample SQL code to explain what I want to do :
select
jobs.name,
jobs.job_id,
jobs.description,...
|
by: fareeda |
last post by:
What is the equivalent of "@" (oracle) in db2?
I could use -f option but the basic requirement is to be able to call
file Y from file X.
In Oracle I can have file X as:
---
command 1;
command...
|
by: gimme_this_gimme_that |
last post by:
Assuming data is being stored in a DB2 TIMESTAMP
what is the equivalent of Oracle's to_date function :
to_date('03/04/2005','MM/DD/YYYY')
It's OK if MM/DD/YYYY is the only supported format....
|
by: gimme_this_gimme_that |
last post by:
Is there something equivalent to Oracle's
SELECT DBMS_LOB.GETLENGTH(COLUMN_NAME) FROM FOO
where COLUMN_NAME is a CLOB in table FOO
returning an integer with a count of the number of...
|
by: hilz |
last post by:
Hi all,
What is the equivalent of VARCHAR2 in access?
thanks
hilz
|
by: Doug Bloebaum |
last post by:
In Oracle I can do this:
SELECT h.transaction_number
CURSOR(SELECT d.detail_number
FROM detail d
WHERE d.transaction_number=h.transaction_number)
FROM header h
WHERE...
|
by: ghe |
last post by:
Good day to all,
Does anyone here know how to convert the specified Oracle code below into PHP?
Oracle Code: TYPE myTable IS TABLE OF varchar2(1) INDEX BY BINARY_INTEGER;
If the Oracle code i...
|
by: DTV12345 |
last post by:
Greetings!
Imagine 2 hosts: host A runs Oracle 10g , host B runs IBM DB2 v9.
I have to write a shell script (bask/Korn...) that checks if a database instance is up and running and does a bunch...
|
by: kg6ypx |
last post by:
Greetings!
Imagine 2 hosts: host A runs Oracle 10g , host B runs IBM DB2 v9. I
have to write a shell script (bask/Korn...) that checks if a database
instance is up and running and does a bunch...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...
| |