How can i use it ??? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

How can i use it ???

Author

Topic

Dan_2004
Starting Member

13 Posts

Posted - 2004-02-09 : 11:38:13

Hi,
I have to re-write a PL\SQL program into SQL (T-SQL) and i don't know how can i re-write the following statement:

"SELECT APBatchNum.NextVal INTO Batch_Number FROM Dual;"

If someone can teach me how can i use the 'Dual table' in SQL, it would be appreciate!!!

Tia,

Dan

jsiedliski
Yak Posting Veteran

61 Posts

Posted - 2004-02-09 : 17:44:00

Oh boy....It's been awhile (about 2 years) since I worked with Oracle. Isn't Dual a table used for testing?

Anyway, are you trying to fill an AUTONUMBER column? Like an auto generated primary key?

If so, in SQL Server, you would to this on the table definition side. Just set up a coulumn (Id) and make sure you set Identity property of that column to "Y". You can then specify the Identity seed (start IDs at 5000, for instance) and the increment (Seed +1 for the next record or seed +5).

More info from you would help me better understand what you are tyring to do.

--Jack

Rome was not built overnight.

Dan_2004
Starting Member

13 Posts

Posted - 2004-02-09 : 20:15:52

Thanks Jack,
Your suggestion is appreciated! It's an idea. The real thing that i have to do is this: in Oracle, i created a subfile with the "Util.FileOpen" function and i put some records in this subfile and i generated a batch number with the "Select" expression. Now in T-SQL i have to rewrite the PL\SQL program and i think that your suggestion will be good because i will create a subfile 'Tmp...' with a column 'Batch_Num' that i will increment...
Thanks again,

Dan

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-10 : 11:47:17

DUAL?

Anyone know what DUAL is....

Well, I'll tell ya...

Oracle and DB2 (SYSIBM.SYSDUMMY1) have system cat tables that contain only 1 row...for the sole purpose of allowing us the privilige to SELECT only 1 literal value...

In SQL server you don't even need a table...

Just do SELECT 'myValue'

Or SELECT GetDate()

(Boy I like that...)

But aha..you want to do more than that..you want to increment the value...so...why aren't you using a table?

In addition, why don't you use...

CREATE TABLE myTable99(Col1 int IDENTITY(1,1), Col2, ect)..

(Until recently IDENTITY, autonumber, what have you was a foreign concept to Oracle and DB2...they caved)

You may also want to look up IDENT_CURRENT...

Or SELECT MAX([id]) FROM Table if not....

And why is this in a file?

Brett

8-)

Dan_2004
Starting Member

13 Posts

Posted - 2004-02-10 : 15:05:45

Ok! Brett,

In my script I wrte it:
CREATE TABLE DUAL (Batch_num int Identity (1,1))
:
:
SELECT @Batch_Number = (IDENT_CURRENT('DUAL')) + 1
:
:
SET @Batch_Num_O =
REPLICATE('0',(5 - datalength(convert(varchar(5),@Batch_Number)))) + @Batch_Num_O
:
:
and the value for the @Batch_Number element is always 'NULL'
Why i don't receive the value of the DUAL record + 1?

Thanks,

Dan

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-10 : 15:18:25

[code]
DECLARE @Batch_Number int

CREATE TABLE DUAL (Batch_num int Identity (1,1), Dummy char(1))

INSERT INTO DUAL(Dummy) SELECT 'X'

SELECT @Batch_Number = MAX(Batch_Num) FROM DUAL

SELECT @Batch_Number
[/code]

I don't know what this is meant to do:

[code]
SET @Batch_Num_O =
REPLICATE('0',(5 - datalength(convert(varchar(5),@Batch_Number)))) + @Batch_Num_O
[/code]

You'll control the value by the INSERT

Take a look at this

http://vyaskn.tripod.com/oracle_sql_server_differences_equivalents.htm

[

Brett

8-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources