[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 290
  • Last Modified:

How to select from one table if a condition is met and another if the condition is not met.

I would like to simplify a process.  The query below returns 't_content.paragraph' and 't_content.customTemplateID'.  If 't_content.customTemplateID' contains an ID that has an associate record in the 't_customTemplate' table, which has a primary key named 't_customTemplate.customTemplateID', I then run another query that returns 't_customTemplate.templateScript' from 't_customTemplate'.  How do I write a query to return a query variable named 'areaContent' (select 'x' as areaContent), where the value of the query variable is that of 't_content.paragraph', unless 't_content.customTemplateID' refers to a valid record within the 't_customTemplate' table, in which case I would want the value to be that of 't_customTemplate.templateScript'.

The query below returns three records.  The query I would like to learn to write would contain three records as well.  Each record would consist of only one column (areaContent), which contain content based off of the logic explained above.   Please let me know if you have any questions.  Hopefully I did well enough in explaining what it is I am after.  Thanks in advance!

<cfquery name="elementHTML" datasource="#request.dsn#">
	SELECT	paragraph,customTemplateId
	FROM	t_content
	WHERE	contentID IN (311,183,319)
</cfquery>

Open in new window

0
brianmfalls
Asked:
brianmfalls
  • 7
  • 4
  • 4
3 Solutions
 
brianmfallsAuthor Commented:
One more thing.....  I need the records to return in the order in which the contentID's are presented in within the SQL 'IN' statement.  Otherwise, I'll have issues...  Thanks again.
0
 
_agx_Commented:
Use an outer join

SELECT      c.paragraph, c.customTemplateId,
        CASE WHEN ct.customTemplateId IS NULL
             THEN c.paragraph ELSE ct.tmplateScript
        END AS AreaContent
FROM      t_content c LEFT JOIN t_customTemplate ct
          ON ct.customTemplateId = c.customTemplateId
WHERE      c.contentID IN (311,183,319)
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
It sounds like a simple matter of coalescing the custom template script with the paragraph. Something like this:

SELECT COALESCE(t_customTemplate.customTemplateID, t_content.paragraph) AS content
FROM t_content LEFT JOIN t_customTemplate ON t_content.customTemplateID=t_customTemplate.customeTemplateID
WHERE content_ID IN (311, 183, 319)
ORDER BY t_content.content_ID

hope this helps. Let me know.

Philippe
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
_agx_Commented:
It sounds like a simple matter of coalescing
Oh, Duh! Yeah, use COALESCE it's simpler.
0
 
_agx_Commented:
>> return in the order in which the contentID's are presented in <<
>> within the SQL 'IN' statement.  Otherwise, I'll have issues...  <<

If it's only a few values, the old case hack would work.  Anything more than that I'd look for a better solution.

SELECT ...
FROM    ....
WHERE ...
ORDER BY CASE 
	<cfloop from="1" to="#listLen(yourIDList)#" index="x">
		WHEN  t_content.contentID = #val(getToken(yourIDList, x))# THEN #x#
	</cfloop>
	ELSE #listLen(yourList)+1# 
END

Open in new window


0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Hi
There's not going to be any way to return the rows in the order specified in the IN clause. The only way I would see is build a list with the contentIDs in the order you want, then lookup the values from the cfquery results one at a time. It can be achieved with some code. What is the overarching reason for doing this?

Philippe
0
 
_agx_Commented:
>> There's not going to be any way to return the rows in the order specified in the IN clause. <<
    The ORDER BY CASE hack above does exactly that.  Though I will say there are
    better methods ...
   
>> Otherwise, I'll have issues...  <<
     But ... it does set off alarm bells ;-) Having that type of requirement often indicates
     a problem in the approach.  Out of curiosity, what is the reason for it?
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
Agx
I've never seen that hack. Does it really work?
I think it's cleaner to build the rows in a function clearly identified for that purpose. Easier to read for the next guy.

Philippe


0
 
_agx_Commented:
Yep, it works.  It's an old ms sql trick to avoid dynamic sql (ugly imo).  

Though I'm more curious why maintaining the order is so critical. It suggests a possible design flaw somewhere.
0
 
brianmfallsAuthor Commented:
aqx, I was able to get your solutions working.  It outputs just as it should.

<cfquery name="elementHTML" datasource="#request.dsn#">
	SELECT      c.paragraph, c.customTemplateId,
		CASE WHEN ct.customTemplateId IS NULL
			THEN c.paragraph ELSE ct.templateScript
		END AS areaContent
	FROM      t_content c LEFT JOIN t_customTemplate ct
			  ON ct.customTemplateId = c.customTemplateId
	WHERE      c.contentID IN (#load.sectionInfo#)
	ORDER BY CASE 
		<cfloop from="1" to="#listLen(load.sectionInfo)#" index="x">
			WHEN  c.contentID = #val(getToken(load.sectionInfo, x))# THEN #x#
		</cfloop>
		ELSE #listLen(load.sectionInfo)+1# 
	END
</cfquery>

Open in new window


damerval, I tried yours, but I could not get it to work without error.  (Conversion failed when converting varchar value '#outputs html from paragraph column#' to int)

Here is the rendered SQL from CFDebug:
	   SELECT COALESCE(t_customTemplate.customTemplateID, t_content.paragraph) AS content FROM t_content LEFT JOIN t_customTemplate ON t_content.customTemplateID=t_customTemplate.customTemplateID WHERE contentID IN (311, 183, 319) ORDER BY t_content.contentID 

Open in new window

0
 
_agx_Commented:
>> damerval, I tried yours, but I could not get it to work without error. <<

Probably just a typo. COALESCE should work fine if you correct the column names:

SELECT   c.paragraph, c.customTemplateId,
               COALESCE(ct.templateScript, c.paragraph)  AS areaContent
.....

Open in new window


Don't forget to use cfqueryparam with your id list

WHERE  c.contentID IN ( <cfqueryparam value="#load.sectionInfo#" cfsqltype="cf_sql_integer" list="true"> )

Open in new window


0
 
brianmfallsAuthor Commented:
I fixed all of the typos... I'm pretty sure that isn't the issue.
0
 
Philippe DamervalSenior Analyst ProgrammerCommented:
I had indicated the wrong column name in the original COALESCE - it should be templateScript instead of templateID.

COALESCE works by taking two values, returning the first one if it is not null, and the second one if it is.

The conversion error may also have to do with joining on the wrong column or assigning the wrong data type in the where clause.


0
 
_agx_Commented:
I didn't test it myself.  But I'd be surprised if that wasn't the issue.  Especially given the conversion error. Because as you can see, the CASE and COALESCE are doing the same thing.  As long as the column data types are aligned properly I'd expect both to work.
0
 
brianmfallsAuthor Commented:
I'll try it again shortly, making the correction to the column as you stated damerval, and of course, correcting any typos.  :)  Thanks guys.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 7
  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now