• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

QofQ join

Contributor ,
Oct 28, 2010 Oct 28, 2010

Copy link to clipboard

Copied

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

TOPICS
Advanced techniques

Views

622

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 29, 2010 Oct 29, 2010

Copy link to clipboard

Copied

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)#)

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Oct 29, 2010 Oct 29, 2010

Copy link to clipboard

Copied

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Oct 30, 2010 Oct 30, 2010

Copy link to clipboard

Copied

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Engaged ,
Nov 01, 2010 Nov 01, 2010

Copy link to clipboard

Copied

LATEST

Ditto. Go from the XML directly into your database. Your options increase by an order of magnitude once the data is in a database.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation