I am trying to use the sql union function and am getting a access
violation 4C5F3168 involving the IDSQL32.dll.  The file input and out
put for both sections of the sql are the same.  They run fine
independently.  All I did was to take a working sql and copy it, change
some of the selection parameters and add a 'sql.add('union')'
statement.  Any suggestions?  Thanks

It has to to with including conditional statements in the sql.  When I
include the following selection criteria  in both sections of the union, I
get the error.  If I comment it out of the 'union' section, runs OK.
However, I need the selection criteria in both sections.

        if gsRecord.gsSalesman <> '' then
        begin
          sql.add('and customer.salesman = :sqlSalesman');
          parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
        end;

Let us see the full code.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Code follows:

    // get year budgets
    decodeDate(r8PeriodEnd,yyear2,mmon2,dday2);
    decodeDate(r8YearBegin,yyear,mmon,dday);
    close;
    sql.clear;
    if mmon <> mmon2 then
    begin
      sql.add('select customer,grossSales,salesman');
      sql.add('from budget,customer');
      sql.add('where customer.code = budget.customer');
      sql.add('and budget.''year'' = :sqlyear');
      parambyname('sqlyear').asInteger := yyear;
      sql.add('and budget.''month'' >= :sqlmon');
      parambyname('sqlmon').asInteger := mmon;
      sql.add('and budget.''month'' <= :sqlmon2');
      if mmon < mmon2 then
        parambyname('sqlmon2').asInteger := 12
      else
        parambyname('sqlmon2').asInteger := mmon2-1;
      if gsRecord.gsSalesman <> '' then
      begin
        sql.add('and customer.salesman = :sqlSalesman');
        parambyname('sqlSalesman').asString := gsRecord.gsSalesman;
      end;
      if gsRecord.gsCustomer <> '' then
      begin
        sql.add('and budget.Customer = :sqlCustomer');
        parambyname('sqlCustomer').asString := gsRecord.gsCustomer;
      end;
      if mmon < mmon2 then
      begin
        sql.add('union');
        sql.add('select customer,grossSales,salesman');
        sql.add('from budget,customer');
        sql.add('where customer.code = budget.customer');
        sql.add('and budget.''year'' = :sqlyear');
        parambyname('sqlyear').asInteger := yyear2;
        sql.add('and budget.''month'' >= 1');
        sql.add('and budget.''month'' <= :sqlmon2');
        parambyname('sqlmon2').asInteger := mmon2-1;
        if gsRecord.gsSalesman <> '' then
        begin
          sql.add('and customer.salesman = :sqlSalesman2');
          parambyname('sqlSalesman2').asString := gsRecord.gsSalesman;
        end;
        if gsRecord.gsCustomer <> '' then
        begin
          sql.add('and budget.Customer = :sqlCustomer2');
          parambyname('sqlCustomer2').asString := gsRecord.gsCustomer;
        end;
      end;
      open;

Hi,

Ups, my last post was corrupt.
(I've tested a new Newsreader... failed :-)

I repeat me:
As far as I know, you can not use params for UNION statements - at least
for Paradox. I do not know the reason for this restriction.

Furthermore, why do you use an UNION statement here? You should get the same
result set using a WHERE expression with OR-clauses.

SELECT customer, grossSales, salesman
FROM budget, customer
WHERE (customer.code = budget.customer)
  AND (<condition of select 1> OR <condition of select 2>)

Regards,
Marco

My guess is that the error is occurring when the query is not complete when you
set parameters.

Try changing the above logic so that the setting the two parameter values comes
after the two SQL Add

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Are you saying that it should work?  What about Marco's comment above?

I am taking a guess at what the problem is.  The code is a bit too strange to
declare working with out testing it

What about it?  He is wrong about being able to use parameters with a Union
query

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

:-)

You are right. Someone has told me garbage. I've tested it with the demo tables
and it works fine.

with Query1 do begin
  SQL.Text := 'SELECT * FROM "VENUES.DB" Venues, "EVENTS.DB" Events';
  SQL.Add('WHERE (Events.VenueNo = Venues.VenueNo) AND (Events.VenueNo =
:Param0)');
  ParamByName('Param0').AsSmallInt := 2;
  SQL.Add('UNION');
  SQL.Add('SELECT * FROM "VENUES.DB" Venues, "EVENTS.DB" Events');
  SQL.Add('WHERE (Events.VenueNo = Venues.VenueNo) AND (Events.VenueNo =
:Param1)');
  ParamByName('Param1').AsSmallInt := 5;
  Open;
end;

Sorry for the disorientation, Rob.

Regards,
Marco

I have simplified the code.  I should run against any table with the appropriate
field and table names.  Note the assignment of  'wsalesman' at the beginning of
the procedure.  If this is not blank, I get the error.  If I make wsalesman = ''
then the code runs ok.

procedure TForm1.Button12Click(Sender: TObject);
var
  wsalesman: string;
begin
  wsalesman := 'HANS';
  mmon := 10;

  with Query1 do
  begin
    close;
    sql.clear;
    sql.add('select code');
    sql.add('from customer');
    if mmon = 10 then
    begin
      sql.add('union');
      sql.add('select code');
      sql.add('from customer');
      sql.add('where code < ''4000'''); // any where statement so 'and'
conditional can be used
      if wSalesman <> '' then
      begin
        sql.add('and customer.salesman = :sqlSalesman');
        parambyname('sqlSalesman').asString := wsalesman;
      end;
    end;
    open;
  end;
  screen.cursor := crDefault;
end;

Rob

It helps to have clear code that I can use with out a lot of work at my end.
I tested your code after adding Code and Salesman to an exiting Customer table.
When I test it works with no errors.
No where in this thread can I find what version of Delphi you are using So I
assume it is 4.  I am testing with Delphi 4 all patches and BDE updates
installed.   So if you have not installed patch 3 and after that the latest BDE
update I suggest you try that.
Then if you still have a problem create a small extract of the table you are
testing with and try that.  If it fails Email me a copy along with a copy of
this message.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

I'm using D3.

Also, BDE 451.

If you don't learn to provide that information up front you waste a lot of time
for everyone.

I tested with Delphi 3 and BDE 5.1 and had no problem.
Looks like it is time for you to try an updated BDE but you better make sure you
copy of Delphi 3 is update too.

--
Brian Bushay (TeamB)
Bbus...@NMPLS.com

Yes, sorry.  I get focused on what I am doing and forget there's a world out there.
Appreciate the help.  Installed 5.10 and it runs.  Any problems going 4.xx to 5.10?