Newbie need help with a query

[Zoom1177]

I've got a table, here is an example

expertise excode
=================================
Building types 01000000
education 01010000
k-12 01020000
construction 02000000
substructure 02010000
foundations 02020000

i would like to pull a dynamic menu from the last table to put it on my ASP. Of course the table has more records.

Now the final results should be categorize according to each category code
so Building types: should have under neath it (education and K-12) as they both belong to the 01 code

Construction should have under neath it (substructure and foundations) as both belong to the 02

It has to be dynamic (loop with variable not numbers), because the categories will increase in the future.

any ideas?
Thanks in advance

 

[r937]

select expertise
     , excode
  from daTable
order
    by excode

r937.com | rudy.ca

 

[Zoom1177]

thank for replying

i know i am not clear about what i want it's a little complicated than that.

i need more than one view or SP that select all the categories that their code is only on this patern:

01000000
02000000
03000000
....etc

i want to select those alone in a query

then another view that select all the categories that belogn to the first number 01000000
those would be like this
01010000
01020000
...etc

then another view that select all those who belongs to the second number 02000000 and so on.

so i need several views or SPs to select head categories and subcategories

any ideas?

 

[r937]

suppose you did have several views -- what would you do with them? how would you use them? it sounds like you want to use loops... why?

why can't you simply return everything in one query?

r937.com | rudy.ca

 

[gmmastros]

To get the main categories...

[tt][blue]
select expertise, excode
from daTable
Where Convert(int, excode) >= 1000000
order by excode
[/blue][/tt]

To get a subcategory...
[tt][blue]
select expertise, excode
from daTable
Where Left(exCode, 2) = '01'
order by excode
[/blue][/tt]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Zoom1177]

because as i said in the first post, i want those sets of data to put them in a dynamic (tree menu) on my ASPs

they have to be categoriezed so i can put them in different nodes on the tree

thanks for your help

any ideas out there are very appreciated

 

[bborissov]

I am not so sure what you trying to do. If you post simple data and desired result it would be easier for me to understand.
Try this:

--- If you want ALL record from 
--- 01 group
SELECT * FROM MyTable WHERE LEFT(excode,2) = '01'

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

 

[Zoom1177]

Thanks again

right now on my tables i have 5 categories
01
02
03
04
05

but in the future this category might expand

so i could've done the "where" clause = constants ('o1') as some of you mentioned

but in the future i'll have to write more views as they add more categories???

My boss want everything automated

 

[SQLBill]

Let's start with our terminology. In SQL Server programming the word VIEW has a specific meaning. You can think of a VIEW as a virtual table that is made up of links to one or more columns in one or more tables. Is that what you want? Or do you want a query to DISPLAY the results that you are looking for?

-SQLBill

Posting advice: FAQ481-4875

 

[Zoom1177]

yes i am looking for a query that either display the result i am looking for or even store it in a table if that would make pulling those data faster on the web page.

i appreciate your hell all my friends.

To make things easy a bit

i got a query that gave me a result close to what i want (i'll put the code later if someone want's it)

which is this:

expertise excode
========================
education 01010000
healthcare 01020000
industrial 01030000
office building 01040000
substructure 02010000
foundations 02020000
pro exe 03010000
superstruc 03020000
shell1 04010000
interiors2 04020000
shell 05010000
interiors 05020000


NOW i want to get group of results....my results should give me the Expertise column grouped by the code in the excode column HOWEVER........

i dont know how much more pattern in the table or in other words we could have in the future 06 and 07 so i can't write in the where clause something like this [where left (excode,2)='02'

I know T-SQL is not like other languages but i was wonderinf if there is a way i can loop through them or group them somehow...

====
here is another question and forgive me as i am new to T-SQL and thinking with other languages in mind

I want to write something like this

Delcare @var1 int
set @var1 = 1

while @var1 <10
beging
select *
from tablname
where excode = 0 + @var1 + 000000
@var1=@var1+1
end
else
break

or something like that....the main point i would like to put a variable in the middle of a field when i query that field in the where clause.

Sorry if i am very confusing
I appreciate your help all
Thanks

 

[egodette]

maybe something like this?

answer='test'

Query = select.... from... where excode= 1 &test& 000000

 

[Zoom1177]

Thanks but it didn't work

it says in the help that this character & is a bitwise logical AND between two integer values

my excode field 01000000 is nvarchar in the table.

Thanks anyway.

 

[SQLBill]

You could convert the values to strings, concatenate them, then convert them back.

So declare @var1 as a varchar(2), then:

where excode = CAST((CAST(0 AS VARCHAR(1)) + @var1 + CAST(000000 AS VARCHAR(6)))AS INT)

