Re: SQL Question
- From: "MikeA" <appell@xxxxxxxxxxxxxxxxxx>
- Date: Thu, 27 Aug 2009 11:09:37 -0700
Another thing to consider is possibly just indexing the table unique:
index on line_num unique
The problem is he said:
"I have thousands of invoices so I want to pull the bad invoices only and
browse only those."
Now if he finds a duplicate then he has to figure out which one to delete
and which one to keep. If it does not matter then he could just open the
table for exclusive use and do something like this (after a backup of
course):
use mytable exclusive
delete all
index on line_num unique
recall all
pack
or still another way (so that he can browse the bad entries) might be
something like this:
use mytable exclusive
if ! type("dupfield") = "L"
ALTER TABLE mytable ADD COLUMN duprecord L
endif
replace all mytable.duprecord with .T.
index on line_num tag temp1 unique
replace all mytable.duprecord with .F.
use
MessageBox("Click OK to browse bad invoices")
select * from mytable where mytable.duprecord
"Tom Libby" <tlibby@xxxxxxxxxxxxxxx> wrote in message
news:OAs3YmpJKHA.4168@xxxxxxxxxxxxxxxxxxxxxxx
Does this work for you?
CREATE CURSOR test (invo_num N(3,0), line_num N(1,0), PRODUCT C(5),
isrepeated L)
INSERT INTO test VALUES (101, 0, [Prod1],.F.)
INSERT INTO test VALUES (101, 1, [Prod2],.F.)
INSERT INTO test VALUES (101, 2, [Prod3],.F.)
INSERT INTO test VALUES (101, 1, [Prod4],.F.)
INSERT INTO test VALUES (102, 0, [Prod1],.F.)
INSERT INTO test VALUES (102, 1, [Prod2],.F.)
INSERT INTO test VALUES (102, 2, [Prod3],.F.)
INSERT INTO test VALUES (102, 3, [Prod4],.F.)
UPDATE test ;
set isrepeated = .T. ;
WHERE STR(invo_num) + STR(line_num) IN ;
(SELECT STR(invo_num) + STR(line_num) from test group by 1 having
count(*)
1)
SELECT * ;
FROM test ;
WHERE test.invo_num IN (SELECT DISTINCT test.invo_num FROM test WHERE
test.isrepeated = .T.)
"Emily" <Emily@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:9E0C524E-A971-48E1-81C4-FCD9CB9C8348@xxxxxxxxxxxxxxxx
I was thinking more like flagging something
Like, look in this invoice table if line_num repats then isrepeating =
.t.
That way I can look at only that table.
I have thousands of invoices so I want to pull the bad invoices only and
browse only those.
Does this make sense?
"Stephen Ibbs" wrote:
try this:
CREATE CURSOR test (invo_num N(3,0), line_num N(1,0), PRODUCT C(5))
INSERT INTO test VALUES (101, 0, [Prod1])
INSERT INTO test VALUES (101, 1, [Prod2])
INSERT INTO test VALUES (101, 2, [Prod3])
INSERT INTO test VALUES (101, 1, [Prod4])
SELECT * FROM test WHERE line_num IN ;
(SELECT line_num FROM test HAVING COUNT(line_num) > 1 GROUP BY
line_num) ;
ORDER BY invo_num
Sincerely
Stephen
******************
"Emily" <Emily@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:46A7501A-A824-4FD7-8F1A-C240AA4D6190@xxxxxxxxxxxxxxxx
Hello,
So I have a table with that looks like this
invo_num line_num Product
101 0 Prod1
101 1 Prod2
101 2 Prod3
101 1 Prod4
Which is a faulty table and the last prod should be line num 3
Is there an sql statement that will allow me to search this table for
repeats of line_num?
Thank you.
.
- References:
- SQL Question
- From: Emily
- Re: SQL Question
- From: Stephen Ibbs
- Re: SQL Question
- From: Emily
- Re: SQL Question
- From: Tom Libby
- SQL Question
- Prev by Date: Re: copy to .txt
- Next by Date: Re: copy to .txt
- Previous by thread: Re: SQL Question
- Next by thread: GO BOTT gives error Record is out of range
- Index(es):
Relevant Pages
|
