Solved

Subquery in coldfusion

Posted on 2014-03-24
4
610 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
  • 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 500 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

The New “Normal” in Modern Enterprise Operations

DevOps for the modern enterprise offers many benefits — increased agility, productivity, and more, but digital transformation isn’t easy, especially if you’re not addressing the right issues. Register for the webinar to dive into the “new normal” for enterprise modern ops.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Save JavaScript generated bar code to folder on web server, ColdFusion and Javascript 4 170
ColdFusion MS SQL CSV Impport 10 68
Detect and combat possible robot 7 111
REGEX HELP 11 48
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

840 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