Skip to main content
D
Dinghus
Inspiring
October 28, 2010
Question

QofQ join

  • October 28, 2010
  • 1 reply
  • 801 views

I have an excel spreadsheet that lists over 300k products. Actually it comes as an xml file but the CF server can't parse a file that big so we import it into excel upload it to the site and then use cfspreadsheet to suck it into a query. That all works well.

NOW here is the problem. The way it works is basically 2 different tables in the same spreadsheet due to the way excel handles subcatagories.

The products have the following info (basically, no need to list all the fields)

ProdID (KEY) and CAT

Then there are also

ProdID1 and CAT2

These actually are subcategories and are on seperate lines in the spreadsheet. Duplicate data except for the CAT2 field.

So I want to combine these down to just 1 entry for each product. It would seem to be easy at first but not so fast. This is a QofQ and a lot of stuff doesn't work. I thought about putting the info into 2 temp tables and then doing the join but if I can do this in one query it would be great.

Basically I have to match up the ProdID to ProdID1 and grab CAT2 putting it into the main query. But QoQ doesn't allow a subquery in the select statement. Also some products don't have a subcatagory.

Any ideas?

Oh yeh. ColdFusion 9

This topic has been closed for replies.

1 reply

D
Dan_Bracuk
Inspiring
October 29, 2010

My initial thought is that 300K records of something might be better off in a database than an xml file for a variety of reasons.

My next thought is that when I am presented with a QofQ limitation problem, I normally solve it by using more than one query.  You stated that you can't do subqueries.  You might be able to do this.

QofQ1

select somefield

from etc

QofQ2

select somefields

from one_or_two_queries

where a_field in (#valuelist(QofQ1.somefield)#)

D
DinghusAuthor
Inspiring
October 29, 2010

My initial thought was "WTF?" My client receives this xml file and there is nothing he can do about the format. Actually xml is the best way to transmit this info BUT it needs to be broken up in chunks. Okay, actually the best way would be a webservice that I could tap into to pull the data over. BUT once again, I have no control over that.

The way it is working out I think there are 2 solutions:

1 - 2 tables. 1 takes the top level info the other takes just the productid and subcat. Then join them in a normal query.

2 - Put all the top level info into the product table and then update them with the subcat by looping over the subcat query. Could be a long loop but since it is done on off hours it should be okay.

I'd just hoped there was someway of pulling off a pretty join or something with QoQ but I guess not.

A
Adam Cameron.
Inspiring
October 30, 2010

Not really an answer to your question re the best DB schema for the solution, but can you not suck the XML straight into the DB without involving CF?  I'm not sure that using an XLS file as an intermediary DB sort of thing is a very good approach, if it can possibly be avoided.

--

Adam

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices