Databases
StackOverflow2013
Postsdbo
POODBC foxpro ORDER BY multiple columns and a specific value issue
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POODBC foxpro ORDER BY multiple columns and a specific value issue
    primarykey
    data
    text
    <p>I am working on a PHP program that reads from a FoxPro system. I am not allowed to modify the foxpro system as it is used for daily operations. So I have been using the ODBC driver with much success. The system I am currently working on requires me to create a union and show the data in a specific order. I have this query thus far:</p> <pre><code>SELECT arinvt01.item as recipe_group, arinvt01.item, arinvt01.descrip, arinvt01.cost, arinvt01.code as rcode FROM arinvt01 WHERE code = 'RW' UNION ALL SELECT arrecp01.item as recipe_group, arinvt01.item, arinvt01.descrip, arinvt01.cost, arinvt01.code as rcode FROM arinvt01 INNER JOIN arrecp01 ON (arrecp01.recipe = arinvt01.item) WHERE arrecp01.item IN ( SELECT item FROM arinvt01 WHERE code = 'RW' ) ORDER BY recipe_group </code></pre> <p>The first <code>SELECT</code> fetches the RAW items from the table while the second <code>SELECT</code> fetches the items that use the RAW items (the table <code>arrecp01</code> stores the associations for the items to the RAW items.) I am trying to list all items and categorize them by the RAW item so we can see all the items used for each RAW item. This means some items will be in more than one RAW item. I basically want the RAW item listed first for each <code>recipe_group</code>.</p> <p>This returns (only showing 20 rows):</p> <pre><code>recipe_group item descrip cost rcode ALMORAW RCHKI10OZTR RETAIL CHINA KISS 10OZ CASE 16.769 ALMORAW ALMORAW ALMONDS RAW 2.32 RW ALSPRAW ALSP ALFALFA SPROUTS 1.44 ALSPRAW ALSPLBS ALFALFA SPROUT LBS 1.44 ALSPRAW ALSPRAW ALFALFA SPROUTS RAW 1.44 RW ALSPRAW ALSP4OZEA ALFALFA SPROUT 4OZ 0.486 APPLEGRRAW FAPGDI250PLD5T FRUIT APPLE GR. DICE PLD 1/4" 5#TR 9.26 APPLEGRRAW FAPGWEDGE3B FRUIT APPLE GREEN WEDGE 3# 4.893 APPLEGRRAW FAPGRSL250SYS 6485249 APPLE GREEN SLICE 1/4" 20# 52.415 APPLEGRRAW APPLEGRSL8MIX APPLE GREEN SLICES INTO 8 MIX 5.457 APPLEGRRAW FAPGRWEDGE502 FRUIT APPLE GREEN WEDGE 50/2oz CASE 15.398 APPLEGRRAW FAPGDI2505B FRUIT APPLE GREEN DICE 1/4" 5# BAG 7.81 APPLEGRRAW FAPGWE2200CS FRUIT APPLE GR. WEDGE 200/2oz CS 47.51 APPLEGRRAW FAPGRPESLSYS APPLE GR. SLICED PLD/CRD 1/8" 2/5# 26.455 APPLEGRRAW APGSDI10 APPLES "GRANNY SMITH" DICED 1/4" 1.423 APPLEGRRAW FAPGRWEDGE4OZ FRUIT APPLE GREEN WEDGE 4oz EACH 0.222 APPLEGRRAW FAPGDI250 FRUIT APPLE GREEN DICE 1/4" 1.801 APPLEGRRAW FAPGSLHM5B FRUIT APPLE GREEN HALFMOON 5# 8.446 APPLEGRRAW FAPGPC5B FRUIT APPLE GREEN PEELED/CORED 5# 8.173 APPLEGRRAW APGDI5005B APPLE GREEN DICED 1/2" 5# BAG 4.795 </code></pre> <p>I imported these tables into MySQL for testing the data and in MySQL I can use this query to get the results as I need them.</p> <pre><code>SELECT arinvt01.item as recipe_group, arinvt01.item, arinvt01.descrip, arinvt01.cost, arinvt01.code as rcode FROM arinvt01 WHERE code = 'RW' UNION ALL SELECT arrecp01.item as recipe_group, arinvt01.item, arinvt01.descrip, arinvt01.cost, arinvt01.code as rcode FROM arinvt01 INNER JOIN arrecp01 ON (arrecp01.recipe = arinvt01.item) WHERE arrecp01.item IN ( SELECT item FROM arinvt01 WHERE code = 'RW' ) ORDER BY recipe_group, (rcode = 'RW') desc </code></pre> <p>Adding <code>, (code = 'RW') desc</code> to the <code>ORDER BY</code> claus returns the results exactly as I need them. It also works in MySQL with <code>ORDER BY recipe_group, CASE tcode WHEN 'RW' THEN 2 ELSE 3 END</code>.</p> <p>This returns (only showing 20 rows):</p> <pre><code>recipe_group item descrip cost rcode ALMORAW ALMORAW ALMONDS RAW 2.32 RW ALMORAW RCHKI10OZTR RETAIL CHINA KISS 10OZ CASE 16.769 ALSPRAW ALSPRAW ALFALFA SPROUTS RAW 1.44 RW ALSPRAW ALSP ALFALFA SPROUTS 1.44 ALSPRAW ALSPLBS ALFALFA SPROUT LBS 1.44 ALSPRAW ALSP4OZEA ALFALFA SPROUT 4OZ 0.486 APPLEGRRAW APPLEGRRAW APPLE GREEN ( CS #/CS) LB RAW 0.625 RW APPLEGRRAW FAPGDI250PLD5T FRUIT APPLE GR. DICE PLD 1/4" 5#TR 9.26 APPLEGRRAW FAPGWEDGE3B FRUIT APPLE GREEN WEDGE 3# 4.893 APPLEGRRAW FAPGRSL250SYS 6485249 APPLE GREEN SLICE 1/4" 20# 52.415 APPLEGRRAW APPLEGRSL8MIX APPLE GREEN SLICES INTO 8 MIX 5.457 APPLEGRRAW FAPGRWEDGE502 FRUIT APPLE GREEN WEDGE 50/2oz CASE 15.398 APPLEGRRAW FAPGDI2505B FRUIT APPLE GREEN DICE 1/4" 5# BAG 7.81 APPLEGRRAW FAPGWE2200CS FRUIT APPLE GR. WEDGE 200/2oz CS 47.51 APPLEGRRAW FAPGRPESLSYS APPLE GR. SLICED PLD/CRD 1/8" 2/5# 26.455 APPLEGRRAW APGSDI10 APPLES "GRANNY SMITH" DICED 1/4" 1.423 APPLEGRRAW FAPGRWEDGE4OZ FRUIT APPLE GREEN WEDGE 4oz EACH 0.222 APPLEGRRAW FAPGDI250 FRUIT APPLE GREEN DICE 1/4" 1.801 APPLEGRRAW FAPGSLHM5B FRUIT APPLE GREEN HALFMOON 5# 8.446 APPLEGRRAW FAPGPC5B FRUIT APPLE GREEN PEELED/CORED 5# 8.173 </code></pre> <p>When I do this with the foxpro ODBC driver I get the following error: <code>[Microsoft][ODBC Visual FoxPro Driver]SQL: ORDER BY clause is invalid.</code> or when using the <code>CASE</code> I get <code>[Microsoft][ODBC Visual FoxPro Driver]Command contains unrecognized phrase/keyword.</code></p> <p><strong>Update</strong> <code>IIF</code> throws the error: <code>[Microsoft][ODBC Visual FoxPro Driver]SQL: ORDER BY clause is invalid.</code></p> <p>Is it possible to get these results using the ODBC driver for foxpro? When I checked the syntax for the <a href="http://msdn.microsoft.com/en-us/library/ms710199%28v=vs.85%29.aspx" rel="nofollow">SELECT structure</a> I noticed it does allow multiple column <code>ORDER BY</code> but like I said, I keep getting a error. </p> <p>It should be noted that RAW items have a code of RW while most have no code and some have other values for code (EQ,EA,PA to name a few.) I only want RW to be listed first, the rest are irrelevant for this system.</p> <p>Perhaps I am missing something so thought I'd ask here for some help on this matter.</p> <p>Thanks</p> <p>I am using the <a href="http://adodb.sourceforge.net/" rel="nofollow">ADOdb Database Abstraction Library for PHP</a> if this helps.</p> <p>Not sure if necessary but just to be safe. Tables structure:</p> <p><strong>arinvt01</strong></p> <pre><code>CREATE TABLE IF NOT EXISTS `arinvt01` ( `item` varchar(15) NOT NULL DEFAULT '', `class` varchar(2) DEFAULT NULL, `descrip` varchar(35) DEFAULT NULL, `cost` double(12,4) DEFAULT NULL, `onhand` double(12,3) DEFAULT NULL, `onorder` double(12,3) DEFAULT NULL, `aloc` double(12,3) DEFAULT NULL, `wip` double(12,3) DEFAULT NULL, `price` double(12,4) DEFAULT NULL, `price1` double(12,4) DEFAULT NULL, `price2` double(12,4) DEFAULT NULL, `level2` double(12,3) DEFAULT NULL, `price3` double(12,4) DEFAULT NULL, `level3` double(12,3) DEFAULT NULL, `ptdqty` double(12,3) DEFAULT NULL, `ytdqty` double(12,3) DEFAULT NULL, `ptdsls` double(12,2) DEFAULT NULL, `ytdsls` double(12,2) DEFAULT NULL, `discrate` double(7,3) DEFAULT NULL, `unitms` varchar(2) DEFAULT NULL, `code` varchar(2) DEFAULT NULL, `type` varchar(5) DEFAULT NULL, `seq` varchar(5) DEFAULT NULL, `ldate` date DEFAULT NULL, `lastordr` date DEFAULT NULL, `orderpt` double(12,3) DEFAULT NULL, `orderqty` double(12,3) DEFAULT NULL, `supplier` varchar(6) DEFAULT NULL, `vpartno` varchar(15) DEFAULT NULL, `lead` double(3,0) DEFAULT NULL, `gllink` varchar(3) DEFAULT NULL, `decnum` double(1,0) DEFAULT NULL, `taxcode` varchar(1) DEFAULT NULL, `stkcode` varchar(1) DEFAULT NULL, `history` varchar(1) DEFAULT NULL, `weight` double(12,4) DEFAULT NULL, `req1` double(12,3) DEFAULT NULL, `rec1` double(12,3) DEFAULT NULL, `req2` double(12,3) DEFAULT NULL, `rec2` double(12,3) DEFAULT NULL, `req3` double(12,3) DEFAULT NULL, `rec3` double(12,3) DEFAULT NULL, `req4` double(12,3) DEFAULT NULL, `rec4` double(12,3) DEFAULT NULL, `req5` double(12,3) DEFAULT NULL, `rec5` double(12,3) DEFAULT NULL, `req6` double(12,3) DEFAULT NULL, `rec6` double(12,3) DEFAULT NULL, `signature` double(2,0) DEFAULT NULL, `price4` double(12,2) DEFAULT NULL, `level4` double(12,3) DEFAULT NULL, `price5` double(12,2) DEFAULT NULL, `level5` double(12,3) DEFAULT NULL, `price6` double(12,2) DEFAULT NULL, `level6` double(12,3) DEFAULT NULL, `price7` double(12,2) DEFAULT NULL, `level7` double(12,3) DEFAULT NULL, `price8` double(12,2) DEFAULT NULL, `level8` double(12,3) DEFAULT NULL, `price9` double(12,2) DEFAULT NULL, `level9` double(12,3) DEFAULT NULL, `olddate` date DEFAULT NULL, `qoitem` varchar(1) DEFAULT NULL, `shortdesc` varchar(17) DEFAULT NULL, `lotcode` varchar(1) DEFAULT NULL, `prlist` varchar(1) DEFAULT NULL, `cubic` double(6,2) DEFAULT NULL, `fprice` double(12,2) DEFAULT NULL, `fprice1` double(12,2) DEFAULT NULL, `fprice2` double(12,2) DEFAULT NULL, `fprice3` double(12,2) DEFAULT NULL, `fprice4` double(12,2) DEFAULT NULL, `fprice5` double(12,2) DEFAULT NULL, `fprice6` double(12,2) DEFAULT NULL, `fprice7` double(12,2) DEFAULT NULL, `fprice8` double(12,2) DEFAULT NULL, `fprice9` double(12,2) DEFAULT NULL, `salesmn` varchar(2) DEFAULT NULL, `recipe` varchar(1) DEFAULT NULL, `upccode` varchar(15) DEFAULT NULL, `packaging` varchar(1) DEFAULT NULL, `umqty` double(3,0) DEFAULT NULL, `umdesc` varchar(6) DEFAULT NULL, `duty` double(6,2) DEFAULT NULL, `itemno` varchar(15) DEFAULT NULL, `factor` double(12,5) DEFAULT NULL, `beginvt` double(12,3) DEFAULT NULL, `endinvt` double(12,3) DEFAULT NULL, `phydate` date DEFAULT NULL, `comment` varchar(65) DEFAULT NULL, `onhwght` double(12,2) DEFAULT NULL, `edited` datetime DEFAULT NULL, `editedby` varchar(10) DEFAULT NULL, `edworkst` varchar(10) DEFAULT NULL, `lastcost` double(10,3) DEFAULT NULL, `buyer` varchar(10) DEFAULT NULL, `unitqty` double(10,2) DEFAULT NULL, PRIMARY KEY (`item`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; </code></pre> <p><strong>arrecp01</strong></p> <pre><code>CREATE TABLE IF NOT EXISTS `arrecp01` ( `recipe` varchar(15) DEFAULT NULL, `item` varchar(15) DEFAULT NULL, `descrip` varchar(35) DEFAULT NULL, `qty` double(12,3) DEFAULT NULL, `cost` double(12,3) DEFAULT NULL, `entered` date DEFAULT NULL, `signature` double(2,0) DEFAULT NULL, KEY `item_recipe` (`item`,`recipe`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8; </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USJeremy
    1. USJeremy
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload