Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Importing Data from file daily - procedure inconsistent

3 posts in General Discussion Last posting was on 2007-01-26 17:00:08.0Z
chris Posted on 2007-01-26 12:07:06.0Z
Sender: 5eb3.45a52573.1804289383@sybase.com
From: Chris
Newsgroups: ianywhere.public.general
Subject: Importing Data from file daily - procedure inconsistent
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <45b9fcfa.474d.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 26 Jan 2007 05:07:06 -0700
X-Trace: forums-1-dub 1169816826 10.22.241.41 (26 Jan 2007 05:07:06 -0700)
X-Original-Trace: 26 Jan 2007 05:07:06 -0700, 10.22.241.41
Lines: 268
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5800
Article PK: 1791

I'll try to be brief with this one -

I import a file daily, into a staging table (real table, not
a temp table or anything). File contains dates, names,
numbers, quoted identifier, comma delimited. I import it
without a problem.

The issue comes with the next step. I have to get that data
into the "real" part of the database, and it has to be kept
in sync with changes.

Example - if someone is new in the file, meaning that they
are not already in the database, it needs to add them in.

If they change location, meaning that they already exist, it
needs to update the location and flag it as "Location
Change".

If their dietary needs have changed, same deal as the
location.

If they discharge from the file (no longer there), it needs
to flag them as discharged.

The results of this are inconsistent at best. Basically it
is downright frustrating. If I sit and test the whole
import process with a couple of rows from the file,
everything is perfectly fine.

If it runs alone, at 6:30 AM, I don't get new entries, their
ICU day locations don't change, barely anything works. It
seems to keep the diets and locations up to date, but the
new/discharge/ICU counting does not work.

The ICU issue is if a patient is in a certain designated ICU
area, it needs to count + 1 to their total days there, and
if it hits 3, then it needs to flag them as NEW. Not a big
deal really.

Below is my procedure that I use. There is a script that
runs to import the file, which does not give me any trouble.
At that point, the script just calls this below stored
proc, which does execute, because it updates the "last_run"
date in a table I have set up.

Any ideas? I'm really at a loss and can't get this to work.

Thanks - Chris

ALTER PROCEDURE DBA."PARSEDATA" ( /* parameter, ... */ )
/* RESULT ( column_name, ... ) */
BEGIN

DECLARE PatientName char(128);
DECLARE BillingNumber char(128);
DECLARE existingBillingNumbers char(128);
DECLARE check1 char(128); // that's check ONE......
DECLARE check2 char(128); // just some temp variables
that don't do anything
DECLARE check3 char(128); // just some temp variables
that don't do anything
DECLARE check4 char(128); // just some temp variables
that don't do anything
DECLARE check5 char(128); // just some temp variables
that don't do anything
DECLARE Attending1 char(128); // holding variable
DECLARE Attending2 char(128); // holding variable
DECLARE billing char(128); // holding variable
DECLARE Service1 char(128); // holding variable
DECLARE Service2 char(128); // holding variable
DECLARE SNS1 char(128); // holding variable
DECLARE SNS2 char(128); // holding variable
DECLARE TeamName char(128); // A/B Team holding
variable
DECLARE FilterParam char(128); // A/B Team filter
parameter
DECLARE FilterLength int; // length of the parameter
DECLARE stmt char(512); // our statement to run

DECLARE foundBillingNumbers CURSOR FOR
SELECT BillingNumber FROM "DBA"."STAGETABLE"
LEFT OUTER JOIN Patients ON Stagetable.BillingNumber =
Patients.Billing_Number
where Patients.Billing_Number is NULL;

DECLARE checkLocations CURSOR FOR
SELECT patients.Unit, Stagetable.Area, patients.Room,
Stagetable.Room, Stagetable.BillingNumber FROM
"DBA"."patients"
inner join Stagetable on patients.billing_number =
stagetable.billingnumber
where patients.Unit != Stagetable.Area OR Patients.Room !=
Stagetable.Room;

