Solved

Help with correlated sub query in select list

Posted on 2006-07-21
7
392 Views
Last Modified: 2012-08-13
I am struggling to make this query below work and am looking for help.

The query is running against a Pervasive 2000i database.

SELECT
  B.BKIC_PROD_CODE Part_Num,
  B.BKIC_PROD_DESC Description,
  W.MTWO_WIP_COMQTY COMQTY,
  W.MTWO_WIP_SQTY SQTY,
  W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
  W.MTWO_WIP_ELABOR ELABOR,
  W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
  (SELECT SUM(WO.MTWORO_ESTHRS)
    FROM
    WORKOUTOLD WO
    WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
  W.MTWO_WIP_AFIN AFINISH,
  W.MTWO_WIP_WOPRE WOPRE
FROM  
  BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WIP_CODE
WHERE
  W.MTWO_WIP_STATUS='C' AND
  W.MTWO_WIP_AFIN>='2003-03-01' AND
  W.MTWO_WIP_AFIN<='2006-03-29' AND
  B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
  B.BKIC_PROD_CLASS,
  B.BKIC_PROD_CODE

The  query runs fine when I remove the subquery in the select list.  

Any thoughts as to what I have wrong?
0
Comment
Question by:pdbernier
7 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17157347
>The  query runs fine when I remove the subquery in the select list.  
what's the error message, resp problem result?
0
 

Author Comment

by:pdbernier
ID: 17157362
The message I get from the Pervasive SQL Editor is

Some error(s) encountered while executing SQL statement(script).

Not very helpful.
0
 
LVL 7

Expert Comment

by:FDzjuba
ID: 17157636
you can't have SELECT statement in the select vaules


use the following.

SELECT
  B.BKIC_PROD_CODE Part_Num,
  B.BKIC_PROD_DESC Description,
  W.MTWO_WIP_COMQTY COMQTY,
  W.MTWO_WIP_SQTY SQTY,
  W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
  W.MTWO_WIP_ELABOR ELABOR,
  W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
  SUM(WO.MTWORO_ESTHRS)
    FROM
    WORKOUTOLD WO
    WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),
  W.MTWO_WIP_AFIN AFINISH,
  W.MTWO_WIP_WOPRE WOPRE
FROM  
  BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WIP_CODE
WHERE
  W.MTWO_WIP_STATUS='C' AND
  W.MTWO_WIP_AFIN>='2003-03-01' AND
  W.MTWO_WIP_AFIN<='2006-03-29' AND
  B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
  B.BKIC_PROD_CLASS,
  B.BKIC_PROD_CODE
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 19

Expert Comment

by:folderol
ID: 17157969
you can re-write it to

SELECT
  B.BKIC_PROD_CODE Part_Num,
  B.BKIC_PROD_DESC Description,
  W.MTWO_WIP_COMQTY COMQTY,
  W.MTWO_WIP_SQTY SQTY,
  W.MTWO_WIP_COMQTY / W.MTWO_WIP_SQTY YIELD,
  W.MTWO_WIP_ELABOR ELABOR,
  W.MTWO_WIP_ALABOR ALABOR, W.MTWO_WIP_ALABOR / W.MTWO_WIP_SQTY LAB_Per_Pc,
  WO.tot_estimated_hours,
  W.MTWO_WIP_AFIN AFINISH,
  W.MTWO_WIP_WOPRE WOPRE
FROM  
  BKICMSTR B INNER JOIN WORKORD W ON B.BKIC_PROD_CODE=W.MTWO_WIP_CODE
left outer join
(
   SELECT SUM(MTWORO_ESTHRS) as tot_estimated_hours, MTWORO_WOPRE, MTWORO_WOSUF
   FROM WORKOUTOLD
   group by MTWORO_WOPRE, MTWORO_WOSUF
) as WO on
WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF
WHERE
  W.MTWO_WIP_STATUS='C' AND
  W.MTWO_WIP_AFIN>='2003-03-01' AND
  W.MTWO_WIP_AFIN<='2006-03-29' AND
  B.BKIC_PROD_CODE LIKE '10275%'
ORDER BY
  B.BKIC_PROD_CLASS,
  B.BKIC_PROD_CODE


OR, you could just try assigning a column name to the subquery, which your editor may require, your query looks like it would run in MS SQL Query Analyser.  I don't know the datatype of MTWORO_ESTHRS but I assume you tested the sub-query separately and it is bringing back the correct summed value.

'tot_estimated_hours' = (SELECT SUM(WO.MTWORO_ESTHRS)
    FROM
    WORKOUTOLD WO
    WHERE WO.MTWORO_WOPRE = W.MTWO_WIP_WOPRE AND WO.MTWORO_WOSUF = W.MTWO_WIP_WOSUF),


Tom
0
 

Author Comment

by:pdbernier
ID: 17168602
Tom,

I've tried both of your suggestions, but still no luck.  I am wondering if Pervasive 2000i cannot handle a subquery like this.
0
 
LVL 19

Accepted Solution

by:
folderol earned 500 total points
ID: 17169332
Can't help you there, I only use MS products.  We already know the the query runs without the sub query. Will this query run as well?

   SELECT SUM(MTWORO_ESTHRS) as tot_estimated_hours, MTWORO_WOPRE, MTWORO_WOSUF
   FROM WORKOUTOLD
   group by MTWORO_WOPRE, MTWORO_WOSUF

If it does, I am out of ideas.  The query as a whole looks pretty conventional so it should be acceptable.

Tom
0
 

Author Comment

by:pdbernier
ID: 17169677
That query does run, but the entire query does not.

Thanks for the help.
0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question