Solved

Select DISTINCT query in ASP Recordset

Posted on 2007-11-15
9
658 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

695 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