?
Solved

Select DISTINCT query in ASP Recordset

Posted on 2007-11-15
9
Medium Priority
?
659 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

771 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