select a.sendingfacility_namespaceid as "PracticeId", Initcap(a.sendingfacility_universalid) as "PracticeName", s.aip_personel_resource_id as "PhysicianId", Initcap(s.aip_personel_resource_lname) as "PhysicianLastName", Initcap(s.aip_personel_resource_fname) as "PhysicianFirstName", coalesce(a.alternate_patient_id, s.alternate_patient_id) as "PatientId", initcap(coalesce(a.patient_fname, s.patient_fname)) as "FirstName", initcap(coalesce(a.patient_lname, s.patient_lname)) as "LastName", a.patient_dob::date as "DateOfBirth", a.patient_sex as "Gender", a.patient_street as "Address1", a.patient_other_designation as "Address2", initcap(a.patient_city) as "City", a.patient_state as "State", a.patient_zip as "Zip", coalesce(a.patient_ssn, s.patient_ssn) as "SSN", a.pri_ins_company_name as "Payer1", a.pri_ins_policy_nbr as "PolicyNumber1", a.sec_ins_company_name as "Payer2", a.sec_ins_policy_nbr as "PolicyNumber2", s.schedule_id as "AppointmentId", to_timestamp(s.appt_startdate, 'YYYYMMDDHH24MISS') as "AppointmentTime", s.event_reason as "AppointmentDescription", initcap(s.appt_type) as "AppointmentType", s.filler_status as "AppointmentStatus", s.event_reason_id as "EncounterID"
from hl7.adt a, hl7.siu s where a.alternate_patient_id = s.alternate_patient_id and length(trim(s.alternate_patient_id)) > 1 i am also matching on 3 particular doctors but dont want to send that. The column that i am trying to match on is s.txn_time. I only want txn_times after the highest txn_time from the previous query. On Tue, Feb 6, 2018 at 9:50 AM, James Wing <[email protected]> wrote: > Understood. Can you share an outline of the query you are trying to use? > Which values would you change based on the maintained state? What would be > the source of the updated state? > > On Tue, Feb 6, 2018 at 6:33 AM, Austin Duncan <[email protected]> > wrote: > >> I am not sure if thats what i need. My query is pretty robust and i am >> not sure if it will be able to be implemented in that >> >> On Tue, Feb 6, 2018 at 9:30 AM, James Wing <[email protected]> wrote: >> >>> Austin, >>> >>> Have you tried QueryDatabaseTable? For some databases and table schema, >>> it provides a shortcut to querying for the recently changed records, as >>> long as you have a "maximum value column" to use. >>> >>> https://nifi.apache.org/docs/nifi-docs/components/org.apache >>> .nifi/nifi-standard-nar/1.5.0/org.apache.nifi.processors.sta >>> ndard.QueryDatabaseTable/index.html >>> >>> Thanks, >>> >>> James >>> >>> On Tue, Feb 6, 2018 at 6:12 AM, Austin Duncan <[email protected]> >>> wrote: >>> >>>> All, >>>> I am trying to do a flow that queries a postgres database every hour. I >>>> am trying to use the stateful settings in the updateAttribute processor so >>>> that I only pull new files that have been uploaded within that hour. I am >>>> having trouble figuring out how to implement it. Can anyone help me out? >>>> Thanks, >>>> Austin >>>> >>>> -- >>>> Austin Duncan >>>> *Developer* >>>> PYA Analytics >>>> 2220 Sutherland Avenue >>>> <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> >>>> Knoxville, TN 37919 >>>> <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> >>>> 423-260-4172 >>>> >>>> <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> >>>> <%28865%29%20684-2828> >>>> >>> >>> >> >> >> -- >> Austin Duncan >> *Developer* >> PYA Analytics >> 2220 Sutherland Avenue >> <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> >> Knoxville, TN 37919 >> <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> >> 423-260-4172 >> >> <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> >> <%28865%29%20684-2828> >> > > -- Austin Duncan *Developer* PYA Analytics 2220 Sutherland Avenue <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> Knoxville, TN 37919 <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> 423-260-4172 <https://maps.google.com/?q=2220+Sutherland+AvenueKnoxville,+TN+37919+865&entry=gmail&source=g> <%28865%29%20684-2828>
