?
Solved

dynamic sql

Posted on 2011-03-01
15
Medium Priority
?
323 Views
Last Modified: 2012-05-11
I need a smart SQL person for this one.   I have a somewhat static table that I use to create colums in my resulting query.  I have a table with static data for each advertising order.  I have a table that relates to the advertising table and contains a variable number of contacts.  The code is in Coldfusion but that's pretty simple stuff.  My concern is that a record is missing for the results. For Ad id 197136 there should be a listing.   My biggest concern is this encredible query I've built.  I'm convinced there is a better way.


 client_services_contacts table client_services_contact_types
The resulting output page---------------------------------
<!--- panel13_ads.cfm --->
<cfinvoke component="globalcfc.basic" method="getContactsByIndiv" individ="#indivnum#" servicelineid="5" returnvariable="Qcontacts" />
<cfinvoke component="globalcfc.basic" method="getContactTypes" servicelineid="5" returnvariable="Qcontacts2x" />

<cfset QADdata = Application.objIndivBasic.GetAdvertising(CompanyNum = "#Val(users_Info.current_company_number)#",indivNum="#indivNum#") />
<div class="PanelTab">
	Advertising - 
	<span><a href="<cfoutput>#request.controlUrl#</cfoutput>advertising/index.cfm?fa=new" target="_blank">Add New</a></span>
</div>

<div class="PanelContent">
	<cfif QADdata.recordcount eq 0>
		No records for this service
	<cfelse>
		<cfgrid format="html" name="grid_Advertising" query="QADdata"  width="1000" selectonload="no" selectmode="row" autowidth="yes" colheaderalign="center" colheaders="yes" griddataalign="left"   selectcolor="##FF9900">
			<cfgridcolumn name="ID" header="ID" display="Yes" dataalign="left" width="10" href="#request.controlPath#advertising/index.cfm?fa=view&ad_id=#QADdata.ID#" target="_blank" />
			<cfgridcolumn name="package_id" header="Package" dataalign="left" width="14" target="_blank"  />
			<cfgridcolumn name="startdate" header="Date Opened" dataalign="left" width="14" target="_blank" />
			<cfgridcolumn name="enddate" header="Date Closed" dataalign="left" width="14"  target="_blank" />
			<cfgridcolumn name="fee" header="Total Fee" dataalign="left" width="10" target="_blank" />
			<cfgridcolumn name="marketer" header="Marketer" dataalign="left" width="20" target="_blank" />
			<cfloop query="Qcontacts2x">
			<cfgridcolumn name="#Qcontacts2x.contact_type_acronym#contactname" header="#Qcontacts2x.contact_type_acronym# Contact Name" dataalign="left" width="20" />
			</cfloop>
		</cfgrid>
	</cfif>
</div>


The main query----------------------------------------------
	<cffunction name="getAdvertising" output="false" returntype="query">
		<cfargument name="indivNum" required="No" type="string" default="" />
		<cfargument name="companyNum" required="No" type="string" default="" />
		<cfset var queryResults="" />
		<cfinvoke component="globalcfc.basic" method="getContactTypes" servicelineid="5" returnvariable="Qcontacts2" />
		<cfinvoke component="globalcfc.basic" method="getContactsByIndiv" individ="#indivnum#" servicelineid="5" returnvariable="Qcontacts" />
		<cfquery datasource="#request.dsn#" name="queryResults">
			SELECT  AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description <cfloop query="QContacts2">,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			<cfloop query="Qcontacts2">
			left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID
			left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid  
			LEFT OUTER JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id
			</cfloop>
			WHERE 0=0 
			<cfif val(arguments.CompanyNum) NEQ "">
				AND ad.company_ID = 
				<cfqueryparam value="#arguments.companynum#" />
			</cfif>
			and (
			<cfif val(arguments.indivNum) NEQ "">
				<cfloop query="Qcontacts">
				 #Qcontacts.contact_ID# = #arguments.indivnum# or
				</cfloop>
				0=1)
			</cfif>
			ORDER BY ad.startdate DESC 
		</cfquery>
		<cfreturn queryResults />
	</cffunction>

