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.booking_form.COLL_POST_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.booking_form.COLL_POST_CODE) AS CountOfCOLL_POST_CODE,
COLL_POST_CODE FROM dbo.booking_form GROUP BY COLL_POST_CODE ORDER BY
CountOfCOLL_POST_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.public.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.booking_form.COLL_POST_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.booking_form.COLL_POST_CODE) AS
CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_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.write SQL &"<hr>"
set mDATA=CONNECT.Execute(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

 

[Simon Gare]

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

<%
Dim AreaColl
Dim AreaColl_numRows

Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING
AreaColl.Source = "SELECT (Left(Count(dbo.booking_form.COLL_POST_CODE),3))
AS CountOfCOLL_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()

AreaColl_numRows = 0
%>

and in the body


<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%></td>
<td width="790"
class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>

Thanks in advance

 

[Evertjan.]

Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:

[Please do not toppost on usenet]

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

Set AreaColl = Server.CreateObject("ADODB.Recordset")

<%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").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?

 

[Mark McGinty]

Simon Gare wrote on 05 jan 2007 in
microsoft.public.inetserver.asp.general:

[Please do not toppost on usenet]

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

Set AreaColl = Server.CreateObject("ADODB.Recordset")

<%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").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

 

[Mark McGinty]

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

What is the problem?

[more comments inline...]

<%
Dim AreaColl
Dim AreaColl_numRows

Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING

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.COLL_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_form 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_POST_CODE, COLL_POST_CODE FROM dbo.booking_form GROUP BY
COLL_POST_CODE ORDER BY CountOfCOLL_POST_CODE DESC"
AreaColl.CursorType = 0
AreaColl.CursorLocation = 2
AreaColl.LockType = 1
AreaColl.Open()

AreaColl_numRows = 0
%>

and in the body


<td colspan=2>Top 10 collection post codes</td>
</tr>
<%
While ((Repeat1__numRows <> 0) AND (NOT AreaColl.EOF))
%>
<tr>
<td width="100"
class="DataSetText"><%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%></td>
<td width="790"
class="DataSetText"><%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)
%></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
AreaColl.MoveNext()
Wend
%>

Thanks in advance

 

[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"
[/QUOTE]

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

 

[Evertjan.]

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.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?

 

[Mark McGinty]

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.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.]

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.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.

 

[Simon Gare]

Hi Mark,

thanks for that works perfectly in the rs test but how do I display the data
on the page?

before it was
<%=(AreaColl.Fields.Item("COLL_POST_CODE").Value)%>

and

<%=(AreaColl.Fields.Item("CountOfCOLL_POST_CODE").Value)%>

now there is no listing in the rsAreaColl

Any ideas?

Regards
Simon

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

suggest.

What is the problem?

[more comments inline...]

<%
Dim AreaColl
Dim AreaColl_numRows

Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING

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.COLL_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_form 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

 

[Simon Gare]

Thanks guys just solved it

AreaColl.Source = "SELECT COUNT(*)AS COUNT, LEFT(COLL_POST_CODE, 3) AS PC
FROM dbo.booking_form GROUP BY LEFT(COLL_POST_CODE, 3) ORDER BY COUNT(*)
DESC"

Thanks for all your help its been driving me mad for 2 days.

Regards
Simon

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

suggest.

What is the problem?

[more comments inline...]

<%
Dim AreaColl
Dim AreaColl_numRows

Set AreaColl = Server.CreateObject("ADODB.Recordset")
AreaColl.ActiveConnection = MM_TobiasNET_STRING

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.COLL_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_form 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

 

[Mark McGinty]

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.general:

Mark McGinty wrote on 06 jan 2007 in
microsoft.public.inetserver.asp.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.

ADODB.Connection.Execute returns an object of type ADODB.Recordset.

var cn = new ActiveXObject("ADODB.Connection");
cn.Open("Provider=[...]");
var obj = cn.Execute("SELECT [...]");

In the example above, "obj" is, in fact, a recordset.

Point being that whether or not you explicitly create a recordset is
inconsequential. Lack of explicit creation does not mean that you never use
recordset, rather, it means that you use it [apparently] without knowing
what you have used.


-Mark

 

[Evertjan.]

Mark McGinty wrote on 07 jan 2007 in
microsoft.public.inetserver.asp.general:

ADODB.Connection.Execute returns an object of type ADODB.Recordset.

var cn = new ActiveXObject("ADODB.Connection");
cn.Open("Provider=[...]");
var obj = cn.Execute("SELECT [...]");

In the example above, "obj" is, in fact, a recordset.

What's in a name? ;-)

Point being that whether or not you explicitly create a recordset is
inconsequential. Lack of explicit creation does not mean that you
never use recordset, rather, it means that you use it [apparently]
without knowing what you have used.

This gets interesting.

Why do all these people declare/create recordsets
if it is inconsequential?

Do they get additional benefits?

I never felt the need sofar, Mark.

 

[Bob Barrows [MVP]]

This gets interesting.

Why do all these people declare/create recordsets
if it is inconsequential?

1. That's how many of the online samples they've seen show it to be done
or,
2. They need a non-default cursor type

Do they get additional benefits?

Sure, they gain the ability to set cursor properties before opening it.
Granted, if all you need is a default server-side forward-only cursor, and
you are planning to use the Execute() method anyways, then it is, indeed, a
waste of time to instantiate a recordset object.

 

[Mark McGinty]

Mark McGinty wrote on 07 jan 2007 in
microsoft.public.inetserver.asp.general:

ADODB.Connection.Execute returns an object of type ADODB.Recordset.

var cn = new ActiveXObject("ADODB.Connection");
cn.Open("Provider=[...]");
var obj = cn.Execute("SELECT [...]");

In the example above, "obj" is, in fact, a recordset.

What's in a name? ;-)

Point being that whether or not you explicitly create a recordset is
inconsequential. Lack of explicit creation does not mean that you
never use recordset, rather, it means that you use it [apparently]
without knowing what you have used.

This gets interesting.

Why do all these people declare/create recordsets
if it is inconsequential?

What I meant was, it's inconsequential in determining whether or not
recordset is used by any given code. It can be returned by other objects.

Do they get additional benefits?

I never felt the need sofar, Mark.

An explicitly created recordset has more cursor and lock option
possibilities than does the default recordset returned by
Connection.Execute -- which is just a "firehose" (forward-only, read-only.)

For example, if you need to traverse the recordset more than once, and/or
call MovePrevious/MoveFirst/MoveLast, you'd need to create an explicit
recordset, connect it, and open it with appropriate parameters to make it
capable of bidirectional scrolling. Another reason would be to open a
persisted recordset from XML, or some other stream.

If you use GetString and/or GetRows a lot, you might not ever miss
explicitly creating recordsets (even though you are still most definitely
using them.)


-Mark