-SQLBill

Posting advice: FAQ481-4875

 

[Zoom1177]

Sorry for the delay in reply

nope that didn't work

it didnt' give me an error but no result either

 

[bborissov]

How many category you can have? You can't have more than 99, haven't you? If the first two digits define your category you could make a stored procedure and pass category number as parameter. Then get all records for that categiry:

CREATE PROCEDURE GetCategory
(
@iCategory as integer
)
AS
BEGIN
   IF @iCategory < 0 OR @iCategory > 99
      BEGIN
          --- Error
          RETURN
      END
   SET NOCOUNT ON
   SELECT * FROM SELECT * FROM MyTable WHERE LEFT(excode,2) = RIGHT('0'+CAST(@iCategory as varchar(2)),2)
END

or if you want you could pass that parameter as char so you didn't have to CAST it in SELECT:

CREATE PROCEDURE GetCategory
(
@iCategory as varchar(2)
)
AS
BEGIN
   IF CAST(@iCategory as int) < 0 OR
      CAST(@iCategory as int) > 99
      BEGIN
          --- Error
          RETURN
      END
   SET NOCOUNT ON
   SELECT * FROM SELECT * FROM MyTable WHERE LEFT(excode,2) = RIGHT('0'+@iCategory,2)
END

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

 

[Zoom1177]

Looks great and good idea I'll try it and let you know.

I didn't try it yet but i might have to make more than SP for each node on the tree menu i want to make and puting 99 might make it really slow...i'll have to try and tell you or actually i might have to reduce the number to 30 or 40 in the looping for the outer nodes and keep it 99 for the inside nodes of the tree

Thank you very much and i'll get back to you as soon as i try it.

Have a great weekend all.

 

[ESquared]

I bet you $1 that the first query in this thread is the best answer, and you should figure out how to use that in your ASP... have you asked in the ASP forum for help with this?

 

[r937]

I bet you $1 that the first query in this thread is the best answer

thanks, E², i agree with you!!





r937.com | rudy.ca

 

[earthandfire]

[COLOR=green]--create a test table[/color]
declare @tbl table(expertise varchar(100), excode varchar(100))

[COLOR=green]--populate the table[/color]
insert into @tbl (expertise, excode)
select 'education', '01010000'
union select 'healthcare', '01020000'
union select 'industrial', '01030000'
union select 'office building', '01040000'
union select 'substructure', '02010000'
union select 'foundations', '02020000'
union select 'pro exe', '03010000'
union select 'superstruc', '03020000'
union select 'shell1', '04010000'
union select 'interiors2', '04020000'
union select 'shell', '05010000'
union select 'interiors', '05020000'

[COLOR=green]--return all the data ordered as per your requirements
--(as shown in r937's original reply)[/color]
select 
	expertise, excode 
from 
	@tbl 
order by 
	excode

[COLOR=green]--alternatively, if you really want to use a loop
--then it would make sense to have it in your calling code

--a loop here would surely return the same data as the code above

--to facilitate a loop in your calling code the following two sections 
--would need to be converted into stored procedures.

--this could be converted into a stored procedure
--to return the maximum category code[/color]
select
	max(left(excode, 2)) Category
from
	@tbl

[COLOR=green]--this could be converted into a stored procedure
--to return all records for a single category
--with the category code passed in as a parameter[/color]

declare @category varchar(2)
set @category = '01'
select 
	expertise, excode
from
	@tbl
where 
	@category = left(excode, 2)
order by
	excode

 

[Zoom1177]

Thanks for your help all

I know i didn't make myself clear enough but it is a bit complicated

this is what i want to do as a tree menu

http://usera.imagecave.com/me72/categories.JPG

all those categories are stored in ONE table each have different codes. However, in the Active Server Page i have a menu as you saw in the picture each category (marked with different colors) must be in each OWN seperate result set (view, stored procedure, temp table...etc)

Because it's the only way i can assign specific categories to parent nodes on the tree..

I found a way to pull the RED colored category alone (in it's own view) it's not what my boss wants but i must move forward

and that is

select *
from xdmaster
where excode like '0[1-2-3-4-5-6-7-8-9-10-11-12-13-14-15]000000'

I dont need to order the result comes up ordered automaticaly

and here is the result (which is marked in red in the previous picture)

Building types 01000000
Specialized Expertise 02000000
Process Expertise 03000000
Product Expertise 04000000
Construction Systems 05000000


this is as far as i went, it's limited only to the number 015 as you saw in the previous select statement. Now i got to write something else for each category...it's a mess

But thanks all for your help