The supporting queries------------------------------------
	<cffunction name="getContactTypes" returntype="query" access="remote">
		<cfargument name="servicelineID" required="yes" />
		<cfquery datasource="#request.dsn#" name="contacttypes">
			select * from client_services_contact_types where servicelineid = #val(arguments.servicelineid)# order by id 
		</cfquery>
		<cfreturn contacttypes />
	</cffunction>


	<cffunction name="getContactsByIndiv" returntype="query" access="remote">
		<cfargument name="indivID" required="yes" />
		<cfargument name="servicelineID" required="yes" />
		<cfquery datasource="#request.dsn#" name="contactsByIndiv">
			SELECT csc.contact_ID, csc.contactTypeID, csc.active, csc.date_added, csct.contact_Type_Acronym,  csct.id as contacttypesid, ui.lastname + ', ' + ui.firstname as contactname ,csc.service_id  
			FROM Client_Services_Contacts as csc
			LEFT OUTER JOIN Users_Info AS ui ON ui.ID = csc.contact_id
			left join client_services_contact_types as csct on csct.id = csc.contacttypeid   
			WHERE (csct.ServiceLineID = #val(arguments.serviceLineID)# and csc.contact_id = #val(arguments.indivID)# AND csc.active = 1) 
		</cfquery>
		<cfreturn contactsByIndiv />
	</cffunction>

	<cffunction name="getContacts" returntype="query" access="remote">
		<cfargument name="serviceID" required="yes" />
		<cfargument name="servicelineID" required="yes" />
		<!--- Query Statement --->
		<cfquery datasource="#request.dsn#" name="contacts">
			<!--- select csc.contact_id, csct.description, csc.contact_type_acronym, csc.active, csc.date_added, ui.lastname + ', ' + ui.firstname as contactname, csct.acronym
				from client_services_contacts as csc  inner join
				client_services_contact_types as csct on csct.servicelineid = '5' left join
				Users_info as ui on ui.id = csc.contact_id AND csc.contact_type_acronym = csct.acronym
				where csc.service_id = #arguments.serviceID# and csc.active =1
				order by csct.id --->
			SELECT ui.ID as uid, csct.description, csc.ContactTypeID, csc.active, csc.date_added, ui.LastName + ', ' + ui.FirstName AS contactname, csct.contact_type_acronym as acronym, csct.id AS csctid 
			FROM Client_Services_Contact_Types AS csct 
			LEFT OUTER JOIN Client_Services_Contacts AS csc ON csct.id = csc.contactTypeID AND csc.service_id = #val(arguments.serviceID)# AND csc.active = 1 
			LEFT OUTER JOIN Users_Info AS ui ON ui.ID = csc.contact_id 
			WHERE (csct.ServiceLineID = #val(arguments.serviceLineID)#) ORDER BY csctid 
		</cfquery>
		<cfreturn contacts />
	</cffunction>

Open in new window

Resulting-page-with-dumps.PNG
0
Comment
Question by:lantervj
[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
  • 9
  • 6
15 Comments
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 35014098
Since you're using all OUTER JOINS, then you would think all ADVERTISING records would appear.  However, your WHERE clause can prevent it.   If your WHERE clause is against any of your OUTER JOIN tables, then you prevent the records from showing....


WHERE 0=0
<cfif val(arguments.CompanyNum) NEQ "">
   ===== THIS IS OK BECAUSE IT IS CHECKING THE ADVERTISING TABLE ==========
   AND ad.company_ID = <cfqueryparam value="#arguments.companynum#" />
</cfif>
and (
<cfif val(arguments.indivNum) NEQ "">
 <cfloop query="Qcontacts">
    ====== ASSUMING THIS CRITERIA IS AGAINST AN OUTER JOIN TABLE, IT WILL KEEP THE ADVERTISING RECORD FROM SHOWING ========
   #Qcontacts.contact_ID# = #arguments.indivnum# or
  </cfloop>
0=1)
</cfif>


You need to put the where clause of any LEFT JOIN table as part of the JOIN ... ON... Condition.
That is IF you want it to stay OUTER JOINED.  

Putting the condition in the WHERE clause kills the OUTER join...

Hope that makes sense.


0
 

Author Comment

by:lantervj
ID: 35014169
I think I understand it.   But, to make it more clear,  this was originally written with the contact ids  embedded in the advertising table. I had to remove them and create a seperate table for a variable number of contacts but maintaining only 4 types of contacts.  The general idea here is to read all advertising contacts and look for just one individual and list all the ads he/she is part of.  I think I should have attacked this differently by collecting the contact records first.  
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35014191

What table joins ADVERTISING and CONTACTS ?

0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 
LVL 39

Expert Comment

by:gdemaria
ID: 35014204

This line is confusing...

 #Qcontacts.contact_ID# = #arguments.indivnum# or

Does this mean you have a Column named after the ID?
 
  123 = 100

You have a column named 123 ?

How about CONTACT_ID = 100 ?


0
 

Author Comment

by:lantervj
ID: 35019677
This is how I ended up.  I was not qualifing the left side of the where clause to resolve to hr.contact_id = indivnum.  It appears to work now.
SELECT AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description 
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id
			</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID 			left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				LEFT JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0 and (
				 
				<cfloop query="Qcontacts2">
					#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# or
					</cfloop>
					0 = 1)
			ORDER BY ad.startdate DESC

Open in new window

0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35019809
I'm curious..

since you already have this statement in your LEFT JOIN clause..

 #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum#


what happens if you remove it from your WHERE clause at the bottom?
I would think the results would be the same.

But either way, glad it's working
0
 

Author Comment

by:lantervj
ID: 35020965
I tried that and got all the ad records in the table.  I was curious about that too.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35021103

if you don't want that, then I would suspect you could just change the join to an inner join..

<cfloop query="Qcontacts2">
   INNER join client_services_contacts .....

0
 

Author Comment

by:lantervj
ID: 35021247
I get zero records.

<cfloop query="Qcontacts2">
                        inner join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID                   left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid

WHERE 0=0

It doesn't make sense that it returns no records.
0
 

Author Comment

by:lantervj
ID: 35021262
The only things that works is to have the compare for the indivnum in both the left join and where clause.
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 35021326
it must be the other conditions of the join..

 it was worth a test I guess
0
 

Author Closing Comment

by:lantervj
ID: 35021765
Once again, many thanks.
0
 

Author Comment

by:lantervj
ID: 35022441
I think I closed this too soon.  The attached queries give zero records and all records.  I can't remember what I might have changed.  I was testing with new data.
No data returned:
SELECT AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description ,c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id
			</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			LEFT JOIN companies as c on c.id = AD.company_id 
			<cfloop query="Qcontacts2">
				inner join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID 			inner join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				inner JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0 and (				 
				 <cfloop query="Qcontacts2">
					#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# or
					</cfloop>
					0 = 1)
			ORDER BY ad.startdate DESC

This one returns 8 duplicates of the first ad record but only a single row of each of the other 2 ads (which wold be correct).

Open in new window

0
 

Author Comment

by:lantervj
ID: 35022459
Oops, forgot the second version of the code
SELECT AD.ID, AD.package_id, ad.startdate, ad.enddate, ad.fee, au.name as marketer, ADdesc.description ,c.name as companyname
			<cfloop query="QContacts2">
				,ui#Qcontacts2.contact_type_acronym#.lastname + ', ' + ui#Qcontacts2.contact_type_acronym#.firstname as #Qcontacts2.contact_type_acronym#contactname, #Qcontacts2.contact_type_acronym#.contact_id as #Qcontacts2.contact_type_acronym#contact_id
			</cfloop>
			FROM advertising as AD 
			LEFT JOIN admin_users as au on au.adminid = ad.marketer_id 
			LEFT JOIN advertising_components as Adc on Adc.advertising_id = ad.ID 
			LEFT JOIN advertising_descriptions as ADdesc on ADdesc.id = ADc.component_id 
			LEFT JOIN companies as c on c.id = AD.company_id 
			<cfloop query="Qcontacts2">
				left join client_services_contacts as #Qcontacts2.contact_type_acronym# on #Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# and #Qcontacts2.contact_type_acronym#.contacttypeid = #Qcontacts2.id# and #Qcontacts2.contact_type_acronym#.service_id = AD.ID 			left join client_services_contact_Types as #Qcontacts2.contact_type_acronym#types on #Qcontacts2.contact_type_acronym#types.id = #Qcontacts2.contact_type_acronym#.contacttypeid 
				left JOIN Users_Info AS ui#Qcontacts2.contact_type_acronym# ON ui#Qcontacts2.contact_type_acronym#.ID = #Qcontacts2.contact_type_acronym#.contact_id 
			</cfloop>
			WHERE 0=0 and (				 
				 <cfloop query="Qcontacts2">
					#Qcontacts2.contact_type_acronym#.contact_id = #arguments.indivnum# or
					</cfloop>
					0 = 1)
			ORDER BY ad.startdate DESC

Open in new window

0
 

Author Comment

by:lantervj
ID: 35022621
This is how the query that returns zero rows should looks when it resolves;

 
SELECT     TOP (100) PERCENT AD.id, AD.package_id, AD.startdate, AD.enddate, AD.fee, au.Name AS marketer, ADdesc.description, c.Name AS companyname, 
                      uihr.LastName + ', ' + uihr.FirstName AS hrcontactname, hr.contact_id AS hrcontact_id, uimkt.LastName + ', ' + uimkt.FirstName AS mktcontactname, 
                      mkt.contact_id AS mktcontact_id, uitax.LastName + ', ' + uitax.FirstName AS taxcontactname, tax.contact_id AS taxcontact_id, 
                      uiag.LastName + ', ' + uiag.FirstName AS agcontactname, ag.contact_id AS agcontact_id
FROM         dbo.Advertising AS AD LEFT OUTER JOIN
                      dbo.Admin_Users AS au ON au.adminID = AD.marketer_id LEFT OUTER JOIN
                      dbo.Advertising_Components AS Adc ON Adc.advertising_id = AD.id LEFT OUTER JOIN
                      dbo.Advertising_Descriptions AS ADdesc ON ADdesc.id = Adc.component_id LEFT OUTER JOIN
                      dbo.Companies AS c ON c.ID = AD.company_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS hr ON hr.contact_id = 27020 AND hr.ContactTypeID = hr.id AND hr.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS hrtypes ON hrtypes.id = hr.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uihr ON uihr.ID = hr.contact_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS mkt ON mkt.contact_id = 27020 AND mkt.ContactTypeID = mkt.id AND mkt.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS mkttypes ON mkttypes.id = mkt.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uimkt ON uimkt.ID = mkt.contact_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS tax ON tax.contact_id = 27020 AND tax.ContactTypeID = tax.id AND tax.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS taxtypes ON taxtypes.id = tax.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uitax ON uitax.ID = tax.contact_id LEFT OUTER JOIN
                      dbo.Client_Services_Contacts AS ag ON ag.contact_id = 27020 AND ag.ContactTypeID = ag.id AND ag.service_id = AD.id LEFT OUTER JOIN
                      dbo.Client_Services_Contact_Types AS agtypes ON agtypes.id = ag.ContactTypeID LEFT OUTER JOIN
                      dbo.Users_Info AS uiag ON uiag.ID = ag.contact_id
WHERE     (0 = 0) AND (hr.contact_id = 27020) OR
                      (0 = 0) AND (mkt.contact_id = 27020) OR
                      (0 = 0) AND (tax.contact_id = 27020) OR
                      (0 = 0) AND (ag.contact_id = 27020) OR
                      (0 = 0) AND (0 = 1)
ORDER BY AD.startdate DESC

Open in new window

0

Featured Post

Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
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
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

752 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