SandboxPage

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;