I have a ledger table with about 23,000 records and a category table with
about 150 records. Both tables have an integer iCat_Id field which I use in
the following code:
SELECT led.iCat_Id, ddate, Led.iCat_Id, cCategory;
FROM Led, Cat;
WHERE Cat.iCat_Id = Led.iCat_Id
The select comes back
Joe,
I wouldn't leave fields ddate and cCategory without 'Cat.' and if you are under
VFP9, I'll write
SELECT led.iCat_Id, cat.ddate, Led.iCat_Id, cat.cCategory ;
FROM cat INNER JOIN led ON cat.icat_id = led.icat_id ;
INTO CURSOR myresult
You can detect if the same icat_id belongs to
Joe Yoder wrote on 2015-05-18:
I have a ledger table with about 23,000 records and a category table with
about 150 records. Both tables have an integer iCat_Id field which I use
in
the following code:
SELECT led.iCat_Id, ddate, Led.iCat_Id, cCategory;
FROM Led, Cat;
WHERE
On Mon, May 18, 2015 at 3:05 PM, Tracy Pearson tr...@powerchurch.com wrote:
I believe by default the FROM Led, Cat is an implicit LEFT JOIN. You'll get
all the records from LED, and only matching records from CAT. If the second
iCat_Id was pulling from Cat, it would probably be blank the same
Ted Roche wrote on 2015-05-18:
On Mon, May 18, 2015 at 3:05 PM, Tracy Pearson tr...@powerchurch.com
wrote:
I believe by default the FROM Led, Cat is an implicit LEFT JOIN. You'll
get
all the records from LED, and only matching records from CAT. If the
second
iCat_Id was pulling from
Tracy,
the best way to be sure is to write INNER, LEFT OUTER or RIGHT OUTER in each
query !!
The Foxil
___
Post Messages to: ProFox@leafe.com
Subscription Maintenance: http://mail.leafe.com/mailman/listinfo/profox
OT-free version of this list:
Jean MAURICE wrote on 2015-05-18:
Tracy,
the best way to be sure is to write INNER, LEFT OUTER or RIGHT OUTER in
each
query !!
The Foxil
Jean,
True, this is my habit.
Not doing that leaves ambiguity when reading the code, and running the code
can have different results in
Joe:
So, here's what you've told us:
CREATE TABLE led (iledpk i autoinc, ddate d, icat_id i)
CREATE TABLE cat (icat_id i autoinc, cCategory c(20))
FOR i = 1 TO 150
INSERT INTO cat (cCategory) VALUES (Category + LTRIM(STR(i)))
NEXT
FOR i = 1 TO 200
FOR j = 1 TO 150
INSERT INTO led
Thanks Ted and all for the help. What I was overlooking is the fact that I
was working with tables with long file names. What was happening is that
the cCategory field is actually in the Led table rather than the Cat table
and the field I actually want is the cCat_Desc field in the Cat table. I
It's not like we all haven't done it.
I often alias ALL the fields in a SELECT statement, at least when I'm
starting out, to ensure I am grabbing the fields from the tables I
think I am. When I'm naming fields I tend to go back to the same
names, so it's not unusual to have an lActive in each
You caught me Gene. I had changed it after copying/pasting. Problem
solved though. Thanks anyway.
On 2014-06-06 12:26, Gene Wirchenko wrote:
At 08:44 2014-06-05, mbsoftwaresoluti...@mbsoftwaresolutions.com wrote:
[snip]
INSERT INTO Test VALUES (7, 6, DATE(2013,1,1), DATE(2013,1,11),
At 08:44 2014-06-05, mbsoftwaresoluti...@mbsoftwaresolutions.com wrote:
[snip]
INSERT INTO Test VALUES (7, 6, DATE(2013,1,1), DATE(2013,1,11),
DATE(2013,6,1), '9', 'A2') we want this ClaimID
Here you state you want ClaimID 7 ...
[snip]
A complete claim set is defined by the PCN,
Here's the code:
CLEAR ALL
CLOSE ALL
CREATE CURSOR Test (ClaimID i, PCN i, Admit d, Disch d, Paid d, ProvNum
c(9), ICN c(11))
INSERT INTO Test VALUES (56, 6, DATE(2013,1,1), DATE(2013,1,11),
DATE(2013,5,1), '9', 'A1')
INSERT INTO Test VALUES (3, 6, DATE(2013,1,1), DATE(2013,1,11),
Are you only looking for a Max() for ICN, A3 being the maximum? Your data
shows A2 as the data you want but your explanation of expected results has
the value for A3.
Select PCN, Max(ICN) from test
group by PCN
order by PCN
as a real quick method to do this.
On Thu, Jun 5, 2014 at 10:44
I think I've got it!!! (ClaimID is unique, btw)
SELECT A1.*
FROM Test A1
WHERE A1.ClaimID = (select TOP 1 ClaimID
FROM Test B1
WHERE A1.PCN = B1.PCN AND A1.Admit = B1.Admit AND A1.Discharge =
B1.Discharge AND A1.ProvNum = B1.ProvNum
Whoops forgot the wrapper for the data.
Select PCN, Max(ICN)
into #t1
from test
where clause here
group by PCN
order by PCN
Select ClaimID , #t1.PCN from Test
inner join #t1
on test.ICN = #t1.ICN
On Thu, Jun 5, 2014 at 11:25 AM, Stephen Russell srussell...@gmail.com
wrote:
Are you only
mbsoftwaresoluti...@mbsoftwaresolutions.com wrote on 2014-06-05:
Here's the code:
CLEAR ALL
CLOSE ALL
CREATE CURSOR Test (ClaimID i, PCN i, Admit d, Disch d, Paid d, ProvNum
c(9), ICN c(11))
INSERT INTO Test VALUES (56, 6, DATE(2013,1,1), DATE(2013,1,11),
DATE(2013,5,1),
Email List'
Subject: RE: Interesting SQL Puzzle
Yes, this works out the same. You would just pull both columns
state+neighbor instead of relation.
John
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf
Of Frank Burcaw
Sent: Monday, June 26, 2006 1:42 PM
To: profox
] [mailto:[EMAIL PROTECTED] On Behalf
Of Dave Crozier
Sent: Monday, June 26, 2006 11:36 PM
To: 'ProFox Email List'
Subject: RE: Interesting SQL Puzzle
John,
Are you aware that all emails to your Shelby.net email address are being
rejected? I tried to send you the VFP Organizer updates a number of times
when you have a situation in wihch
three or more states border eachother, like NE, IA, and SD.
think 'four color theorum' - or maybe that just really muddies the waters
g
Andrew Davies MBCS CITP
- AndyD 8-)#
**
This
Here's a tough one that I could use help with.
I need to sum /.group a set of records based on if the States
(Nebraska, Arizona, New York), border one another or if a chain of
bordering States can be found.
So for example,
NE $100.00
IA $100.00
IL $100.00
TX $200.00
AZ $350.00
CA $350.00
From: john harvey [EMAIL PROTECTED]
Date: Mon, June 26, 2006 3:52 pm
To: 'ProFox Email List' profox@leafe.com
Yes, this works out the same. You would just pull both columns
state+neighbor instead of relation.
What if you were trying to get the path from Louisiana to TN?
LAMS, MSTN = 200
22 matches
Mail list logo