query help appreciated 
Author Message
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 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
 
 [ 15 post ] 

 Relevant Pages 

1. query help appreciated

2. query help appreciated. max()/joins/subquery

3. Query Challenge - Help Appreciated

4. Simple Contains Query Question (any help appreciated)

5. slow query -- help much appreciated

6. Complex query, some help would be appreciated

7. Help for query interface design would be highly appreciated

8. Ado Help Please Help any help is appreciated

9. sql query - comments appreciated

10. IMMEDIATE help appreciated

11. Trigger Question, any help would be appreciated


 
Powered by phpBB® Forum Software