SQL Select Query help

[Simon Gare]

Hi,

trying to retrieve postal codes from the db but only want the query to look
at the first 3 digits of the code tried using
(LEFT(dbo.booki ng_form.COLL_PO ST_CODE),3) but that doesn't work. I don't
want the query to count individual post codes but instead look at an area
found in the first 3 digits e.g. HA0 3TD is for a particular house but HA)
is for the area Harrow.
"SELECT Count(dbo.booki ng_form.COLL_PO ST_CODE) AS CountOfCOLL_POS T_CODE,
COLL_POST_CODE FROM dbo.booking_for m GROUP BY COLL_POST_CODE ORDER BY
CountOfCOLL_POS T_CODE DESC"

Regards
Simon Gare
The Gare Group Limited

website: www.thegaregroup.co.uk
website: www.privatehiresolutions.co.uk

[Evertjan.]

Simon Gare wrote on 05 jan 2007 in
microsoft.publi c.inetserver.as p.general:

Hi,

trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booki ng_form.COLL_PO ST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booki ng_form.COLL_PO ST_CODE) AS
CountOfCOLL_POS T_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POS T_CODE DESC"

You should mention the db-engine used for a correct answer.

I use this with the Jet engine:

SQL = "SELECT left(postcode,3 ) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3 )"

'''response.wri te SQL &"<hr>"
set mDATA=CONNECT.E xecute(SQL)

Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td> <b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Simon Gare]

Thanks Evertjan, having a problem though could you look below and suggest.

<%
Dim AreaColl
Dim AreaColl_numRow s

Set AreaColl = Server.CreateOb ject("ADODB.Rec ordset")
AreaColl.Active Connection = MM_TobiasNET_ST RING
AreaColl.Source = "SELECT (Left(Count(dbo .booking_form.C OLL_POST_CODE), 3))
AS CountOfCOLL_POS T_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POS T_CODE DESC"
AreaColl.Cursor Type = 0
AreaColl.Cursor Location = 2
AreaColl.LockTy pe = 1
AreaColl.Open()

AreaColl_numRow s = 0
%>

and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numR ows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetT ext"><%=(AreaCo ll.Fields.Item( "COLL_POST_CODE ").Value)%> </td>
<td width="790"
class="DataSetT ext"><%=(AreaCo ll.Fields.Item( "CountOfCOLL_PO ST_CODE").Value )
%></td>
</tr>
<%
Repeat1__index= Repeat1__index+ 1
Repeat1__numRow s=Repeat1__numR ows-1
AreaColl.MoveNe xt()
Wend
%>

Thanks in advance
"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@19 4.109.133.242.. .

Simon Gare wrote on 05 jan 2007 in
microsoft.publi c.inetserver.as p.general:

Hi,

trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booki ng_form.COLL_PO ST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booki ng_form.COLL_PO ST_CODE) AS
CountOfCOLL_POS T_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POS T_CODE DESC"

You should mention the db-engine used for a correct answer.

I use this with the Jet engine:

SQL = "SELECT left(postcode,3 ) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3 )"

'''response.wri te SQL &"<hr>"
set mDATA=CONNECT.E xecute(SQL)

Response.Write "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td> <b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Write "</table>" & vbcrlf

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Evertjan.]

Simon Gare wrote on 05 jan 2007 in
microsoft.publi c.inetserver.as p.general:

"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@19 4.109.133.242.. .

>Simon Gare wrote on 05 jan 2007 in
microsoft.publ ic.inetserver.a sp.general:

SQL = "SELECT left(postcode,3 ) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3 )"

[Please do not toppost on usenet]

Thanks Evertjan, having a problem though could you look below and
suggest.

Set AreaColl = Server.CreateOb ject("ADODB.Rec ordset")

<%=(AreaColl.Fi elds.Item("Coun tOfCOLL_POST_CO DE").Value)%>

I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those long
names with multiple _'s and unnecessary ()'s.

.... having a problem though could you look below and
suggest.

If you could test your code yourself, starting with the smallest and most
readable code that gives a problem, perhaps you could even come up with
explaining the kind of problem you have, Simon.

