Aggregate Data from Two Tables

clakerley · Mar 13, 2006

Hello Everyone,

I am having trouble creating a query that pulls aggregate data from separate tables.

Background:

My database tracks sales data. Each sales opportunity has an opportunity identifier (OppID). Each opportunity is related to a business unit (BU). Each opportunity has one (or more) vehicle models associated with it.

I track sales data on two tables. One table stores data at the opportunity level. The other stores data at a deeper level, the opportunity/vehicle level. Here are simplified versions of these tables:

Code:

Table: Sales_Opp
Fields:
   OppID,     : String
   BU,        : String
   PeakAnnRev : Double

Table: Sales_Vehicle
Fields:
   OppID,     : String
   Vehicle    : String
   BU,        : String
   PeakAnnRev : Double

Here is an example of the data my tables can contain:

Code:

Sales_Opp:
OppID  BU   PeakAnnRev
-----  --   ----------
Opp1   BU1  1000
Opp2   BU2  2000
Opp3   BU2  3000

Sales_Vehicle:
OppID  Vehicle  BU   PeakAnnRev
-----  -------  --   ----------
Opp1   Veh1     BU1   500
Opp1   Veh2     BU1   600
Opp2   Veh3     BU2  2000
Opp3   Veh4     BU2  2200
Opp3   Veh5     BU2   900

Note: The sum of the PeakAnnRev values for the vehicles within an opportunity do NOT necessarily equal the PeakAnnRev value for the opportunity as a whole. In fact, they very often differ.

My task was to run a query that returns the number of opportunities and the sum of the PeakAnnRev of those opportunities, grouped by business Unit. I did this by running the following query:

Code:

SELECT BU, Sum(1) as NumOpps, Sum(PeakAnnRev) as ValueOpps
FROM Sales_Opp
GROUP BY BU
ORDER BY BU

When I run this query I get the following results:

Code:

BU  NumOpps  ValueOpps
--  -------  ---------
BU1 1        1000
BU2 2        5000

However, my instructions were changed; now I want a query that returns the number of opportunities and the sum of the PeakAnnRev of the VEHICLES associated with those opportunities, grouped by business Unit. If I simply change my query to use the other table, like this:

Code:

SELECT BU, Sum(1) as NumOpps, Sum(PeakAnnRev) as ValueOpps
FROM Sales_Vehicle
GROUP BY BU
ORDER BY BU

I get the following results:

Code:

BU  NumOpps  ValueOpps
--  -------  ---------
BU1 2        1100
BU2 3        5100

These are NOT the results I want. What I want is to get the NumOpps values from the first result set and the ValueOpps values from the second result set, like this:

Code:

BU  NumOpps  ValueOpps
--  -------  ---------
BU1 1        1100
BU2 2        5100

I tried various ways of doing this with no success. I guess I am not experienced enough in SQL to figure it out.

So, does anyone have any ideas of how I can write a query that will get me the results as I've described them? Thank you very much for your help!

Thanks,

clakerley

PHV · Mar 13, 2006

Something like this ?
SELECT O.BU, COUNT(DISTINCT O.OppID) AS NumOpps, SUM(V.PeakAnnRev) AS ValueOpps
FROM Sales_Opp O INNER JOIN Sales_Vehicle V
ON O.BU = V.BU AND O.OppID = V.OppID
GROUP BY O.BU
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

clakerley · Mar 13, 2006

Thanks for that! And while that can work for my simplified example, my actual solution will have to be different.

You see, my actual starting query is much more involved than the above examples. One of the things I am doing is showing the number of opportunities that are won or lost, and the win/loss rate for both the number of wins and the revenue. Perhaps a more involved example will illistrate my difficulty.

Each opportunity has a status of either Won or Lost. So, the tables now look like this:

Code:

Table: Sales_Opp
Fields:
   OppID,     : String
   BU,        : String
   PeakAnnRev : Double
   Status     : String

Table: Sales_Vehicle
Fields:
   OppID,     : String
   Vehicle    : String
   BU,        : String
   PeakAnnRev : Double
   Status     : String

Here is how the data can look:

Code:

Sales_Opp:
OppID  BU   PeakAnnRev  Status
-----  --   ----------  ------
Opp1   BU1  1000        Won
Opp2   BU2  2000        Lost
Opp3   BU2  3000        Won