DECLARE checkDiets CURSOR FOR
SELECT PATIENTS.SNS, STAGETABLE.SNS, patients.billing_number
FROM patients inner join stagetable on
patients.billing_number = stagetable.billingnumber where
patients.sns != stagetable.sns;

DECLARE filterTeams CURSOR FOR
SELECT Team_Name, Filter_Param FROM TEAMFILTERS;

UPDATE PATIENTS SET Status = 'Discharged', onService = 'N',
Update_Date = date(now()), Discharge_Date =
(DATE(DATEADD(DAY, -1, NOW())))
WHERE (Status != 'Discharged') and (patients.billing_number
in
(select Billing_Number from patients where not exists
(select * from stagetable where stagetable.billingnumber =
patients.billing_number)));

print 'done finding discharged';

SET billing = ''; // i'm paranoid

////////////////////////////////////////

OPEN checkLocations;

FETCH NEXT checkLocations into check1, check2, check3,
check4, check5;

WHILE SQLCODE = 0 LOOP

set stmt = 'UPDATE PATIENTS SET Unit = ' + char(39) + check2
+ char(39) + ', Room = ' + char(39) + check4 + char(39) + ',
Status = ' + char(39) + 'CHANGE' + char(39) ||
+ ', onServiceReason = ' + char(39) + 'Location Change' +
char(39) + ', Update_Date = Date(Now()) WHERE ' ||
'Patients.Billing_Number = ' + char(39) + check5 + char(39)
+ ' AND Patients.Status != ' + char(39) + 'Discharged' +
char(39);

//print stmt;
execute immediate stmt;

set stmt = 'DELETE ALL FROM STAGETABLE2 WHERE BillingNumber
= ' + char(39) + check5 + char(39);

//print stmt;
execute immediate stmt;

set stmt = '';

FETCH NEXT checkLocations into check1, check2, check3,
check4, check5;
END LOOP;

CLOSE checkLocations;
print 'check locations done';

// above module takes care of location change patients

OPEN checkDiets;

FETCH NEXT checkDiets into SNS1, SNS2, billing;

WHILE SQLCODE = 0 LOOP

set stmt = 'UPDATE PATIENTS SET SNS = ' + char(39) + SNS2 +
char(39) + ', Status = ' + char(39) + 'CHANGE' + char(39) ||
+ ', onServiceReason = ' + char(39) + 'Diet Change' +
char(39) + ', Update_Date = Date(Now()) WHERE ' ||
'Patients.Billing_Number = ' + char(39) + billing + char(39)
+ ' AND Patients.Status != ' + char(39) + 'Discharged' +
char(39);
print stmt;
execute immediate stmt;

set stmt = 'DELETE ALL FROM STAGETABLE2 WHERE BillingNumber
= ' + char(39) + billing + char(39);
//print stmt;
execute immediate stmt;

set stmt = '';

FETCH NEXT checkDiets into SNS1, SNS2, billing;
END LOOP;

CLOSE checkDiets;
print 'check diets done';

// above module takes care of diet change patients

/***********************************************************************************
Check first for the billing number in the PATIENTS table
(new data is in STAGETABLE).
See if STAGETABLE.BillingNumber exists in PATIENTS.
*/

OPEN foundBillingNumbers;

FETCH NEXT foundBillingNumbers into existingBillingNumbers;

WHILE SQLCODE = 0 LOOP

set stmt = '';

set stmt = 'INSERT INTO PATIENTS (Display_Name,
Billing_Number, MRN, DOB, Gender, SNS, Diagnosis, Age,
Admit_Date, Admit_MD, Attending_MD, Service_Name, Unit,
Room, Import_Date, Update_Date) ' +
'SELECT PatientName, BillingNumber, MRN, DOB, Gender, SNS,
Diagnosis, Age, AdmitDate, AdmittingMD, AttendingMD,
ServiceName, Area, Room, Date(Now()), Date(Now()) FROM
STAGETABLE ' +
'where billingnumber = ' + char(39) + existingBillingNumbers
+ char(39);
execute immediate stmt;