btw, did my code work with you?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Mark McGinty]

"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ***********@194 .109.133.242...

Simon Gare wrote on 05 jan 2007 in
microsoft.publi c.inetserver.as p.general:

>"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******* *************@1 94.109.133.242. ..

>>Simon Gare wrote on 05 jan 2007 in
microsoft.pub lic.inetserver. asp.general:

SQL = "SELECT left(postcode,3 ) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3 )"

[Please do not toppost on usenet]

>Thanks Evertjan, having a problem though could you look below and
suggest.

Set AreaColl = Server.CreateOb ject("ADODB.Rec ordset")

><%=(AreaColl.F ields.Item("Cou ntOfCOLL_POST_C ODE").Value)%>

I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those
long
names with multiple _'s and unnecessary ()'s.

What do you use instead?
-Mark

>.... having a problem though could you look below and
suggest.

If you could test your code yourself, starting with the smallest and most
readable code that gives a problem, perhaps you could even come up with
explaining the kind of problem you have, Simon.

btw, did my code work with you?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Mark McGinty]

"Simon Gare" <sg@simongare.c omwrote in message
news:%2******** ********@TK2MSF TNGP04.phx.gbl. ..

Thanks Evertjan, having a problem though could you look below and suggest.

What is the problem?

[more comments inline...]

<%
Dim AreaColl
Dim AreaColl_numRow s

Set AreaColl = Server.CreateOb ject("ADODB.Rec ordset")
AreaColl.Active Connection = MM_TobiasNET_ST RING

You should create an explicit connection object, rather than relying on ADO
to create one for you implicitly.

AreaColl.Source = "SELECT (Left(Count(dbo .booking_form.C OLL_POST_CODE), 3))

I doubt this is legal, LEFT expects varchar or text, COUNT returns int. I
think what you want is:

SELECT COUNT(*), LEFT(COLL_POST_ CODE, 3) FROM dbo.booking_for m GROUP BY
LEFT(COLL_POST_ CODE, 3) ORDER BY COUNT(*) DESC

You might want to consider defining a computed column for the left 3 of the
postal code, for both ease of reference and db server efficiency.
-Mark

AS CountOfCOLL_POS T_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POS T_CODE DESC"
AreaColl.Cursor Type = 0
AreaColl.Cursor Location = 2
AreaColl.LockTy pe = 1
AreaColl.Open()

AreaColl_numRow s = 0
%>

and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numR ows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetT ext"><%=(AreaCo ll.Fields.Item( "COLL_POST_CODE ").Value)%> </td>
<td width="790"
class="DataSetT ext"><%=(AreaCo ll.Fields.Item( "CountOfCOLL_PO ST_CODE").Value )
%></td>
</tr>
<%
Repeat1__index= Repeat1__index+ 1
Repeat1__numRow s=Repeat1__numR ows-1
AreaColl.MoveNe xt()
Wend
%>

Thanks in advance
"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@19 4.109.133.242.. .

>Simon Gare wrote on 05 jan 2007 in
microsoft.publ ic.inetserver.a sp.general:

Hi,

trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.booki ng_form.COLL_PO ST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booki ng_form.COLL_PO ST_CODE) AS
CountOfCOLL_POS T_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POS T_CODE DESC"

You should mention the db-engine used for a correct answer.

I use this with the Jet engine:

SQL = "SELECT left(postcode,3 ) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3 )"

