Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Select DISTINCT query in ASP Recordset

Posted on 2007-11-15
9
Medium Priority
?
670 Views
Last Modified: 2010-08-05
Hi - thanks for looking at my question :)

I am using a  very simple Select Distinct query in my ASP recordset.

There is another column in the table called Resource_Group that I need to access from my recordset

sqltext="Select DISTINCT(Resource_Owner) from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A'"


Is this possible?
0
Comment
Question by:Pigdogmonster
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20288114
in case you have 2 (or more) rows with the same resource_owner value, which row do you want to choose, in regards to the other column?

based on that, you will be able to use some technique to do so.
you can start using GROUP BY instead of distinct, so you can use aggregate functions:
sqltext="Select Resource_Owner, max(othercolumn) from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A' GROUP BY resource_owner"

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20288119
hmmm... code snipped does not like so long texts?  will report that...

and copy it again:

sqltext="Select Resource_Owner, max(othercolumn) 
 from MDM_REQUEST_RESOURCES 
 where Request_ID ='"&varRequest_ID&"' 
 AND Resource_Type=2 
 AND Approved='A' GROUP BY resource_owner" 

Open in new window

0
 

Author Comment

by:Pigdogmonster
ID: 20288179
Hi Angell,

I tried this..
sqltext16="Select Resource_Owner, max(Resource_Group)  from MDM_REQUEST_RESOURCES  where Request_ID ='"&varRequest_ID&"'  AND Resource_Type=2  AND Approved='A' GROUP BY resource_owner"

and I got the following error...

ADODB.Recordset error '800a0cc1'
Item cannot be found in the collection corresponding to the requested name or ordinal.
/Extracts/MDM/inc/EmailSub.asp, line 152


I'm not sure if I explained requirements properly :)
here is an example of the data in the DB..

Request_ID      Resource_Type    Resource_Group   Resource_Owner    Approved
123                   2                           101                         stuabuck                  A
123                   2                           102                         none                         A
123                   2                           105                         stuabuck                  A
123                   2                           108                         johnsmith                 A
123                   2                           109                         none                        A

The SQL statement is to send an email, but I only want to send 1 email to stuabuck rather than 2 so that is where the DISTINCT comes in but if the Resource_Owner is 'none' then I want to access the Resource_Group field.

So here is my full code (that does not work)...

set oRSceNT16=server.createobject("ADODB.recordset")
sqltext16="Select DISTINCT(Resource_Owner) from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A'"
oRSceNT16.open sqltext16, "dsn=MDM;uid=web_Admin;pwd=F1nnforest"
            
Do while NOT oRSceNT16.EOF

if oRSceNT16("Resource_Owner")="None" then
varLUGrp = oRSceNT16("Resource_Group")
else
varLUUser = oRSceNT16("Resource_Owner")
end if


oRSceNT16.movenext
loop




I hope this is better :)

Thanks

PDM
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 25

Expert Comment

by:imitchie
ID: 20288270

sqltext16="
 select sorter, Resource_Owner, Max(Resource_Group) as Resource_Group from (
 select Resource_Owner, case when Resource_Owner = "none" then NewID() else 1 end as sorter,
 case when Resource_Owner = 'none' then Resource_Group else null end as Resource_Group
 from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A'
 ) innerq
 group by sorter, Resource_Owner
"

Open in new window

0
 
LVL 25

Expert Comment

by:imitchie
ID: 20288276
hmm... code snippet is good at highliting typos...

sqltext16="
 select sorter, Resource_Owner, Max(Resource_Group) as Resource_Group from (
 select Resource_Owner, case when Resource_Owner = 'none' then NewID() else 1 end as sorter,
 case when Resource_Owner = 'none' then Resource_Group else null end as Resource_Group
 from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A'
 ) innerq
 group by sorter, Resource_Owner
"

Open in new window

0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20288283
as imitchie indicates, you have to give an alias name to the column to retrieve it with that name in your adodb recordset
0
 

Author Comment

by:Pigdogmonster
ID: 20288297
hey, thats one complex query (well for me anyway :))

I get this error..

SubForApprovalGB
Microsoft OLE DB Provider for ODBC Drivers error '80040e07'

[Microsoft][ODBC SQL Server Driver][SQL Server]Operand type clash: uniqueidentifier is incompatible with int

/Extracts/MDM/inc/EmailSub.asp, line 160
0
 
LVL 25

Accepted Solution

by:
imitchie earned 200 total points
ID: 20288298

oops./. fixed
sqltext16="
 select sorter, Resource_Owner, Max(Resource_Group) as Resource_Group from (
 select Resource_Owner, case when Resource_Owner = 'none' then NewID() else null end as sorter,
 case when Resource_Owner = 'none' then Resource_Group else null end as Resource_Group
 from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A'
 ) innerq
 group by sorter, Resource_Owner
"

Open in new window

0
 
LVL 9

Expert Comment

by:CCongdon
ID: 20322502
You could expand your original query to:

sqltext="Select DISTINCT Resource_Owner, Resource_Group from MDM_REQUEST_RESOURCES where Request_ID ='"&varRequest_ID&"' AND Resource_Type=2 AND Approved='A'"

With the data you gave:

Request_ID      Resource_Type    Resource_Group   Resource_Owner    Approved
123                   2                           101                         stuabuck                  A
123                   2                           102                         none                         A
123                   2                           105                         stuabuck                  A
123                   2                           108                         johnsmith                 A
123                   2                           109                         none                        A

You would get all records returned. The distinct in this SQL statement is set to catch unique combinations of the two columsn. So if you have two records that both said 101 stuabuck, it will only return one row with that data.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question