>SQL.Add('Customer.City +' + QuotedStr(', ') + '+ Customer.State AS ...
>
>Please note the 2 additional "+".

thanks Fernando but

SQL.Add('Customer.City +' + QuotedStr(', ') + '+ Customer.State AS
LastAddressLine ');

gives error also

Pat

"Pat" <pat@downunder.com> wrote in message
news:81vjs3dcupp2dsteitfaj435f1g5bhmvu6@4ax.com...
>
> I did that in my initial troubleshooting but was not sure how it
> should 'look'. At present the SQL.Test looks like
>
> Customer.City ', ' Customer.State AS LastAddressLine
>
> as seen in the attached Error.jpg
>
>>Hint2: Develop queries in DBSYS, and then cut and paste into the SQL.Add
>>statements.
>
> I did that also and the following works fine
>
> SELECT COrders.OrderNumber, COrdersDetails.OrderNumberDetailID,
> COrdersDetails.StockType, COrdersDetails.StockNumber,
> COrdersDetails.OrderedQuantity, Customer.CustomerNo,
> Customer.CustomerName, Customer.CustomerDepartment,
> Customer.AddressLine1, Customer.AddressLine2,
> (Customer.City + ', ' + Customer.StateProv + ', ' + Customer.ZipCode )
> AS LastAddressLine

This is not the same as what you listed above. As somebody else suggested,
you need the + signs BOTH outside of the SQL, so that Delphi can concatenate
the three strings that end up making the single SQL string. and also inside
the SQL, so that at execution SQL can concatenate the database  fields.

Sql.Add('Field1 +' + 'Field2 + ' + 'Field3');

the string inside SQL will be 'Field1 + Field2 + Field3' without the quotes,
which I think is what you want.

Robert

>
> FROM (COrders LEFT JOIN COrdersDetails ON COrders.OrderNumber =
> COrdersDetails.OrderNumber)
> LEFT JOIN Customer ON COrders.CustomerNumber = Customer.CustomerNo
>
> WHERE COrders.OrderNumber = '123'
> ORDER BY COrdersDetails.OrderNumberDetailID
>
> Thanks,
> Pat

Pat

What error do you see?
Can you post all sql statement and the error message?

--
Fernando Dias

Pat escreveu:
>> SQL.Add('Customer.City +' + QuotedStr(', ') + '+ Customer.State AS ...
>>
>> Please note the 2 additional "+".
>
> thanks Fernando but
>
> SQL.Add('Customer.City +' + QuotedStr(', ') + '+ Customer.State AS
> LastAddressLine ');
>
>  gives error also
>
> Pat
>

Pat

The following has *got* to do what you want (all quotes are single
ones):

SQL.Add('Customer.City + '', '' + ' Customer.State AS
LastAddressLine,');

or, as Roy, etc suggest:

SQL.Add('Customer.City + ' + QuotedStr(', ') + ' + Customer.State AS
LastAddressLine,');

I use these constructs all the time.  If you still get an error, are
you sure it is on this line and are you sure you do not have a typo?  I
have a piece of boilerplate code I stick at the end of all my .SQL
creation blocks when developing:

   if DirectoryExists(DataPath + 'Debug') then
     SomeQuery.SQL.SaveToFile(DataPath + 'Debug\debug.sql');

This dumps the generated SQL to a file so you can see what is created
at run time .. and, if necessary, paste it into DBSys for debugging.

If the above SQL still does not 'run', please capture the actual SQL
created and paste (not type) it into a post here.  

Malcolm
--

Fernando


That's the bit my tired old eyes missed.

Roy Lambert

Hi all,

thanks VERY much for the input, still not going as yet so the problem
must be at my end.

What I will do, first thing tomorrow, is just make a 3 field table and
try to get the SQL working with that first (following the KISS
principle).

Pat

Pat


Post the tables and sql to the binaries

Roy Lambert

OK finally got it going.

BIG THANKS for all that helped  

================================
working SQL:

    begin
     qryPickList.Close;
     SQL.Clear;
     SQL.Add('SELECT COrders.OrderNumber,
COrdersDetails.OrderNumberDetailID, COrdersDetails.StockType,
COrdersDetails.StockNumber, COrdersDetails.OrderedQuantity,
Customer.CustomerNo, ');
     SQL.Add('Customer.CustomerName, Customer.CustomerDepartment,
Customer.AddressLine1, Customer.AddressLine2, ');

     SQL.Add('Customer.City + ' + QuotedStr(', ') + ' +
Customer.StateProv + ' + QuotedStr(', ') + ' + Customer.ZipCode AS
LastAddressLine');

     SQL.Add('FROM (COrders LEFT JOIN COrdersDetails ON
COrders.OrderNumber = COrdersDetails.OrderNumber) ');
     SQL.Add('LEFT JOIN Customer ON COrders.CustomerNumber =
Customer.CustomerNo  ');
     SQL.Add('WHERE COrders.OrderNumber = :TheOrderNumber');
     SQL.Add('ORDER BY COrdersDetails.OrderNumberDetailID');
     ParamByName('TheOrderNumber').AsString :=
tblCOrdersOrderNumber.Value;
     qryPickList.ExecSQL;
    end;
================================

resultant SQL for DBSYS testing

SELECT COrders.OrderNumber, COrdersDetails.OrderNumberDetailID,
COrdersDetails.StockType, COrdersDetails.StockNumber,
COrdersDetails.OrderedQuantity, Customer.CustomerNo,
Customer.CustomerName, Customer.CustomerDepartment,
Customer.AddressLine1, Customer.AddressLine2,
Customer.City + ', ' + Customer.StateProv + ', ' + Customer.ZipCode AS
LastAddressLine
FROM (COrders LEFT JOIN COrdersDetails ON COrders.OrderNumber =
COrdersDetails.OrderNumber)
LEFT JOIN Customer ON COrders.CustomerNumber = Customer.CustomerNo  
WHERE COrders.OrderNumber = :TheOrderNumber
ORDER BY COrdersDetails.OrderNumberDetailID
================================

Malcolm

> I have a piece of boilerplate code I stick at the end of all my .SQL
>creation blocks when developing:
>
>    if DirectoryExists(DataPath + 'Debug') then
>      SomeQuery.SQL.SaveToFile(DataPath + 'Debug\debug.sql');
>
>This dumps the generated SQL to a file so you can see what is created
>at run time .. and, if necessary, paste it into DBSys for debugging.

thanks for that, appreciate it, it helped in the debugging

Pat