On Wed, Apr 16, 2014 at 11:37 PM, Andrew Stirling supp...@calcpay.co.uk 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
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
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'
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
[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 not in ('value1','value2
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
: 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
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
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.
scan while a group may
use an index. In VFP... g
--
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
On 2014-04-16 14:08, Richard Kaye wrote:
My comment/question about optimization was VFP specific, Mike. Are you
bathing again? g,dr
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
-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 saying
On Wed, Apr 16, 2014 at 2:27 PM, Richard Kaye rk...@invaluable.com wrote:
I resemble that remark... g
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
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:
On Wed, Oct 9, 2013 at 5:50 PM, Gene Wirchenko ge...@telus.net 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;
At 00:00 2013-10-10, Alan Bourke alanpbou...@fastmail.fm 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:
At 13:11 2013-10-08, Ted Roche tedro...@gmail.com wrote:
On Tue, Oct 8, 2013 at 3:52 PM, Gene Wirchenko ge...@telus.net 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,
At 13:40 2013-10-08, Stephen Russell srussell...@gmail.com wrote:
On Tue, Oct 8, 2013 at 2:56 PM, Gene Wirchenko ge...@telus.net 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
On Tue, Oct 8, 2013 at 2:54 PM, Gene Wirchenko ge...@telus.net 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
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
On Tue, Oct 8, 2013 at 3:25 PM, Dan Covill dcov...@san.rr.com 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
At 12:12 2013-10-08, Ted Roche tedro...@gmail.com wrote:
On Tue, Oct 8, 2013 at 2:54 PM, Gene Wirchenko ge...@telus.net 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
At 12:25 2013-10-08, Dan Covill dcov...@san.rr.com 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
At 12:44 2013-10-08, Ted Roche tedro...@gmail.com wrote:
On Tue, Oct 8, 2013 at 3:25 PM, Dan Covill dcov...@san.rr.com 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
On Tue, Oct 8, 2013 at 3:52 PM, Gene Wirchenko ge...@telus.net 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
On Tue, Oct 8, 2013 at 3:56 PM, Gene Wirchenko ge...@telus.net 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
---
On Tue, Oct 8, 2013 at 2:56 PM, Gene Wirchenko ge...@telus.net 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.
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:
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
Perhaps this:
Select
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:
]
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: Francis I
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:
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.CustNo
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
Table 3 has 2 records
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:
35 matches
Mail list logo