Sales_Vehicle:
OppID  Vehicle  BU   PeakAnnRev  Status
-----  -------  --   ----------  ------
Opp1   Veh1     BU1   500        Won
Opp1   Veh2     BU1   600        Won
Opp2   Veh3     BU2  2000        Lost
Opp3   Veh4     BU2  2200        Won
Opp3   Veh5     BU2   900        Won

My query is similar to the following:

Code:

SELECT
BU,
(NumWon + NumLost) as NumOpps,
(ValueWon + ValueLost) as ValueLost,
NumWon,
ValueWon,
NumLost,
ValueLost,
(case when ((NumWon + NumLost) > 0) then (NumWon / (NumWon + NumLost) * 100.00000) else 0 end) as RateNumWon,
(case when ((ValueWon + ValueLost) > 0) then (ValueWon / (ValueWon + ValueLost) * 100.00000) else 0 end) RateValueWon,
FROM
(
   SELECT
   BU,
   sum(case when (Status = 'Won') then 1 else 0 end) as NumWon,
   Sum(case when (Status = 'Won') then PeakAnnRev else 0 end) as Valuewon,
   sum(case when (Status = 'Lost') then 1 else 0 end) as NumLost,
   Sum(case when (Status = 'Lost') then PeakAnnRev else 0 end) as ValueLost
   FROM Sales_Opp
   GROUP BY BU
   ORDER BY BU
)

And gives me results similar to the following:

Code:

BU  NumOpps  ValueOpps  NumWon  ValueWon  NumLost  ValueLost  RateNumWon  RateValueWon
--  -------  ---------  ------  --------  -------  ---------  ----------  ------------
BU1 1        1000       1       1000      0        0          100         100
BU2 2        5000       1       3000      1        2000        50          60

So, whatever solution I use needs to take into account that counts and sums are separated into different categories based on status.

I am sorry I didn't make this clearer in my first post. I had made my first post very simple to simplify my question and help posters figure out a viable solution more easily.

Thanks again,

clakerley

PHV · Mar 13, 2006

And with the new rules, what is the expected result ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

clakerley · Mar 13, 2006

The expected results would be:

Code:

BU  NumOpps  ValueOpps  NumWon  ValueWon  NumLost  ValueLost  RateNumWon  RateValueWon
--  -------  ---------  ------  --------  -------  ---------  ----------  ------------
BU1 1        1100       1       1100      0        0          100         100
BU2 2        5100       1       3100      1        2000        50          60.8

The Num fields would come from the Sales_Opp table, while the Value fields would come from the Sales_Vehicle table.

Thanks,

clakerley

PHV · Mar 13, 2006

Perhaps something like this ?
SELECT O.BU,
(NumWon + NumLost) as NumOpps,
(ValueWon + ValueLost) as ValueOpps,
NumWon, ValueWon, NumLost, ValueLost,
(case when ((NumWon + NumLost) > 0) then (NumWon / (NumWon + NumLost) * 100.00000) else 0 end) as RateNumWon,
(case when ((ValueWon + ValueLost) > 0) then (ValueWon / (ValueWon + ValueLost) * 100.00000) else 0 end) RateValueWon
FROM (
SELECT BU,
sum(case when (Status = 'Won') then 1 else 0 end) as NumWon,
sum(case when (Status = 'Lost') then 1 else 0 end) as NumLost,
FROM Sales_Opp
GROUP BY BU
) O INNER JOIN (
SELECT BU,
Sum(case when (Status = 'Won') then PeakAnnRev else 0 end) as ValueWon,
Sum(case when (Status = 'Lost') then PeakAnnRev else 0 end) as ValueLost
FROM Sales_Vehicle
GROUP BY BU
) V ON O.BU = V.BU
ORDER BY 1

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

clakerley · Mar 15, 2006

