"Senad Ljuca" <senad@ljuca.com> wrote in message
news:c59krl$s2s$00$1@news.t-online.com...
> Is there any way to generate artificial Query Result from Non-existing
> table, as scalar array needed for another Query?
>
> For Example:
> There is one Table tbl1 with the Field f1, and appropriate Values v1, v2,
> v3,
> Therefore, a Query SELECT f1 FROM tbl1 gives the following:
> f1
> --
> v1
> v2
> v3
> ...
>
> What I need is the following Result:
> f1, f2
> -----
> v1, 1
> v1, 2
> v1, 3
> v1, 4
> v2, 1
> v2, 2
> ...
>
> therefore I need some artificial Table with one int field and 4 Records:
1,
> 2, 3, 4, then could I with CROSS JOIN query statement acchieve the goal.
>
> I would appreciate any suggestion
>
>
>
> Best Regards, Senad
>
>
>
>
>

> Hi Senda,
>
> One solution is to use a table valued function:
>
> create function ufn_number (@size int)
> returns @tbl table (number int)
> as
> begin
> while @size > 0
> begin
> insert into @tbl(number) values (@size)
>
> set @size = @size - 1
> end
>
> return
> end
> go
>
>
> Call this function as a normal table like with a parameter to determine
the
> size:
>
> select number from ufn_number(4)
>
> or in your case:
>
> SELECT f1, number
> FROM tbl1 CROSS JOIN ufn_number(4)
>
>
> --
> Karl Gram, BSc, MBA
> http://www.gramonline.com
>
>
> "Senad Ljuca" <senad@ljuca.com> wrote in message
> news:c59krl$s2s$00$1@news.t-online.com...
> > Is there any way to generate artificial Query Result from Non-existing
> > table, as scalar array needed for another Query?
> >
> > For Example:
> > There is one Table tbl1 with the Field f1, and appropriate Values v1,
v2,
> > v3,
> > Therefore, a Query SELECT f1 FROM tbl1 gives the following:
> > f1
> > --
> > v1
> > v2
> > v3
> > ...
> >
> > What I need is the following Result:
> > f1, f2
> > -----
> > v1, 1
> > v1, 2
> > v1, 3
> > v1, 4
> > v2, 1
> > v2, 2
> > ...
> >
> > therefore I need some artificial Table with one int field and 4 Records:
> 1,
> > 2, 3, 4, then could I with CROSS JOIN query statement acchieve the
goal.
> >
> > I would appreciate any suggestion
> >
> >
> >
> > Best Regards, Senad
> >
> >
> >
> >
> >
>
>

On Sat, 10 Apr 2004 22:19:57 +0200, Senad Ljuca wrote:

>Is there any way to generate artificial Query Result from Non-existing
>table, as scalar array needed for another Query?
>
>For Example:
>There is one Table tbl1 with the Field f1, and appropriate Values v1, v2,
>v3,
>Therefore, a Query SELECT f1 FROM tbl1 gives the following:
>f1

> On Sat, 10 Apr 2004 22:19:57 +0200, Senad Ljuca wrote:
>
> >Is there any way to generate artificial Query Result from Non-existing
> >table, as scalar array needed for another Query?
> >
> >For Example:
> >There is one Table tbl1 with the Field f1, and appropriate Values v1, v2,
> >v3,
> >Therefore, a Query SELECT f1 FROM tbl1 gives the following:
> >f1
>
> As an alternative to Karl's function, you could also use UNION to
> create the non-existing table as a derived table:
>
> select tbl1.f1,tbl2.f2
> from tbl1
> cross join (select 1 AS f2
> union select 2
> union select 3
> union select 4) AS tbl2
>
>
> Best, Hugo
> --
>
> (Remove _NO_ and _SPAM_ to get my e-mail address)

"Senad Ljuca" <senad@ljuca.com> wrote in message
news:c59te7$bgq$04$1@news.t-online.com...
> Hi Hugo,
>
> Thanks for your support. I have compared the two solutions. Both of them
> function correctly, the one with the function seems to be about 10%
quicker
> (even as quick as with the static Table in the Database!)
>
> Best Regards
>
> Senad
>
>
> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> schrieb im Newsbeitrag
> news:ccpg7017hiuisigk11mhv4c25ilg5j5dqg@4ax.com...
> > On Sat, 10 Apr 2004 22:19:57 +0200, Senad Ljuca wrote:
> >
> > >Is there any way to generate artificial Query Result from Non-existing
> > >table, as scalar array needed for another Query?
> > >
> > >For Example:
> > >There is one Table tbl1 with the Field f1, and appropriate Values v1,
v2,
> > >v3,
> > >Therefore, a Query SELECT f1 FROM tbl1 gives the following:
> > >f1
> >
> > As an alternative to Karl's function, you could also use UNION to
> > create the non-existing table as a derived table:
> >
> > select tbl1.f1,tbl2.f2
> > from tbl1
> > cross join (select 1 AS f2
> > union select 2
> > union select 3
> > union select 4) AS tbl2
> >
> >
> > Best, Hugo
> > --
> >
> > (Remove _NO_ and _SPAM_ to get my e-mail address)
>
>