Subquery in FROM?

[ThatRickGuy]

Hi! I'm having some problems with a sub query. I have to admit I'm not too familiar with uSQL/Informix but this is an older app that our users want modified.

SELECT 
  (... field list...)
FROM 
  CUSTOMR_SHIP_TO,
  INVOICE_PRODUCT,
  [COLOR=#ff0000](SELECT DISTINCT
    PROGRAM_TYPE,
    CUSTOMER_NUMBER
   FROM
    SERIAL_EQUIP) CUSTOMER_PROGRAM_TYPE[/color]
WHERE 
  CUSTOMR_SHIP_TO.SHIP_TO = INVOICE_PRODUCT.SHIP_TO    AND
  CUSTOMR_SHIP_TO.SHIP_TO = CUSTOMER_PROGRAM_TYPE.CUSTOMER_NUMBER

When I try to run this query I get: "*** Error: ')' expected here (DISTINCT)" If I remove the DISTINCT command I get the same error except the (FROM) is specified instead.

Any thoughts?

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Switch the syntax arround after looking at some more web sites to this:

SELECT 
  (... field list ...),
  [COLOR=#ff0000](SELECT DISTINCT PROGRAM_TYPE FROM SERIAL_EQUIP WHERE SERIAL_EQUIP.CUSTOMER_NUMBER = CUSTOMR_SHIP_TO.SHIP_TO) AS EQUIP_PROGRAM_TYPE[/color]
FROM 
  CUSTOMR_SHIP_TO,
  INVOICE_PRODUCT,
WHERE 
  CUSTOMR_SHIP_TO.SHIP_TO = INVOICE_PRODUCT.SHIP_TO

But I'm still getting that same ')' expected error.

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[ThatRickGuy]

Yet another syntax that still doesn't work:

[COLOR=#ff0000]SELECT DISTINCT
  CUSTOMER_NUMBER, 
  PROGRAM_TYPE 
FROM 
  SERIAL_EQUIP 
INTO TEMP EQUIP_PROGRAM_TYPE;[/color]
SELECT 
  INVOICE_PRODUCT.INVOICE_DATE,
  INVOICE_PRODUCT.AR_INVOICE, 
  INVOICE_PRODUCT.QTY_SHIP_INP, 
  INVOICE_PRODUCT.UNIT_PRICE_INP, 
  INVOICE_PRODUCT.UNIT_COST_INP, 
  (INVOICE_PRODUCT.QTY_SHIP_INP*INVOICE_PRODUCT.UNIT_COST_INP) AS ext_cost, 
  INVOICE_PRODUCT.PRODUCT_NUMBER2, 
  INVOICE_PRODUCT.CUSTOMER_NUMBER, 
  CUSTOMR_SHIP_TO.SHIP_TO, 
  CUSTOMR_SHIP_TO.CUS_NAME1, 
  CUSTOMR_SHIP_TO.CUS_ADDRESS1, 
  CUSTOMR_SHIP_TO.CUS_CITY, 
  CUSTOMR_SHIP_TO.CUS_STATE, 
  INVOICE_PRODUCT.REPRESENTATIVE, 
  INVOICE_PRODUCT.AR_REFER, 
  INVOICE_PRODUCT.EXEMPT_CODE_INP,
  CUSTOMER_PROGRAM_TYPE.PROGRAM_TYPE,
  EQUIP_PROGRAM_TYPE.PROGRAM_TYPE
FROM 
  CUSTOMR_SHIP_TO,
  INVOICE_PRODUCT,
  EQUIP_PROGRAM_TYPE
WHERE 
  CUSTOMR_SHIP_TO.SHIP_TO = INVOICE_PRODUCT.SHIP_TO    AND
  CUSTOMR_SHIP_TO.SHIP_TO = EQUIP_PROGRAM_TYPE.CUSTOMER_NUMBER AND
  INVOICE_PRODUCT.AR_REFER > 'dQ5312' AND
  INVOICE_PRODUCT.REPRESENTATIVE <> '' AND 
  INVOICE_PRODUCT.REPRESENTATIVE <> '100000' AND
  INVOICE_PRODUCT.REPRESENTATIVE <> '300000' AND 
  INVOICE_PRODUCT.REPRESENTATIVE <> '350000' AND
  INVOICE_PRODUCT.REPRESENTATIVE <> '270000' AND 
  INVOICE_PRODUCT.REPRESENTATIVE <> '500000';

With this code, which should be selecting the distinct customer number and program type values and insterting them into the temp table EQUIP_PROGRAM_TYPE, I get 2 (or 3?) errors:

*** Error: superfluous (INTO)
*** Error: superfluous (INTO)*** ERROR: Unknown Table 'EQUIP_PROGRAM_TYPE'

-Rick

VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[alcyone]

I don't exactly know what it is that you want, but as far as I know you can't use a subquery in the SELECT part. Try something like:

SELECT
<field list>,
PROGRAM_TYPE, CUSTOMER_NUMBER
FROM
CUSTOMR_SHIP_TO c,
INVOICE_PRODUCT i,
SERIAL_EQUIP s
WHERE
c.SHIP_TO = i.SHIP_TO
AND s.CUSTOMER_NUMBER = c.SHIP_TO

Hope this helps

 

[ThatRickGuy]

Yeah, I wound up trying that... maxed the client at 3.5+ gigs of memory/page file and never spit out the data.

I wound up creating two seperate queries and manipulated the data in VB. Performance was still poor (about 15 minutes) but it is successful and uses a much more reasonable amount of memory.

-Rick



VB.Net Forum forum796 forum855 ASP.NET Forum
I believe in killer coding ninja monkeys.

 

[alcyone]

I don't know if you will get it much faster than this. As far as I see you need two joins (three tables). Some things you could try:

1. Verify you have INDEXes created on the columns you use in the join.

2. Switch the table order in the FROM clause, because at first table1 is joined to table2 and the result is joined to table3. Choose the table-order so that the resuult of the first join is the smallest.

3. Run UPDATE STATISTICS. This will recalculate the execution paths which are being used for the internal Informix optimizer.

Good luck!