Solved

Select DISTINCT query in ASP Recordset

Posted on 2007-11-15
9
652 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 142

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 142

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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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 142

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 50 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
sql query 7 37
How many Lat/Long for a single UK Postcode 5 12
BULK INSERT most recent CSV 19 21
sql query Help 12 34
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

746 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now