SQL Query

[chorpa]

Is there a way to specify the number you want to select a specific record? For instace the following select statement return one record:

SELECT *
FROM Stock
where itemid = 6

I want to add code to this statement to return the results x number om times?

CAn anyone help??? Is this possible to do???

 

[bborissov]

What do you mean? Can you post some example data and desired result?

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[chorpa]

SELECT *
FROM Stock
where itemid = 6

The statement above returns this:

ItemID Name Weight
6 Parsley 500

The results I want should return the record x number of thimes where x is 3 in the following example:

ItemID Name Weight
6 Parsley 500
6 Parsley 500
6 Parsley 500

Hope this is clear... The record appears once in the databse but the query should return it 3 times.

Thanks

 

[bborissov]

Isn't that easier to accomplish in your FrontEnd?
That way you will reduce Network traffic. But on top of my head:

DECLARE @lcSQL varchar(8000)
DECLARE @lnNumberOfRecords 

SET @lcSQL = ''
SET @lnNumberOfRecords = 3 -- We want 3 records
WHILE @lnNumberOfRecords > 0
      BEGIN
         SET @lcSQL = @lcSQL +
                     'SELECT * FROM Stock where itemid = 6'
         SET @lnNumberOfRecords = @lnNumberOfRecords - 1
         IF @lnNumberOfRecords > 0
            SET @lcSQL = @lcSQL + ' UNION ALL '

      END

EXEC (@lcSQL)

not tested

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[r937]

do you have an integers table? if not, you may find it to be extremely useful, for this as well as other purposes

create table integers 
  (i integer not null primary key) 
insert into integers (i) values (0) 
insert into integers (i) values (1) 
insert into integers (i) values (2) 
insert into integers (i) values (3) 
insert into integers (i) values (4) 
insert into integers (i) values (5) 
insert into integers (i) values (6) 
insert into integers (i) values (7) 
insert into integers (i) values (8) 
insert into integers (i) values (9)

now to create your results...

SELECT *
FROM Stock [red]cross join integers[/red]
where itemid = 6 [red]and i < 3[/red]

r937.com | rudy.ca