Thanks again for your help. I tried something like the above SQL statement, but I get an error complaining about "text after end of valid SQL statement," and it indicates that the error is on this line:

) O INNER JOIN (

Is the suggested SQL standard? Is it supposed to be supported by most vendors? Is there an alternative way of writing this for vendors that do not seem to support it?

I know this is a standard SQL forum but, if it helps, I am using Nexus Database Server 1.08. If I am allowed to post such things on this forum, I can post their web site address.

Thanks again,

clakerley

clakerley · Mar 15, 2006

If it helps, here is my actual, complete SQL statement that gives me the error:

Code:

SELECT
P.BusinessUnit,
(NumWon + NumLost + NumPending) as NumQuoted,
(ValueWon + ValueLost + ValuePending) as ValueQuoted,
NumWon,
ValueWon,
NumLost,
ValueLost,
NumPending,
ValuePending,
(case when ((NumWon + NumLost) > 0) then (NumWon / (NumWon + NumLost) * 100.00000) else 0 end) as RateNumWon,
(case when ((ValueWon + ValueLost) > 0) then (ValueWon / (ValueWon + ValueLost) * 100.00000) else 0 end) RateValueWon,
'01-01-2006 00:00:00' as StartDate,
'02-28-2006 23:59:59' as EndDate

FROM (
   SELECT
   BusinessUnit,
   sum(case when ("Business Activity" = 'Awarded') then 1 else 0 end) as NumWon,
   sum(case when ("Business Activity" = 'Lost') then 1 else 0 end) as NumLost,
   sum(case when ("Business Activity" = 'Quoted') then 1 else 0 end) as NumPending

   FROM
   "Profile Data"

   Where 
   Active = True 
   and ((("Business Activity" = 'Awarded') and 
         (("Award Date" >= '2006-01-01 00:00:00') and ("Award Date" <= '2006-02-28 23:59:59'))) OR 
        (("Business Activity" = 'Lost') and 
         (("LostTo Date" >= '2006-01-01 00:00:00') and ("LostTo Date" <= '2006-02-28 23:59:59'))) OR 
        (("Business Activity" = 'Quoted') and 
         (("QuoteDate" >= '2006-01-01 00:00:00') and ("QuoteDate" <= '2006-02-28 23:59:59'))))

   Group By 
   BusinessUnit 
) P INNER JOIN (
   SELECT
   BusinessUnit,
   sum(case when ("Business Activity" = 'Awarded') then
   (Case when ((TSWP.LYSales >= TSWP.CYSales) and
              (TSWP.LYSales >= TSWP.Y1Sales) and
              (TSWP.LYSales >= TSWP.Y2Sales) and
              (TSWP.LYSales >= TSWP.Y3Sales) and
              (TSWP.LYSales >= TSWP.Y4Sales) and
              (TSWP.LYSales >= TSWP.Y5Sales)) then TSWP.LYSales / 1000.00000
      when ((TSWP.CYSales >= TSWP.LYSales) and
            (TSWP.CYSales >= TSWP.Y1Sales) and
            (TSWP.CYSales >= TSWP.Y2Sales) and
            (TSWP.CYSales >= TSWP.Y3Sales) and
            (TSWP.CYSales >= TSWP.Y4Sales) and
            (TSWP.CYSales >= TSWP.Y5Sales)) then TSWP.CYSales / 1000.00000
      when ((TSWP.Y1Sales >= TSWP.LYSales) and
            (TSWP.Y1Sales >= TSWP.CYSales) and
            (TSWP.Y1Sales >= TSWP.Y2Sales) and
            (TSWP.Y1Sales >= TSWP.Y3Sales) and
            (TSWP.Y1Sales >= TSWP.Y4Sales) and
            (TSWP.Y1Sales >= TSWP.Y5Sales)) then TSWP.Y1Sales / 1000.00000
      when ((TSWP.Y2Sales >= TSWP.LYSales) and
            (TSWP.Y2Sales >= TSWP.CYSales) and
            (TSWP.Y2Sales >= TSWP.Y1Sales) and
            (TSWP.Y2Sales >= TSWP.Y3Sales) and
            (TSWP.Y2Sales >= TSWP.Y4Sales) and
            (TSWP.Y2Sales >= TSWP.Y5Sales)) then TSWP.Y2Sales / 1000.00000
      when ((TSWP.Y3Sales >= TSWP.LYSales) and
            (TSWP.Y3Sales >= TSWP.CYSales) and
            (TSWP.Y3Sales >= TSWP.Y1Sales) and
            (TSWP.Y3Sales >= TSWP.Y2Sales) and
            (TSWP.Y3Sales >= TSWP.Y4Sales) and
            (TSWP.Y3Sales >= TSWP.Y5Sales)) then TSWP.Y3Sales / 1000.00000
      when ((TSWP.Y4Sales >= TSWP.LYSales) and
            (TSWP.Y4Sales >= TSWP.CYSales) and
            (TSWP.Y4Sales >= TSWP.Y1Sales) and
            (TSWP.Y4Sales >= TSWP.Y2Sales) and
            (TSWP.Y4Sales >= TSWP.Y3Sales) and
            (TSWP.Y4Sales >= TSWP.Y5Sales)) then TSWP.Y4Sales / 1000.00000
      when ((TSWP.Y5Sales >= TSWP.LYSales) and
            (TSWP.Y5Sales >= TSWP.CYSales) and
            (TSWP.Y5Sales >= TSWP.Y1Sales) and
            (TSWP.Y5Sales >= TSWP.Y2Sales) and
            (TSWP.Y5Sales >= TSWP.Y3Sales) and
            (TSWP.Y5Sales >= TSWP.Y4Sales)) then TSWP.Y5Sales / 1000.00000
      else 0 End) else 0 end
   ) as ValueWon,
   sum(case when ("Business Activity" = 'Lost') then
   (Case when ((TSWP.LYSales >= TSWP.CYSales) and
              (TSWP.LYSales >= TSWP.Y1Sales) and
              (TSWP.LYSales >= TSWP.Y2Sales) and
              (TSWP.LYSales >= TSWP.Y3Sales) and
              (TSWP.LYSales >= TSWP.Y4Sales) and
              (TSWP.LYSales >= TSWP.Y5Sales)) then TSWP.LYSales / 1000.00000
      when ((TSWP.CYSales >= TSWP.LYSales) and
            (TSWP.CYSales >= TSWP.Y1Sales) and
            (TSWP.CYSales >= TSWP.Y2Sales) and
            (TSWP.CYSales >= TSWP.Y3Sales) and
            (TSWP.CYSales >= TSWP.Y4Sales) and
            (TSWP.CYSales >= TSWP.Y5Sales)) then TSWP.CYSales / 1000.00000
      when ((TSWP.Y1Sales >= TSWP.LYSales) and
            (TSWP.Y1Sales >= TSWP.CYSales) and
            (TSWP.Y1Sales >= TSWP.Y2Sales) and
            (TSWP.Y1Sales >= TSWP.Y3Sales) and
            (TSWP.Y1Sales >= TSWP.Y4Sales) and
            (TSWP.Y1Sales >= TSWP.Y5Sales)) then TSWP.Y1Sales / 1000.00000
      when ((TSWP.Y2Sales >= TSWP.LYSales) and
            (TSWP.Y2Sales >= TSWP.CYSales) and
            (TSWP.Y2Sales >= TSWP.Y1Sales) and
            (TSWP.Y2Sales >= TSWP.Y3Sales) and
            (TSWP.Y2Sales >= TSWP.Y4Sales) and
            (TSWP.Y2Sales >= TSWP.Y5Sales)) then TSWP.Y2Sales / 1000.00000
      when ((TSWP.Y3Sales >= TSWP.LYSales) and
            (TSWP.Y3Sales >= TSWP.CYSales) and
            (TSWP.Y3Sales >= TSWP.Y1Sales) and
            (TSWP.Y3Sales >= TSWP.Y2Sales) and
            (TSWP.Y3Sales >= TSWP.Y4Sales) and
            (TSWP.Y3Sales >= TSWP.Y5Sales)) then TSWP.Y3Sales / 1000.00000
      when ((TSWP.Y4Sales >= TSWP.LYSales) and
            (TSWP.Y4Sales >= TSWP.CYSales) and
            (TSWP.Y4Sales >= TSWP.Y1Sales) and
            (TSWP.Y4Sales >= TSWP.Y2Sales) and
            (TSWP.Y4Sales >= TSWP.Y3Sales) and
            (TSWP.Y4Sales >= TSWP.Y5Sales)) then TSWP.Y4Sales / 1000.00000
      when ((TSWP.Y5Sales >= TSWP.LYSales) and
            (TSWP.Y5Sales >= TSWP.CYSales) and
            (TSWP.Y5Sales >= TSWP.Y1Sales) and
            (TSWP.Y5Sales >= TSWP.Y2Sales) and
            (TSWP.Y5Sales >= TSWP.Y3Sales) and
            (TSWP.Y5Sales >= TSWP.Y4Sales)) then TSWP.Y5Sales / 1000.00000
      else 0 End) else 0 end
   ) as ValueLost,
   sum(case when ("Business Activity" = 'Quoted') then
   (Case when ((TSWP.LYSales >= TSWP.CYSales) and
              (TSWP.LYSales >= TSWP.Y1Sales) and
              (TSWP.LYSales >= TSWP.Y2Sales) and
              (TSWP.LYSales >= TSWP.Y3Sales) and
              (TSWP.LYSales >= TSWP.Y4Sales) and
              (TSWP.LYSales >= TSWP.Y5Sales)) then TSWP.LYSales / 1000.00000
      when ((TSWP.CYSales >= TSWP.LYSales) and
            (TSWP.CYSales >= TSWP.Y1Sales) and
            (TSWP.CYSales >= TSWP.Y2Sales) and
            (TSWP.CYSales >= TSWP.Y3Sales) and
            (TSWP.CYSales >= TSWP.Y4Sales) and
            (TSWP.CYSales >= TSWP.Y5Sales)) then TSWP.CYSales / 1000.00000
      when ((TSWP.Y1Sales >= TSWP.LYSales) and
            (TSWP.Y1Sales >= TSWP.CYSales) and
            (TSWP.Y1Sales >= TSWP.Y2Sales) and
            (TSWP.Y1Sales >= TSWP.Y3Sales) and
            (TSWP.Y1Sales >= TSWP.Y4Sales) and
            (TSWP.Y1Sales >= TSWP.Y5Sales)) then TSWP.Y1Sales / 1000.00000
      when ((TSWP.Y2Sales >= TSWP.LYSales) and
            (TSWP.Y2Sales >= TSWP.CYSales) and
            (TSWP.Y2Sales >= TSWP.Y1Sales) and
            (TSWP.Y2Sales >= TSWP.Y3Sales) and
            (TSWP.Y2Sales >= TSWP.Y4Sales) and
            (TSWP.Y2Sales >= TSWP.Y5Sales)) then TSWP.Y2Sales / 1000.00000
      when ((TSWP.Y3Sales >= TSWP.LYSales) and
            (TSWP.Y3Sales >= TSWP.CYSales) and
            (TSWP.Y3Sales >= TSWP.Y1Sales) and
            (TSWP.Y3Sales >= TSWP.Y2Sales) and
            (TSWP.Y3Sales >= TSWP.Y4Sales) and
            (TSWP.Y3Sales >= TSWP.Y5Sales)) then TSWP.Y3Sales / 1000.00000
      when ((TSWP.Y4Sales >= TSWP.LYSales) and
            (TSWP.Y4Sales >= TSWP.CYSales) and
            (TSWP.Y4Sales >= TSWP.Y1Sales) and
            (TSWP.Y4Sales >= TSWP.Y2Sales) and
            (TSWP.Y4Sales >= TSWP.Y3Sales) and
            (TSWP.Y4Sales >= TSWP.Y5Sales)) then TSWP.Y4Sales / 1000.00000
      when ((TSWP.Y5Sales >= TSWP.LYSales) and
            (TSWP.Y5Sales >= TSWP.CYSales) and
            (TSWP.Y5Sales >= TSWP.Y1Sales) and
            (TSWP.Y5Sales >= TSWP.Y2Sales) and
            (TSWP.Y5Sales >= TSWP.Y3Sales) and
            (TSWP.Y5Sales >= TSWP.Y4Sales)) then TSWP.Y5Sales / 1000.00000
      else 0 End) else 0 end
   ) as ValuePending

   FROM
   "Profile Data" PD 
   join "Mod Total Sales WO PF" TSWP on PD."Profile ID" = TSWP."Profile ID" 

   Where 
   Active = True 
   and ((("Business Activity" = 'Awarded') and 
         (("Award Date" >= '2006-01-01 00:00:00') and ("Award Date" <= '2006-02-28 23:59:59'))) OR 
        (("Business Activity" = 'Lost') and 
         (("LostTo Date" >= '2006-01-01 00:00:00') and ("LostTo Date" <= '2006-02-28 23:59:59'))) OR 
        (("Business Activity" = 'Quoted') and 
         (("QuoteDate" >= '2006-01-01 00:00:00') and ("QuoteDate" <= '2006-02-28 23:59:59'))))

   Group By 
   BusinessUnit 
) M on P.BusinessUnit = M.BusinessUnit

WHERE 
NumQuoted > 0 
and ValueQuoted > 0 

ORDER BY
P.BusinessUnit
;

Thanks,

clakerley

clakerley · Mar 15, 2006

The vendor just confirmed to me that my problem stems from a limitation of my older version of their database server. It works fine on their latest version; unfortunately for me, upgrading is not an option for me at the moment.

I'd still welcome any ideas for possible work-arounds!

Thanks again,

clakerley

clakerley · Mar 16, 2006

Hello everyone,

Well, I finally have a solution that works! A very helpful person at the database server's vendor suggested that I create two temp tables, one to hold the count data and the other to hold the amount data, and then query off of these temp tables. His solution looks similar to the following:

Code:

SELECT BU,
   sum(case when (Status = 'Won') then 1 else 0 end) as NumWon,
   sum(case when (Status = 'Lost') then 1 else 0 end) as NumLost
INTO #O_TEMP
FROM Sales_Opp
GROUP BY BU;

SELECT BU,
  Sum(case when (Status = 'Won') then PeakAnnRev else 0 end)
    as ValueWon,
  Sum(case when (Status = 'Lost') then PeakAnnRev else 0 end)
    as ValueLost
INTO #V_TEMP
FROM Sales_Vehicle
GROUP BY BU;

SELECT O.BU,
(NumWon + NumLost) as NumOpps,
(ValueWon + ValueLost) as ValueOpps,
NumWon, ValueWon, NumLost, ValueLost,
(case when ((NumWon + NumLost) > 0) then (NumWon / (NumWon + NumLost) *
100.00000) else 0 end) as RateNumWon,
(case when ((ValueWon + ValueLost) > 0) then (ValueWon / (ValueWon +
ValueLost) * 100.00000) else 0 end) RateValueWon
FROM #O_TEMP O
INNER JOIN #V_TEMP V ON O.BU = V.BU;

Technically, I suppose this is really three SQL statements rather than one, but I am able to run it all together within the same query object within my code, which is just what I needed. I suppose if I were more of an SQL guru I would have come up with this myself; anyway, I hope someone finds this thread as useful as I did!

Thanks again!

clakerley

ddiamond · Mar 22, 2006

Just out of curiosity, what database server are you using and what programming language are you using?

clakerley · Mar 22, 2006

Hello ddiamond,

I am using Nexus Database Server version 1.08, and I am using Borland Delphi 7 as my programming language.

Thanks,

clakerley

ddiamond · Mar 23, 2006

The reason I asked is because I was impressed by your statement "I am able to run it all together within the same query object within my code." If you were using a similar platform as me, I would ask you how you did this, but I don't think your solution would apply to me since I develop in C# against DB2 and Oracle. You may want to post your code anyway in case another Delphi or Nexus programmer reads this.

clakerley · Mar 27, 2006

I use a function similar to the following:

Code:

//receives a query object & a query string, does the work to run string in query object    
Function RunQuery(QueryObject: TnxQuery; QueryString : String): Boolean;
begin
   Result := False;
   try
      with QueryObject do
      begin
         Close;
         Unprepare;
         Sql.Clear;
         Sql.Add(QueryString);
         if FDebug then
            Sql.SaveToFile('QueryRun.txt');
         Prepare;
         Open;
         Result := True;
      end;
   except
      on E:Exception do ShowMessage(e.Message+#13+queryString);
   end;
end;

The proprietary class TnxQuery is a TQuery-compatible implementation of a Nexus SQL query descended from the proprietary class TnxDataset. The proprietary class TnxDataset is descended from TDataset and is the base class for all TDataset-compatible Nexus components. TQuery and TDataset, of course, are classes from Delphi's own library.

I simply did the proper initialization of my QueryObject and passed the text of my SQL statement in for my QueryString. In this case my QueryString text consisted of three SQL statements, each ending with a semicolon, concatenated together. I don't know if this would work with a standard Delphi TQuery object or if Nexus implements something that gets this to work.

As I noted in my earlier post, my solution is really a work-around; according to the people at Nexus, the single SQL statement solution presented earlier works on the latest version of their DB.

Thanks,

clakerley

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Aggregate Data from Two Tables

clakerley

Programmer

PHV

MIS

clakerley

Programmer

PHV

MIS

clakerley

Programmer

PHV

MIS

clakerley

Programmer

clakerley

Programmer

clakerley

Programmer

clakerley

Programmer

ddiamond

Programmer

clakerley

Programmer

ddiamond

Programmer

clakerley

Programmer

Similar threads

Part and Inventory Search

Sponsor