'''response.wr ite SQL &"<hr>"
set mDATA=CONNECT.E xecute(SQL)

Response.Wri te "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td> <b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Wri te "</table>" & vbcrlf

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Mike Brind]

Good idea. For a direct marketing app, I created a column which took the
first 2 letters of the postcode, which made more localised selections
easier. For a higher level of granularity, I would suggest all those
characters to the left of the space. The first 3 won't always work. BS2 is
in the centre of Bristol and BS21 is Clevedon - over 20 miles away for
instance, but would both be included in a search for Left(PostCode,3 ) =
"BS2"

--
Mike Brind

"Mark McGinty" <mm******@spamf romyou.comwrote in message
news:ei******** ******@TK2MSFTN GP02.phx.gbl...

>

>
You might want to consider defining a computed column for the left 3 of
the postal code, for both ease of reference and db server efficiency.
-Mark

>AS CountOfCOLL_POS T_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CO DE ORDER BY CountOfCOLL_POS T_CODE DESC"
AreaColl.Curso rType = 0
AreaColl.Curso rLocation = 2
AreaColl.LockT ype = 1
AreaColl.Open( )

AreaColl_numRo ws = 0
%>

and in the body
<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numR ows <0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSet Text"><%=(AreaC oll.Fields.Item ("COLL_POST_COD E").Value)%> </td>
<td width="790"
class="DataSet Text"><%=(AreaC oll.Fields.Item ("CountOfCOLL_P OST_CODE").Valu e)
%></td>
</tr>
<%
Repeat1__index= Repeat1__index+ 1
Repeat1__numRow s=Repeat1__numR ows-1
AreaColl.MoveNe xt()
Wend
%>

Thanks in advance
"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******* *************@1 94.109.133.242. ..

>>Simon Gare wrote on 05 jan 2007 in
microsoft.pub lic.inetserver. asp.general:

Hi,

trying to retrieve postal codes from the db but only want the query to
look at the first 3 digits of the code tried using
(LEFT(dbo.book ing_form.COLL_P OST_CODE),3) but that doesn't work. I
don't want the query to count individual post codes but instead look
at an area found in the first 3 digits e.g. HA0 3TD is for a
particular house but HA) is for the area Harrow.
"SELECT Count(dbo.booki ng_form.COLL_PO ST_CODE) AS
CountOfCOLL_PO ST_CODE, COLL_POST_CODE FROM dbo.booking_for m GROUP BY
COLL_POST_CO DE ORDER BY CountOfCOLL_POS T_CODE DESC"

You should mention the db-engine used for a correct answer.

I use this with the Jet engine:

SQL = "SELECT left(postcode,3 ) as pc,count(pc) as tal" &_
" FROM myTbl GROUP BY left(postcode,3 )"

'''response.w rite SQL &"<hr>"
set mDATA=CONNECT.E xecute(SQL)

Response.Writ e "<table border=1><tr>" & vbcrlf
Do Until mDATA.Eof
tal = mDATA("tal")
pc = mDATA("pc")
if pc="" then pc="No postcode: " else pc="Postcode: " & pc & ": "
Response.Write "<td>"&pc&"<td> <b>"&tal&"</b><tr>" & vbcrlf
mDATA.MoveNext
Loop
Response.Writ e "</table>" & vbcrlf

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Evertjan.]

Mark McGinty wrote on 06 jan 2007 in
microsoft.publi c.inetserver.as p.general:

>I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those
long
names with multiple _'s and unnecessary ()'s.

What do you use instead?

Of what?

The multiple _'s or unnecessary ()'s?

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Mark McGinty]

"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@19 4.109.133.242.. .

Mark McGinty wrote on 06 jan 2007 in
microsoft.publi c.inetserver.as p.general:

>>I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those
long
names with multiple _'s and unnecessary ()'s.

What do you use instead?

Of what?

The multiple _'s or unnecessary ()'s?

Instead of Recordset.
-Mark

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Evertjan.]

Mark McGinty wrote on 06 jan 2007 in
microsoft.publi c.inetserver.as p.general:

>
"Evertjan." <ex************ **@interxnl.net wrote in message
news:Xn******** ************@19 4.109.133.242.. .

>Mark McGinty wrote on 06 jan 2007 in
microsoft.publ ic.inetserver.a sp.general:

>>>I never use a Recordset [you can easily do without it]
and the code you show is much to complex for me to read with all those
long names with multiple _'s and unnecessary ()'s.

What do you use instead?

Of what?

The multiple _'s or unnecessary ()'s?

Instead of Recordset.

Ah, that's what you mean. [I would never have guessed]

Well nothing.

The execute() command already gives me the info I nead when reading with
sql SELECT, and with the UPDATE and INSERT SQL string it does a good job
for writing to the db.
--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)