Select DISTINCT query in ASP Recordset

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?
PigdogmonsterAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
imitchieConnect With a Mentor Commented:

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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
PigdogmonsterAuthor Commented:
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
 
imitchieCommented:

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
 
imitchieCommented:
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
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as imitchie indicates, you have to give an alias name to the column to retrieve it with that name in your adodb recordset
0
 
PigdogmonsterAuthor Commented:
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
 
CCongdonCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.