| Author |
Message |
|
FR
#1 / 15
|
 query help appreciated
-- This is the data that it returns which is fine.
[Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
I would like the output to return this instead ofcourse with some added work
.
a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
Whatever data gets outputted Id like to split it into sub values that are
seperated by comman and see it as a.<subvalue1> - b.<subvalue1>, and so on
|
| Mon, 31 May 2004 09:22:13 GMT |
|
 |
|
Hirantha S Hettiarachch
#2 / 15
|
query help appreciated Can you post the DDL and some sample data ? --
-------------------------------------------
Hirantha S Hettiarachchi MCSD,MCDBA,OCP
Please reply only to the newsgroups.
Quote:
> -- This is the data that it returns which is fine.
> [Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
> I would like the output to return this instead ofcourse with some added
work
> .
> a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
> b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
> Whatever data gets outputted Id like to split it into sub values that are
> seperated by comman and see it as a.<subvalue1> - b.<subvalue1>, and so on
|
| Mon, 31 May 2004 09:46:56 GMT |
|
|
|
FR
#3 / 15
|
query help appreciated create table rules (name varchar(50 , xyz varchar(200) ) insert rules values ('3-28', '[Tstbits1], [Tstbits2], [Tstbits3],
[Diffbits]')
insert rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
Quote: > Can you post the DDL and some sample data ? > --
> -------------------------------------------
> Hirantha S Hettiarachchi MCSD,MCDBA,OCP
> Please reply only to the newsgroups.
> > -- This is the data that it returns which is fine.
> > [Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
> > I would like the output to return this instead ofcourse with some added
> work
> > .
> > a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
> > b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
> > Whatever data gets outputted Id like to split it into sub values that
are
> > seperated by comman and see it as a.<subvalue1> - b.<subvalue1>, and so
on
|
| Mon, 31 May 2004 09:40:23 GMT |
|
|
|
oj
#4 / 15
|
query help appreciated using woodoo... create table #rules
(name varchar(50) ,
xyz varchar(200) )
insert #rules values ('3-28', '[Tstbits1], [Tstbits2],
[Tstbits3],[Diffbits]')
insert #rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
~a.~ + str2 + ~ - b.~ + str2 + ~, ~ +
~a.~ + str3 + ~ - b.~ + str3 as [str]
from rac'
--Result---
str
----------------------------------------------------------------------------
---------
a.[Diffbits] - b.[Diffbits], a.[Tstbits1] - b.[Tstbits1], a.[Tstbits2] -
b.[Tstbits2]
-oj
Quote: > create table rules > (name varchar(50 , > xyz varchar(200) ) > insert rules values ('3-28', '[Tstbits1], [Tstbits2], [Tstbits3],
> [Diffbits]')
> insert rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> > Can you post the DDL and some sample data ?
> > --
> > -------------------------------------------
> > Hirantha S Hettiarachchi MCSD,MCDBA,OCP
> > Please reply only to the newsgroups.
> > > -- This is the data that it returns which is fine.
> > > [Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
> > > I would like the output to return this instead ofcourse with some
added
> > work
> > > .
> > > a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
> > > b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
> > > Whatever data gets outputted Id like to split it into sub values that
> are
> > > seperated by comman and see it as a.<subvalue1> - b.<subvalue1>, and
so
> on
|
| Mon, 31 May 2004 10:06:01 GMT |
|
|
|
FR
#5 / 15
|
query help appreciated Well OJ, i cant use voodoo as i dont have it.. How else can i achieve the same..
cmon OJ, you can use ur smart SQL statements and figure this one out
Quote: > using woodoo... > create table #rules
> (name varchar(50) ,
> xyz varchar(200) )
> insert #rules values ('3-28', '[Tstbits1], [Tstbits2],
> [Tstbits3],[Diffbits]')
> insert #rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> EXECUTE Rac
> ~a.~ + str2 + ~ - b.~ + str2 + ~, ~ +
> ~a.~ + str3 + ~ - b.~ + str3 as [str]
> from rac'
> --Result---
> str
> --------------------------------------------------------------------------
--
> ---------
> a.[Diffbits] - b.[Diffbits], a.[Tstbits1] - b.[Tstbits1], a.[Tstbits2] -
> b.[Tstbits2]
> -oj
> > create table rules
> > (name varchar(50 ,
> > xyz varchar(200) )
> > insert rules values ('3-28', '[Tstbits1], [Tstbits2], [Tstbits3],
> > [Diffbits]')
> > insert rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> > > Can you post the DDL and some sample data ?
> > > --
> > > -------------------------------------------
> > > Hirantha S Hettiarachchi MCSD,MCDBA,OCP
> > > Please reply only to the newsgroups.
> > > > -- This is the data that it returns which is fine.
> > > > [Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
> > > > I would like the output to return this instead ofcourse with some
> added
> > > work
> > > > .
> > > > a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
> > > > b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
> > > > Whatever data gets outputted Id like to split it into sub values
that
> > are
> > > > seperated by comman and see it as a.<subvalue1> - b.<subvalue1>, and
> so
> > on
|
| Mon, 31 May 2004 10:10:16 GMT |
|
|
|
oj
#6 / 15
|
query help appreciated floyd, by the way, why are you doing this at the backend. you could easily handle
these at the frontend (i.e. vb: split()/join()). if you must do at the
backend, you have a few options:
1. create a cursor and step through - yuk!
2. a. split the row into rows - you can use joe's approach for this
b. transform each row to desired format (i.e. a.[row] - b.[row])
c. combine rows into a single row
3. a. split the row into columns
b. transform each column to desired format
c. combine columns into row
g'luck.
-oj
Quote: > Well OJ, i cant use voodoo as i dont have it.. > How else can i achieve the same..
> cmon OJ, you can use ur smart SQL statements and figure this one out
> > using woodoo...
> > create table #rules
> > (name varchar(50) ,
> > xyz varchar(200) )
> > insert #rules values ('3-28', '[Tstbits1], [Tstbits2],
> > [Tstbits3],[Diffbits]')
> > insert #rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> > EXECUTE Rac
> > ~a.~ + str2 + ~ - b.~ + str2 + ~, ~ +
> > ~a.~ + str3 + ~ - b.~ + str3 as [str]
> > from rac'
> > --Result---
> > str
> --------------------------------------------------------------------------
> --
> > ---------
> > a.[Diffbits] - b.[Diffbits], a.[Tstbits1] - b.[Tstbits1], a.[Tstbits2] -
> > b.[Tstbits2]
> > -oj
> > > create table rules
> > > (name varchar(50 ,
> > > xyz varchar(200) )
> > > insert rules values ('3-28', '[Tstbits1], [Tstbits2], [Tstbits3],
> > > [Diffbits]')
> > > insert rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> > > > Can you post the DDL and some sample data ?
> > > > --
> > > > -------------------------------------------
> > > > Hirantha S Hettiarachchi MCSD,MCDBA,OCP
> > > > Please reply only to the newsgroups.
> > > > > -- This is the data that it returns which is fine.
> > > > > [Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
> > > > > I would like the output to return this instead ofcourse with some
> > added
> > > > work
> > > > > .
> > > > > a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
> > > > > b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
> > > > > Whatever data gets outputted Id like to split it into sub values
> that
> > > are
> > > > > seperated by comman and see it as a.<subvalue1> - b.<subvalue1>,
and
> > so
> > > on
|
| Mon, 31 May 2004 11:31:47 GMT |
|
|
|
Dinesh T
#7 / 15
|
query help appreciated Floyd, Quote: >>>Well OJ, i cant use voodoo as i dont have it..
I once predicted that one day you will find the truth..still having doubts with the voodoo man? :-) Yours "truly",
Dinesh.
Quote: > Well OJ, i cant use voodoo as i dont have it.. > How else can i achieve the same..
> cmon OJ, you can use ur smart SQL statements and figure this one out
> > using woodoo...
> > create table #rules
> > (name varchar(50) ,
> > xyz varchar(200) )
> > insert #rules values ('3-28', '[Tstbits1], [Tstbits2],
> > [Tstbits3],[Diffbits]')
> > insert #rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> > EXECUTE Rac
> > ~a.~ + str2 + ~ - b.~ + str2 + ~, ~ +
> > ~a.~ + str3 + ~ - b.~ + str3 as [str]
> > from rac'
> > --Result---
> > str
> --------------------------------------------------------------------------
> --
> > ---------
> > a.[Diffbits] - b.[Diffbits], a.[Tstbits1] - b.[Tstbits1], a.[Tstbits2] -
> > b.[Tstbits2]
> > -oj
> > > create table rules
> > > (name varchar(50 ,
> > > xyz varchar(200) )
> > > insert rules values ('3-28', '[Tstbits1], [Tstbits2], [Tstbits3],
> > > [Diffbits]')
> > > insert rules values ('3-29', '[Tstbits11], [Tstbits12], [Diffbits1]')
> > > > Can you post the DDL and some sample data ?
> > > > --
> > > > -------------------------------------------
> > > > Hirantha S Hettiarachchi MCSD,MCDBA,OCP
> > > > Please reply only to the newsgroups.
> > > > > -- This is the data that it returns which is fine.
> > > > > [Tstbits1], [Tstbits2], [Tstbits3], [Diffbits]
> > > > > I would like the output to return this instead ofcourse with some
> > added
> > > > work
> > > > > .
> > > > > a.[Tstbits1]-b.[Tstbits1],a.[Tstbits2]-
> > > > > b.[Tstbits2],a.[Tstbits3]-b.[Tstbits3],a.[Diffbits] -b.[Diffbits]
> > > > > Whatever data gets outputted Id like to split it into sub values
> that
> > > are
> > > > > seperated by comman and see it as a.<subvalue1> - b.<subvalue1>,
and
> > so
> > > on
|
| Mon, 31 May 2004 21:40:29 GMT |
|
|
|
oj
#8 / 15
|
query help appreciated hey dinesh, hehehhe... when did you turn psychic? :)
-oj
Quote: > I once predicted that one day you will find the truth..still having doubts > with the voodoo man? :-) > Yours "truly",
> Dinesh.
|
| Tue, 01 Jun 2004 01:26:37 GMT |
|
|
|
Dinesh T
#9 / 15
|
query help appreciated No need to turn psychic when you come across replies, explicitly starting like "
using woodoo...
create table #rules
(name varchar(50) ,
xyz varchar(200) )
-----
----- "
hohohohoo :)
Dinesh.
Quote: > hey dinesh, > hehehhe... when did you turn psychic? :)
> -oj
> > I once predicted that one day you will find the truth..still having
doubts
> > with the voodoo man? :-)
> > Yours "truly",
> > Dinesh.
|
| Tue, 01 Jun 2004 01:45:26 GMT |
|
|
|
oj
#10 / 15
|
query help appreciated hehhehe... how are you doing?
-oj
Quote: > No need to turn psychic when you come across replies, explicitly starting > like > "
> using woodoo...
> create table #rules
> (name varchar(50) ,
> xyz varchar(200) )
> -----
> ----- "
> hohohohoo :)
> Dinesh.
> > hey dinesh,
> > hehehhe... when did you turn psychic? :)
> > -oj
> > > I once predicted that one day you will find the truth..still having
> doubts
> > > with the voodoo man? :-)
> > > Yours "truly",
> > > Dinesh.
|
| Tue, 01 Jun 2004 01:55:53 GMT |
|
|
|
Dinesh T
#11 / 15
|
query help appreciated doin okay...planning to take a vacation(read trying to escape from the snow :) what abt u?that proj. is over?
Dinesh.
Quote: > hehhehe... > how are you doing?
> -oj
> > No need to turn psychic when you come across replies, explicitly
starting
> > like
> > "
> > using woodoo...
> > create table #rules
> > (name varchar(50) ,
> > xyz varchar(200) )
> > -----
> > ----- "
> > hohohohoo :)
> > Dinesh.
> > > hey dinesh,
> > > hehehhe... when did you turn psychic? :)
> > > -oj
> > > > I once predicted that one day you will find the truth..still having
> > doubts
> > > > with the voodoo man? :-)
> > > > Yours "truly",
> > > > Dinesh.
|
| Tue, 01 Jun 2004 02:05:38 GMT |
|
|
|
oj
#12 / 15
|
query help appreciated kewl...i'll probably take a break after this proj to go to disney land. already purchased a 4-day package just haven't set the travel date yet. i'm not too bad. still another month left before i wrap it up. ready for a
change, though.
-oj
Quote: > doin okay...planning to take a vacation(read trying to escape from the snow > :) > what abt u?that proj. is over?
> Dinesh.
> > hehhehe...
> > how are you doing?
> > -oj
> > > No need to turn psychic when you come across replies, explicitly
> starting
> > > like
> > > "
> > > using woodoo...
> > > create table #rules
> > > (name varchar(50) ,
> > > xyz varchar(200) )
> > > -----
> > > ----- "
> > > hohohohoo :)
> > > Dinesh.
> > > > hey dinesh,
> > > > hehehhe... when did you turn psychic? :)
> > > > -oj
> > > > > I once predicted that one day you will find the truth..still
having
> > > doubts
> > > > > with the voodoo man? :-)
> > > > > Yours "truly",
> > > > > Dinesh.
|
| Tue, 01 Jun 2004 02:15:12 GMT |
|
|
|
kumarave
#13 / 15
|
query help appreciated FR, You can try this query . I think you can do it with UDF . HTH, Kumar
'a.' + substring(xyz, stringstart , (stringend-stringstart)+ 1 ) + '-b.'
+substring(xyz, stringstart , (stringend-stringstart)+ 1 ) + ','
from
(
select distinct stringstart , stringend , xyz from
(
select
charindex('[',A.xyz,b.id ) as stringstart ,
charindex(']',A.xyz,B.id) as stringend , xyz
from rules A ,
(
select
num6 + (num5* 2) + (num4*4) + (num3*8) + (num2*16) + (num1*32) as id
from
(
select 0 as num1 , 0 as num2 , 0 as num3
union all select 0,0,1
union all select 0,1,0
union all select 0,1,1
union all select 1,0,0
union all select 1,0,1
union all select 1,1,0
union all select 1,1,1
) as a
cross join
(
select 0 as num4 , 0 as num5 , 0 as num6
union all select 0,0,1
union all select 0,1,0
union all select 0,1,1
union all select 1,0,0
union all select 1,0,1
union all select 1,1,0
union all select 1,1,1
)as b
) as b
where name = '3-28'
) as c where
stringstart < stringend and stringstart <>0
)as d
|
| Mon, 14 Jun 2004 18:58:53 GMT |
|
|
|
kumarave
#14 / 15
|
query help appreciated FR, You can try this query . I think you can use UDF .
'a.' + substring(xyz, stringstart , (stringend-stringstart)+ 1 ) + '-b.'
+substring(xyz, stringstart , (stringend-stringstart)+ 1 ) + ','
from
(
select distinct stringstart , stringend , xyz from
(
select
charindex('[',A.xyz,b.id ) as stringstart ,
charindex(']',A.xyz,B.id) as stringend , xyz
from rules A ,
(
select
num6 + (num5* 2) + (num4*4) + (num3*8) + (num2*16) + (num1*32) as id
from
(
select 0 as num1 , 0 as num2 , 0 as num3
union all select 0,0,1
union all select 0,1,0
union all select 0,1,1
union all select 1,0,0
union all select 1,0,1
union all select 1,1,0
union all select 1,1,1
) as a
cross join
(
select 0 as num4 , 0 as num5 , 0 as num6
union all select 0,0,1
union all select 0,1,0
union all select 0,1,1
union all select 1,0,0
union all select 1,0,1
union all select 1,1,0
union all select 1,1,1
)as b
) as b
where name = '3-28'
) as c where
stringstart < stringend and stringstart <>0
)as d
HTH,
Kumar
|
| Mon, 14 Jun 2004 19:04:10 GMT |
|
|
|
kumarave
#15 / 15
|
query help appreciated FR, Here is a better query . create table num(id int)
begin
end
'a.' + substring(xyz, stringstart , (stringend-stringstart)+ 1 ) + '-b.'
+substring(xyz, stringstart , (stringend-stringstart)+ 1 ) + ','
from
(
select distinct stringstart , stringend,xyz from
(
select
charindex('[',A.xyz,b.id ) as stringstart ,
charindex(']',A.xyz,B.id) as stringend , xyz
from rules A , num b
where name = '3-28' and id =< len(xyz)
) as c where
stringstart < stringend and stringstart <>0
)as d
HTH,
Kumar
|
| Mon, 14 Jun 2004 20:56:58 GMT |
|
| |
|
