We're using this page to swap code and otherwise text at each other at the same time we talk on the phone.
Error Fixing
Fixing errors that show up in neighbors....
Missing a required ncode:
select * from neighbors as needs, ncodes where ncodes.ncode = needs.ncode and ncodes.requires is not NULL and not exists (select 1 from neighbors as needed where needed.pntid = needs.pntid and needed.ncode = ncodes.requires) order by pntid;
Duplicate sname:
select * from neighbors as one, neighbors as another where one.pntid = another.pntid and one.sname = another.sname and one.nghid < another.nghid and not ((one.ncode = 'A' and another.ncode = '1') or (one.ncode = '1' and another.ncode = 'A'));
Data Maintenance
Adding a new census row (foxpro version):
insert into census (cenid, sname, date, status, cen) ; select max(cenid) + 1, "JOE", {25/10/2006}, "C", t ; from census
Adding a new census row (postgres version):
insert into census (sname, date, status, cen) values ('JOE', '2006-10-25', 'C', t);
Changing Case:
select sname, lower(sname) as lowercase from biograph; select sname, upper(sname) as uppercase from biograph;
Extracting Part of a Date:
select date_part('year', biograph.birth) as byear from biograph;
Granting Permission in the Sandbox:
GRANT ALL ON sandbox.tablename to GROUP babase_editors; -- And, if the table has a column (like an Id column) -- that automatically generates the next sequential -- value, you also need to grant permission to -- update the sequence. -- -- Supposing the column containing the sequential -- value is called foo.... GRANT SELECT ON tablename_foo_seq TO GROUP babase_editors; GRANT UPDATE ON tablename_foo_seq TO GROUP babase_editors;
More stuff:
select * from cycpoints_cycles where sname = 'RIN' order by date;
- To select a set number of characters from any field
select substring(pid from 1 for 3) as mom from biograph;
This query will give you 3 characters from the pid field, starting at the first character, aka the sname of the mother to whom the individual was born.
Query to extract date from date-time columns
cast(min_maxs.wrdaytime AS date)
for columns containined a date,timestamp, use this to extract the date
Query To Extract ADM Ranks
This is the basic premise of the query.. it can be adapted to fit any query you are running, it is just important that you include the sname=sname and rnkdate>ranked information.
select ranks.* from ranks, rankdates where ranks.sname = rankdates.sname and ranks.rnkdate>rankdates.ranked;