Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Subquery in coldfusion

Posted on 2014-03-24
4
Medium Priority
?
649 Views
Last Modified: 2014-03-25
Hi,

can I do a sub query in ColdFusion?

How can I accomplish a sub query result.

<cfquery name = "QSearchResultRecord"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , T.TicketNumber
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , C.Category
   FROM LogEntry L
     LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
     LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
     LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
  <cfif len(FoundIssueIDs) eq 0 >
   where 1 = 2 
  <cfelse>
   where L.IssueID in  (#FoundIssueIDs#)
  </cfif>
  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 
</cfquery>

 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="rcr">

select * from QSearchResultRecord

</cfquery>

Open in new window

0
Comment
Question by:lulu50
[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
  • 2
  • 2
4 Comments
 
LVL 39

Expert Comment

by:gdemaria
ID: 39950945
What is it you want to do?  

You can have a subquery in your select or your join or in your where clause..

(see bold text as examples)

SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
             end  as TopIssueID
             , L.IssueID
             , L.ParentIssueID
             , T.TicketNumber
             , L.TitleOfIssue
             , I.Status
             , L.DateCreated
         , C.Category
       , (select count(*) from xxxx where issueId = xxx) as CountRelated
  FROM LogEntry L
     LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
     LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
     LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
    LEFT JOIN (select ONe,  Two, Three
             from aTable) sub on sub.issueId = l.issueID
0
 

Author Comment

by:lulu50
ID: 39950965
Hi gdemaria,

Question:

It has been a long time since I have worked with coldfusion. but I know that I did it before
where I have a query and I can create a subquery to select from the first query.

Something like this:


<cfquery name = "QSearchResultRecord"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="r">
  SELECT case when L.ParentIssueID is null then L.IssueId
         else L.ParentIssueID
		 end  as TopIssueID
		 , L.IssueID
		 , L.ParentIssueID
		 , T.TicketNumber
		 , L.TitleOfIssue
		 , I.Status
		 , L.DateCreated
         , C.Category
   FROM LogEntry L
     LEFT JOIN IssueStatus I on I.StatusID = L.StatusID
     LEFT JOIN CategoryType C on C.CategoryTypeID = L.CategoryTypeID
     LEFT JOIN TicketNumber T on T.TicketNumberID = L.TicketNumberID
  <cfif len(FoundIssueIDs) eq 0 >
   where 1 = 2 
  <cfelse>
   where L.IssueID in  (#FoundIssueIDs#)
  </cfif>
  order by 1 desc, case when L.parentIssueID is null then 1 else 2 end 
</cfquery>

Open in new window


 <cfquery name = "QSearchResult"  datasource='#strDSN#' username='#strUID#' password='#strPWD#' result="rcr">

select * from QSearchResultRecord

</cfquery>

Open in new window

0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 39951282
Perhaps you are looking for a "query of query"

This allows you to use the first query as the "table" in the another query.



<cfquery name="getSubSet" dbtype="query">
   select * from QSearchResultRecord   <!=== =the table here is the query name
   where IssueID between 1 and 100
</cfquery>
0
 

Author Closing Comment

by:lulu50
ID: 39953092
Thank you
0

Featured Post

Not sure which OpenStack Certification to get?

So you’ve realized you might want to get certified in OpenStack, but you’re not sure what the benefits might be or even which one you should take. You know there are several certification courses you can choose from, but how do you know which one is right for you?

Question has a verified solution.

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

Hi, I will be creating today a basic tutorial on how we can create a Mail Custom Function and use it where ever we want. The main advantage about creating a custom function is that we can accommodate a range of arguments to pass to the Function and …
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

721 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