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 |
|
|
|