4 Replies Latest reply on Nov 1, 2010 8:33 AM by tclaremont

    QofQ join

    Dinghus Level 1

      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

        • 1. Re: QofQ join
          Dan Bracuk Level 5

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

          • 2. Re: QofQ join
            Dinghus Level 1

            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.

            • 3. Re: QofQ join
              Adam Cameron. Level 5

              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

              • 4. Re: QofQ join
                tclaremont Level 2

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