set stmt = '';

set stmt = 'DELETE ALL FROM STAGETABLE2 WHERE BillingNumber
= ' + char(39) + existingBillingNumbers + char(39);
execute immediate stmt;

FETCH NEXT foundBillingNumbers into existingBillingNumbers;
END LOOP;

CLOSE foundBillingNumbers;
print 'found billing numbers done';

// above module takes care of new records added on in import
file (daily)

UPDATE PATIENTS SET Status = 'NEW', onService = 'Y',
onServiceReason = 'Diet-TPN', onServiceDate = date(now())
WHERE (PATIENTS.SNS = 'P') AND Status is NULL;
UPDATE PATIENTS SET Status = 'NEW', onService = 'Y',
onServiceReason = 'Diet-TEN', onServiceDate = date(now())
WHERE (PATIENTS.SNS = 'E') AND Status is NULL;
UPDATE PATIENTS SET Status = 'NEW', onService = 'Y',
onServiceReason = 'Diet-Both', onServiceDate = date(now())
WHERE (PATIENTS.SNS = 'B') AND Status is NULL;

UPDATE PATIENTS SET Status = 'NEW', onService = 'Y',
onServiceReason = 'Dx', onServiceDate = date(now()) WHERE
PATIENTS.Diagnosis IN (select Patients.Diagnosis from
patients inner join diagnosis on patients.diagnosis like '%'
+ diagnosis.dxtext + '%' where Date(Patients.Import_Date) =
Date(now()))
and (PATIENTS.onService != 'Y') AND
date(PATIENTS.Import_Date) = date(now());

///////////////////////////////////////////////////////////////////////////

UPDATE PATIENTS SET ICU_Days = (ICU_Days + 1) WHERE (Unit =
'ICU' OR Unit = 'MICA' OR Unit = 'MICB' OR Unit = 'INCU' OR
Unit = 'ISRA' OR Unit = 'ISRB' OR Unit = 'IMCU' OR Unit =
'SICA' OR Unit = 'SICB' OR Unit = 'SICU' OR Unit = 'SINA' OR
Unit = 'SINB' OR Unit = 'SIRA' OR Unit = 'SIRB'); // AND
(Status != 'Discharged');

UPDATE PATIENTS SET onService = 'Y', onServiceReason = 'ICU
Days', Status = 'NEW', Update_Date = Date(Now()),
onServiceDate = date(now()) where
(ICU_Days = 3 and Status IS NULL);

print 'icu day count done';

//////////////////////////////////////////////////////////////

INSERT INTO LAST_RUN (Run_Date) VALUES (NOW());

print 'parsedata procedure done!';

END


Greg Fenton Posted on 2007-01-26 15:31:39.0Z
From: Greg Fenton <greg.fenton@googles-mail-site>
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Importing Data from file daily - procedure inconsistent
References: <45b9fcfa.474d.1681692777@sybase.com>
In-Reply-To: <45b9fcfa.474d.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: wsp04839138wss.cr.net.cable.rogers.com
X-Original-NNTP-Posting-Host: wsp04839138wss.cr.net.cable.rogers.com
Message-ID: <45ba2ceb@forums-1-dub>
Date: 26 Jan 2007 08:31:39 -0700
X-Trace: forums-1-dub 1169829099 24.235.100.122 (26 Jan 2007 08:31:39 -0700)
X-Original-Trace: 26 Jan 2007 08:31:39 -0700, wsp04839138wss.cr.net.cable.rogers.com
Lines: 34
X-Authenticated-User: sa_beta
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5801
Article PK: 1793


Chris wrote:
>
> The results of this are inconsistent at best. Basically it
> is downright frustrating. If I sit and test the whole
> import process with a couple of rows from the file,
> everything is perfectly fine.

I have not looked over your code at length, but a few things jump out at me:

