On Wed, Apr 16, 2014 at 11:37 PM, Andrew Stirling wrote:
> Thanks Peter & All
> Got it to work with a ALLTRIM:
> WHERE NOT (( ALLTRIM(compno)$mystring ) )
I suppose compno is a column in a table. Does it have an index?
alltrim(compno) would break Rushmore optimization, making the index not usabl
On Wed, Apr 16, 2014 at 2:27 PM, Richard Kaye wrote:
> I resemble that remark...
>
> But seriously, even if you are using a different backend, the ability to
> leverage VFPs DML can be a lot more efficient than figuring out how to
> write that uber query against your enterprise DBMS. At least fo
-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of
mbsoftwaresoluti...@mbsoftwaresolutions.com
Sent: Wednesday, April 16, 2014 2:54 PM
To: profoxt...@leafe.com
Subject: RE: SQL Query
On 2014-04-16 14:08, Richard Kaye wrote:
>
> I was also sayin
On 2014-04-16 14:08, Richard Kaye wrote:
My comment/question about optimization was VFP specific, Mike. Are you
bathing again?
LOL!!!
I was also saying that using both distinct and group by in a query is
potentially redundant, particularly with a single column query, and
that all other thi
scan while a group may
use an index. In VFP...
--
rk
-Original Message-
From: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of
mbsoftwaresoluti...@mbsoftwaresolutions.com
Sent: Wednesday, April 16, 2014 2:02 PM
To: profoxt...@leafe.com
Subject: RE: SQL Query
I've got a
On Wed, Apr 16, 2014 at 1:02 PM, <
mbsoftwaresoluti...@mbsoftwaresolutions.com> wrote:
> I've got a very large MariaDB (MySQL) table and ran an EXPLAIN on a GROUP
> BY query and a separate DISTINCT query on an indexed field. Both came back
> the same in terms of optimization. ymmv.
>
> -
I've got a very large MariaDB (MySQL) table and ran an EXPLAIN on a
GROUP BY query and a separate DISTINCT query on an indexed field. Both
came back the same in terms of optimization. ymmv.
On 2014-04-16 12:21, Richard Kaye wrote:
I was just throwing out some options that came off the top o
: ProfoxTech [mailto:profoxtech-boun...@leafe.com] On Behalf Of Dave Crozier
Sent: Wednesday, April 16, 2014 11:33 AM
To: profoxt...@leafe.com
Subject: RE: SQL Query
Richard
More efficient this way if there ate multiple instance records in the compno
field
Select columns from table
Where compno not
Thanks Peter & All
Got it to work with a ALLTRIM:
WHERE NOT (( ALLTRIM(compno)$mystring ) )
Kind regards
Andrew Stirling
01250 874580
supp...@calcpay.co.uk
http://www.calcpay.co.uk
On 16/04/2014 16:17, Peter Cushing wrote:
Andrew Stirling wrote:
Hi
I have a table with a column compno which
ge-
From: ProFox [mailto:profox-boun...@leafe.com] On Behalf Of Richard Kaye
Sent: 16 April 2014 16:19
To: profox@leafe.com
Subject: RE: SQL Query
You don't say how many values you want to exclude. If there are only a few you
can do something like:
Select columns from table
Where compno
Andrew, you didn't specify which database platform you're using. Peter's
example will work fine with VFP, but in the off chance you're using
another SQL platform, they typically use the 'IN' or 'NOT IN' clause.
SELECT ... WHERE compno NOT IN ('Comp2','Comp4','Comp99')
HTH.
On 04/16/2014 11:17
You don't say how many values you want to exclude. If there are only a few you
can do something like:
Select columns from table
Where compno not in ('value1','value2',...)
Or
Select columns from table
Where compno not in
(select compno from table
Where compno='value1' or compno='value2' or.
Andrew Stirling wrote:
Hi
I have a table with a column compno which has
Comp1
Comp2
Comp3
etc
I have got a string, tempexclude with 'Comp2,Comp4,Comp99' in it.
How do I get a SELECT to exclude the string values?
select ... where !(compno $ mystring)
Peter
.
This communication is intended
At 00:00 2013-10-10, Alan Bourke wrote:
SQL in general is well designed. The Visual FoxPro implementation is
lacking in some areas.
Ha! Even Codd disclaimed it.
Sincerely,
Gene Wirchenko
___
Post Messages to: ProFox@leafe.com
Subscription M
On Wed, Oct 9, 2013 at 5:50 PM, Gene Wirchenko wrote:
>
> I will stick with SQL not being very well-designed. For example,
> select joincolumn, othercolumn1, othercolumn2;
> from tableone join tabletwo;
> on tableone.joincolumn=tabletwo.**joincolumn;
>
SQL in general is well designed. The Visual FoxPro implementation is
lacking in some areas.
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list: http://mail.leafe.com
At 13:40 2013-10-08, Stephen Russell wrote:
On Tue, Oct 8, 2013 at 2:56 PM, Gene Wirchenko wrote:
> What did end up working was creating a aggregate cursor of catx then
> selecting out only what I wanted. I probably lost a good optimisation
> possibility, but I got my answers.
>
> S
At 13:11 2013-10-08, Ted Roche wrote:
On Tue, Oct 8, 2013 at 3:52 PM, Gene Wirchenko wrote:
> I tried that, and it did not work. Now, ten WFCs are reported, one
> having a zero storageuse, but the other three are still not there.
> Puzzling.
Well, no, it's probably that the other th
On Tue, Oct 8, 2013 at 2:56 PM, Gene Wirchenko wrote:
>
> What did end up working was creating a aggregate cursor of catx then
> selecting out only what I wanted. I probably lost a good optimisation
> possibility, but I got my answers.
>
> SQL is not very well designed.
>
-
On Tue, Oct 8, 2013 at 3:56 PM, Gene Wirchenko wrote:
>
> SQL is not very well designed.
>
Ha, ha, ha, ha !
I guess that's why it's never caught on, and we all use FoxPro syntax for
data manipulation.
--
Ted Roche
Ted Roche & Associates, LLC
http://www.tedroche.com
--- StripMime Repor
On Tue, Oct 8, 2013 at 3:52 PM, Gene Wirchenko wrote:
>
> I tried that, and it did not work. Now, ten WFCs are reported, one
> having a zero storageuse, but the other three are still not there.
> Puzzling.
>
>
Well, no, it's probably that the other three WFCs that were not invoiced
are al
At 12:44 2013-10-08, Ted Roche wrote:
On Tue, Oct 8, 2013 at 3:25 PM, Dan Covill wrote:
> The simplest way I can think of to do that is to have a separate 1-field
> table of valid WFC's. Do your count(WFC) query, then match it to the
> ValidWFC table and report the zeroes as well as your coun
At 12:25 2013-10-08, Dan Covill wrote:
On 10/08/13 11:54 AM, Gene Wirchenko wrote:
I would like to see the number of uses of each WFC even if it is
zero. How do I do this last bit? I have a query, but it does not
report any of the unused WFCs.
The simplest way I can think of to do tha
At 12:12 2013-10-08, Ted Roche wrote:
On Tue, Oct 8, 2013 at 2:54 PM, Gene Wirchenko wrote:
> I would like to see the number of uses of each WFC even if it is
> zero.
If you're filtering on the table that could be NULL (the catx table), you
need to ensure you don't filter out the results
On Tue, Oct 8, 2013 at 3:25 PM, Dan Covill wrote:
>
> The simplest way I can think of to do that is to have a separate 1-field
> table of valid WFC's. Do your count(WFC) query, then match it to the
> ValidWFC table and report the zeroes as well as your counts.
> Assuming WFC's get validated, you
On 10/08/13 11:54 AM, Gene Wirchenko wrote:
I would like to see the number of uses of each WFC even if it is
zero. How do I do this last bit? I have a query, but it does not
report any of the unused WFCs.
The simplest way I can think of to do that is to have a separate 1-field
table of
On Tue, Oct 8, 2013 at 2:54 PM, Gene Wirchenko wrote:
>
> I would like to see the number of uses of each WFC even if it is
> zero.
If you're filtering on the table that could be NULL (the catx table), you
need to ensure you don't filter out the results. Change the second line of
the WHERE
L PROTECTED]
Subject: RE: SQL Query
JOIN clauses work with ON clauses to get better control of what how the data
is being joined.
This LEFT JOIN forces all records from the POL table. When no matching
record exist in the PROCESS table, the values are .NULL..
Tracy
-Original Message-
From
JOIN clauses work with ON clauses to get better control of what how the data
is being joined.
This LEFT JOIN forces all records from the POL table. When no matching
record exist in the PROCESS table, the values are .NULL..
Tracy
-Original Message-
From: Francis I. Coppage, Jr.
Sent: Frid
x27;s from other orderno's associated to the
wrong
orderno.
Would you mind?
Thanks,
Francis
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On
Behalf Of Tracy Pearson
Sent: Friday, December 05, 2008 1:29 PM
To: [EMAIL PROTECTED]
Subject: RE: SQL Query
Perhap
Perhaps this:
Select po.orderno, pol.lineitem ;
From po inner join pol on po.orderno == pol.orderno ;
Where po.orderno not in ;
(select pol.orderno from pol left join process ;
On pol.lineitem == process.sequence ;
Where isnull(process.orderno))
-Original Message-
From: Fr
Given the example Tristan gave then you could have tables two and/or
three with more records than table one and so laft outers wont work.
With no context given I added the restriction.
--
Regards
Michael.
[EMAIL PROTECTED]
___
Post Messages to:
Tristan Leask <> wrote:
> Hi Guys,
>
> It's a Monday morning and my brain isn't working properly (surprise,
> surprise!)
>
> Anyway, I have 3 tables that I want to join together.
>
> Table 1 has 1 record =
>
> "Table1 My data"
>
> Table 2 has 1 record =
>
> "Table2 My data"
>
> T
Michael Hawksworth <> wrote:
> As long as you know that table one has all the records you need then
> two and thress should be left outer joins.
What?
Select Customers.Name, Invoices.InvNo, Invoices.OrderDate, Invoices.
>From Customers
Left join Invoices on Customers.CustNo = Invoices.CustN
As long as you know that table one has all the records you need then two
and thress should be left outer joins.
--
Regards
Michael.
[EMAIL PROTECTED]
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://leafe.com/mailman/listinf
35 matches
Mail list logo