Well my issue has been resolved .. not in the fashion I had orginally hoped
for, but with the vast differences between MSSQL and MySQL I split up
specific queries based on which database the customer is using ... Though
not preferred, it will work with various queries and such throughout the
application.
Here is the code I used to do the same thing as a UNION in mySQL (v3.2.3)..
<cfquery datasource="#request.dsn#" name="getState"
username="#request.dbUserName#" password="#request.dbPassword#">
CREATE TEMPORARY TABLE tmp type=HEAP SELECT state, state_abv FROM
#request.taxStateTable#
</cfquery>
<cfquery datasource="#request.dsn#" name="getState"
username="#request.dbUserName#" password="#request.dbPassword#">
INSERT INTO tmp SELECT province AS state, province_abv AS state_abv
FROM #request.taxCanadaTable#
</cfquery>
<cfquery datasource="#request.dsn#" name="getState"
username="#request.dbUserName#" password="#request.dbPassword#">
SELECT state, state_abv FROM tmp ORDER BY state
</cfquery>
<cfquery datasource="#request.dsn#" name="getState"
username="#request.dbUserName#" password="#request.dbPassword#">
DROP TABLE tmp
</cfquery>
I know I could have done the same thing in QoQ, but there were other areas
that needed fixing too that by doing a <cfif variables.dbType Is "mysql">
mysqlcode<cfelse>MS SQL code</cfif> seemed to work well.
Just FYI for those who care :)
Paul Giesenhagen
QuillDesign
----- Original Message -----
From: "Tyler Silcox" <[EMAIL PROTECTED]>
To: "CF-Talk" <[EMAIL PROTECTED]>
Sent: Wednesday, September 25, 2002 9:41 AM
Subject: Re: OT: Can't use UNION
> Paul, are you using CFMX? Because you could run your separate queries and
> then QoQ with a UNION statement. I know it's not a SQL solution, but it
> might do you alright->
>
> Tyler
> email | [EMAIL PROTECTED]
>
> ----- Original Message -----
> From: "Paul Giesenhagen" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, September 25, 2002 9:53 AM
> Subject: Re: OT: Can't use UNION
>
>
> v3.2.3 doesn't support FULL OUTER JOIN... Jochem .. I thought you had it
for
> me, dang!
>
> Paul Giesenhagen
> QuillDesign
>
> ----- Original Message -----
> From: "Jochem van Dieten" <[EMAIL PROTECTED]>
> To: "CF-Talk" <[EMAIL PROTECTED]>
> Sent: Wednesday, September 25, 2002 4:31 AM
> Subject: Re: OT: Can't use UNION
>
>
> > Paul Giesenhagen wrote:
> > > I am putting together an application and I canot use UNION, here is my
> query that I have used in the past, does anyone have a solution that would
> not use UNION?
> > >
> > > SELECT state, state_abv
> > > FROM #request.taxStateTable#
> > > UNION ALL
> > > SELECT province AS state, province_abv AS state_abv
> > > FROM #request.taxCanadaTable#
> > > ORDER BY 1
> >
> > SELECT
> > CASE WHEN tST.state IS NULL
> > THEN tCT.province
> > ELSE tST.state END AS state,
> > CASE WHEN tST.state_abv IS NULL
> > THEN tCT.province_abv
> > ELSE tST.state_abv END AS state_abv
> > FROM
> > #request.taxStateTable# tST
> > FULL OUTER JOIN
> > #request.taxCanadaTable# tCT
> >
> > Jochem
> >
> >
>
>
______________________________________________________________________
Your ad could be here. Monies from ads go to support these lists and provide more
resources for the community. http://www.fusionauthority.com/ads.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