- You are mixing Watcom SQL and TSQL. I recommend that you stick to
Watcom SQL. For example, use MESSAGE instead of PRINT.

- You have no error checking whatsoever. If a SQL statement fails (e.g.
one of your "execute statement" has an error). See the section "Default
error handling in procedures and triggers" in the SQL Anywhere 10.x
online docs:

SQL Anywhere® Server - SQL Usage
Using Procedures, Triggers, and Batches
Errors and warnings in procedures and triggers
- Default error handling in procedures and triggers

- You should be checking the SQLCODE after an OPEN.

- Personally I prefix all variables with an "@" so that I don't end up
with a conflict with other database object names (e.g. "@name" is a
variable where as "name" could be a column name, a table name, etc...)


Hope this helps,
g.f
--
Greg Fenton
Some Random Dude


"Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g Posted on 2007-01-26 17:00:08.0Z
From: "Chris Keating(iAnywhere Solutions)" <NoSpamPlease_k_e_a_t_i_n_g@i_A_n_y_w_h_e_r_e.com>
Organization: iAnywhere Solutions
User-Agent: Thunderbird 1.5.0.9 (Windows/20061207)
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Importing Data from file daily - procedure inconsistent
References: <45b9fcfa.474d.1681692777@sybase.com> <45ba2ceb@forums-1-dub>
In-Reply-To: <45ba2ceb@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vpneast-client-017.sybase.com
X-Original-NNTP-Posting-Host: vpneast-client-017.sybase.com
Message-ID: <45ba41a8$1@forums-1-dub>
Date: 26 Jan 2007 10:00:08 -0700
X-Trace: forums-1-dub 1169834408 10.18.242.24 (26 Jan 2007 10:00:08 -0700)
X-Original-Trace: 26 Jan 2007 10:00:08 -0700, vpneast-client-017.sybase.com
Lines: 64
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:5802
Article PK: 1792

Using a representative dataset, rerun in your test environment and
ensure that stmt is not NULL when passed to execute immediate. In the
current code, you are building a string and if one of the arguments is
NULL, the string will be NULL. It is possible that your sample row test
does not include data that triggers the problem.

Greg Fenton wrote:
> Chris wrote:
>>
>> The results of this are inconsistent at best. Basically it
>> is downright frustrating. If I sit and test the whole
>> import process with a couple of rows from the file,
>> everything is perfectly fine.
>
> I have not looked over your code at length, but a few things jump out at
> me:
>
> - You are mixing Watcom SQL and TSQL. I recommend that you stick to
> Watcom SQL. For example, use MESSAGE instead of PRINT.
>
> - You have no error checking whatsoever. If a SQL statement fails (e.g.
> one of your "execute statement" has an error). See the section "Default
> error handling in procedures and triggers" in the SQL Anywhere 10.x
> online docs:
>
> SQL Anywhere® Server - SQL Usage
> Using Procedures, Triggers, and Batches
> Errors and warnings in procedures and triggers
> - Default error handling in procedures and triggers
>
> - You should be checking the SQLCODE after an OPEN.
>
> - Personally I prefix all variables with an "@" so that I don't end up
> with a conflict with other database object names (e.g. "@name" is a
> variable where as "name" could be a column name, a table name, etc...)
>
>
> Hope this helps,
> g.f

--

Chris Keating
Sybase Adaptive Server Anywhere Professional Version 8

*****************************************************************************

Sign up today for your copy of the SQL Anywhere Studio 9 Developer
Edition =and try out the market-leading database for mobile, embedded
and small to medium sized business environments for free!

http://www.ianywhere.com/promos/deved/index.html

*****************************************************************************

iAnywhere Solutions http://www.iAnywhere.com

** Please only post to the newsgroup

** Whitepapers can be found at http://www.iAnywhere.com/developer
** EBFs can be found at http://downloads.sybase.com/swx/sdmain.stm
** Use Case Express to report bugs http://case-express.sybase.com

*****************************************************************************