Little help with SQL syntax 
Author Message
 Little help with SQL syntax

Folks,

I am doing something odd with Scalable SQL, and scalable SQL is not in
general my forte.

What I need to do is in a stored procedure take a record from one table and
place modified contents in another table.  The issue is that in several
cases, I need to change a variable from a string in "19981222" format and
make it into a date.  What I am trying to do is something like this, where
"DateAsBTDate" is the date that needs to be filled in (and is a Btrieve
date) and "DateAsString" is the ccyymmdd format.  Any help appreciated.

INSERT INTO SD_PATIENT_REG(Patient_ID,
  Skilled_Care_Pat_ID,DateAsBTDate)
  (SELECT soc_sec_no,Patient_No,DateAsString
   FROM patients
  WHERE Record_number = SKID);

The entire SP without this code is as follows.  I also have a SP that does
the procedure of changing the string, but do not know how to go about
calling this as part of the insert statement.

CREATE PROCEDURE AdmitToSD ( SKID int(4));
BEGIN

INSERT INTO SD_patient_ids(Patient_ID,Med_Rec_No,Soc_Sec_No,Last_name,
  address_1,address_2,City,
  Zipcode,home_phone)
  (select Patient_no,Medical_record,soc_sec_no,
  SUBSTRING(patient_name,1,25),address_1,address_2,
  City,zipcode,phone_no FROM patients
  WHERE Record_number = SKID);

INSERT INTO SD_PATIENT_REG(Patient_ID,
  Skilled_Care_Pat_ID)
  (SELECT soc_sec_no,Patient_No
   FROM patients
  WHERE Record_number = SKID);
END



Sat, 09 Jun 2001 03:00:00 GMT
 Little help with SQL syntax

If you're using Scalable SQL 4 or Pervasive.SQL 7, you can use the
CAST function, either directly in the INSERT or indirectly via a
DECLAREd variable.  The following should work as long as you're logged
in using 4.0 mode:

INSERT INTO SD_PATIENT_REG(Patient_ID,
  Skilled_Care_Pat_ID,DateAsBTDate)
  (SELECT soc_sec_no,Patient_No, CAST(DateAsString AS DATE)
   FROM patients
  WHERE Record_number = SKID);

Linda Anderson
Customer Engineering - Pervasive Software

On Tue, 22 Dec 1998 16:53:33 -0500, "Doug Reilly"

Quote:

>Folks,

>I am doing something odd with Scalable SQL, and scalable SQL is not in
>general my forte.

>What I need to do is in a stored procedure take a record from one table and
>place modified contents in another table.  The issue is that in several
>cases, I need to change a variable from a string in "19981222" format and
>make it into a date.  What I am trying to do is something like this, where
>"DateAsBTDate" is the date that needs to be filled in (and is a Btrieve
>date) and "DateAsString" is the ccyymmdd format.  Any help appreciated.

>INSERT INTO SD_PATIENT_REG(Patient_ID,
>  Skilled_Care_Pat_ID,DateAsBTDate)
>  (SELECT soc_sec_no,Patient_No,DateAsString
>   FROM patients
>  WHERE Record_number = SKID);

>The entire SP without this code is as follows.  I also have a SP that does
>the procedure of changing the string, but do not know how to go about
>calling this as part of the insert statement.

>CREATE PROCEDURE AdmitToSD ( SKID int(4));
>BEGIN

>INSERT INTO SD_patient_ids(Patient_ID,Med_Rec_No,Soc_Sec_No,Last_name,
>  address_1,address_2,City,
>  Zipcode,home_phone)
>  (select Patient_no,Medical_record,soc_sec_no,
>  SUBSTRING(patient_name,1,25),address_1,address_2,
>  City,zipcode,phone_no FROM patients
>  WHERE Record_number = SKID);

>INSERT INTO SD_PATIENT_REG(Patient_ID,
>  Skilled_Care_Pat_ID)
>  (SELECT soc_sec_no,Patient_No
>   FROM patients
>  WHERE Record_number = SKID);
>END



Sat, 16 Jun 2001 03:00:00 GMT
 Little help with SQL syntax
Linda,

Thanks.  I will give it a try.  I have recently tries using the mask, and
that did not work, and so I look forward to getting this going.

Hope you had a nice holiday, and hope the New Year is nice as well!

Quote:

>If you're using Scalable SQL 4 or Pervasive.SQL 7, you can use the
>CAST function, either directly in the INSERT or indirectly via a
>DECLAREd variable.  The following should work as long as you're logged
>in using 4.0 mode:

>INSERT INTO SD_PATIENT_REG(Patient_ID,
>  Skilled_Care_Pat_ID,DateAsBTDate)
>  (SELECT soc_sec_no,Patient_No, CAST(DateAsString AS DATE)
>   FROM patients
>  WHERE Record_number = SKID);

>Linda Anderson
>Customer Engineering - Pervasive Software

>On Tue, 22 Dec 1998 16:53:33 -0500, "Doug Reilly"

>>Folks,

>>I am doing something odd with Scalable SQL, and scalable SQL is not in
>>general my forte.

>>What I need to do is in a stored procedure take a record from one table
and
>>place modified contents in another table.  The issue is that in several
>>cases, I need to change a variable from a string in "19981222" format and
>>make it into a date.  What I am trying to do is something like this, where
>>"DateAsBTDate" is the date that needs to be filled in (and is a Btrieve
>>date) and "DateAsString" is the ccyymmdd format.  Any help appreciated.

>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>  Skilled_Care_Pat_ID,DateAsBTDate)
>>  (SELECT soc_sec_no,Patient_No,DateAsString
>>   FROM patients
>>  WHERE Record_number = SKID);

>>The entire SP without this code is as follows.  I also have a SP that does
>>the procedure of changing the string, but do not know how to go about
>>calling this as part of the insert statement.

>>CREATE PROCEDURE AdmitToSD ( SKID int(4));
>>BEGIN

>>INSERT INTO SD_patient_ids(Patient_ID,Med_Rec_No,Soc_Sec_No,Last_name,
>>  address_1,address_2,City,
>>  Zipcode,home_phone)
>>  (select Patient_no,Medical_record,soc_sec_no,
>>  SUBSTRING(patient_name,1,25),address_1,address_2,
>>  City,zipcode,phone_no FROM patients
>>  WHERE Record_number = SKID);

>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>  Skilled_Care_Pat_ID)
>>  (SELECT soc_sec_no,Patient_No
>>   FROM patients
>>  WHERE Record_number = SKID);
>>END



Sat, 16 Jun 2001 03:00:00 GMT
 Little help with SQL syntax
Linda,

THANK YOU.

The actual final code used was as follows, with the MASK [yyyymmdd] added...

Very cool.

INSERT INTO SD_PATIENT_REG(Patient_ID,
  Skilled_Care_Pat_ID,Admit_date,discharge_date)
 (SELECT soc_sec_no,Patient_No,CAST(Admit_date as DATE MASK [yyyymmdd]),
  CAST(Discharge_date as DATE MASK [yyyymmdd])
   FROM patients
  WHERE Record_number = SKID);

Quote:

>If you're using Scalable SQL 4 or Pervasive.SQL 7, you can use the
>CAST function, either directly in the INSERT or indirectly via a
>DECLAREd variable.  The following should work as long as you're logged
>in using 4.0 mode:

>INSERT INTO SD_PATIENT_REG(Patient_ID,
>  Skilled_Care_Pat_ID,DateAsBTDate)
>  (SELECT soc_sec_no,Patient_No, CAST(DateAsString AS DATE)
>   FROM patients
>  WHERE Record_number = SKID);

>Linda Anderson
>Customer Engineering - Pervasive Software

>On Tue, 22 Dec 1998 16:53:33 -0500, "Doug Reilly"

>>Folks,

>>I am doing something odd with Scalable SQL, and scalable SQL is not in
>>general my forte.

>>What I need to do is in a stored procedure take a record from one table
and
>>place modified contents in another table.  The issue is that in several
>>cases, I need to change a variable from a string in "19981222" format and
>>make it into a date.  What I am trying to do is something like this, where
>>"DateAsBTDate" is the date that needs to be filled in (and is a Btrieve
>>date) and "DateAsString" is the ccyymmdd format.  Any help appreciated.

>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>  Skilled_Care_Pat_ID,DateAsBTDate)
>>  (SELECT soc_sec_no,Patient_No,DateAsString
>>   FROM patients
>>  WHERE Record_number = SKID);

>>The entire SP without this code is as follows.  I also have a SP that does
>>the procedure of changing the string, but do not know how to go about
>>calling this as part of the insert statement.

>>CREATE PROCEDURE AdmitToSD ( SKID int(4));
>>BEGIN

>>INSERT INTO SD_patient_ids(Patient_ID,Med_Rec_No,Soc_Sec_No,Last_name,
>>  address_1,address_2,City,
>>  Zipcode,home_phone)
>>  (select Patient_no,Medical_record,soc_sec_no,
>>  SUBSTRING(patient_name,1,25),address_1,address_2,
>>  City,zipcode,phone_no FROM patients
>>  WHERE Record_number = SKID);

>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>  Skilled_Care_Pat_ID)
>>  (SELECT soc_sec_no,Patient_No
>>   FROM patients
>>  WHERE Record_number = SKID);
>>END



Sat, 16 Jun 2001 03:00:00 GMT
 Little help with SQL syntax
I'm glad it worked (I knew it would, of course :)

I can't imagine life before the CAST function!

Quote:
>Linda,

>THANK YOU.

>The actual final code used was as follows, with the MASK [yyyymmdd] added...

>Very cool.

>INSERT INTO SD_PATIENT_REG(Patient_ID,
>  Skilled_Care_Pat_ID,Admit_date,discharge_date)
> (SELECT soc_sec_no,Patient_No,CAST(Admit_date as DATE MASK [yyyymmdd]),
>  CAST(Discharge_date as DATE MASK [yyyymmdd])
>   FROM patients
>  WHERE Record_number = SKID);


>>If you're using Scalable SQL 4 or Pervasive.SQL 7, you can use the
>>CAST function, either directly in the INSERT or indirectly via a
>>DECLAREd variable.  The following should work as long as you're logged
>>in using 4.0 mode:

>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>  Skilled_Care_Pat_ID,DateAsBTDate)
>>  (SELECT soc_sec_no,Patient_No, CAST(DateAsString AS DATE)
>>   FROM patients
>>  WHERE Record_number = SKID);

>>Linda Anderson
>>Customer Engineering - Pervasive Software

>>On Tue, 22 Dec 1998 16:53:33 -0500, "Doug Reilly"

>>>Folks,

>>>I am doing something odd with Scalable SQL, and scalable SQL is not in
>>>general my forte.

>>>What I need to do is in a stored procedure take a record from one table
>and
>>>place modified contents in another table.  The issue is that in several
>>>cases, I need to change a variable from a string in "19981222" format and
>>>make it into a date.  What I am trying to do is something like this, where
>>>"DateAsBTDate" is the date that needs to be filled in (and is a Btrieve
>>>date) and "DateAsString" is the ccyymmdd format.  Any help appreciated.

>>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>>  Skilled_Care_Pat_ID,DateAsBTDate)
>>>  (SELECT soc_sec_no,Patient_No,DateAsString
>>>   FROM patients
>>>  WHERE Record_number = SKID);

>>>The entire SP without this code is as follows.  I also have a SP that does
>>>the procedure of changing the string, but do not know how to go about
>>>calling this as part of the insert statement.

>>>CREATE PROCEDURE AdmitToSD ( SKID int(4));
>>>BEGIN

>>>INSERT INTO SD_patient_ids(Patient_ID,Med_Rec_No,Soc_Sec_No,Last_name,
>>>  address_1,address_2,City,
>>>  Zipcode,home_phone)
>>>  (select Patient_no,Medical_record,soc_sec_no,
>>>  SUBSTRING(patient_name,1,25),address_1,address_2,
>>>  City,zipcode,phone_no FROM patients
>>>  WHERE Record_number = SKID);

>>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>>  Skilled_Care_Pat_ID)
>>>  (SELECT soc_sec_no,Patient_No
>>>   FROM patients
>>>  WHERE Record_number = SKID);
>>>END



Sat, 16 Jun 2001 03:00:00 GMT
 Little help with SQL syntax
Linda,

I am a bit spoiled by having used another brand of SQL, and it does have
powerful conversion features (implemented differently).

Another handy feature would be more specific error messages in SQL Scope.
Since the API provides functions to get the position of the error, a message
saying 'Syntax error near "Fred"' might be handy.  Other SQL Scope like
products for other SQL engines do provide that...

Quote:

>I'm glad it worked (I knew it would, of course :)

>I can't imagine life before the CAST function!

>>Linda,

>>THANK YOU.

>>The actual final code used was as follows, with the MASK [yyyymmdd]
added...

>>Very cool.

>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>  Skilled_Care_Pat_ID,Admit_date,discharge_date)
>> (SELECT soc_sec_no,Patient_No,CAST(Admit_date as DATE MASK [yyyymmdd]),
>>  CAST(Discharge_date as DATE MASK [yyyymmdd])
>>   FROM patients
>>  WHERE Record_number = SKID);


>>>If you're using Scalable SQL 4 or Pervasive.SQL 7, you can use the
>>>CAST function, either directly in the INSERT or indirectly via a
>>>DECLAREd variable.  The following should work as long as you're logged
>>>in using 4.0 mode:

>>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>>  Skilled_Care_Pat_ID,DateAsBTDate)
>>>  (SELECT soc_sec_no,Patient_No, CAST(DateAsString AS DATE)
>>>   FROM patients
>>>  WHERE Record_number = SKID);

>>>Linda Anderson
>>>Customer Engineering - Pervasive Software

>>>On Tue, 22 Dec 1998 16:53:33 -0500, "Doug Reilly"

>>>>Folks,

>>>>I am doing something odd with Scalable SQL, and scalable SQL is not in
>>>>general my forte.

>>>>What I need to do is in a stored procedure take a record from one table
>>and
>>>>place modified contents in another table.  The issue is that in several
>>>>cases, I need to change a variable from a string in "19981222" format
and
>>>>make it into a date.  What I am trying to do is something like this,
where
>>>>"DateAsBTDate" is the date that needs to be filled in (and is a Btrieve
>>>>date) and "DateAsString" is the ccyymmdd format.  Any help appreciated.

>>>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>>>  Skilled_Care_Pat_ID,DateAsBTDate)
>>>>  (SELECT soc_sec_no,Patient_No,DateAsString
>>>>   FROM patients
>>>>  WHERE Record_number = SKID);

>>>>The entire SP without this code is as follows.  I also have a SP that
does
>>>>the procedure of changing the string, but do not know how to go about
>>>>calling this as part of the insert statement.

>>>>CREATE PROCEDURE AdmitToSD ( SKID int(4));
>>>>BEGIN

>>>>INSERT INTO SD_patient_ids(Patient_ID,Med_Rec_No,Soc_Sec_No,Last_name,
>>>>  address_1,address_2,City,
>>>>  Zipcode,home_phone)
>>>>  (select Patient_no,Medical_record,soc_sec_no,
>>>>  SUBSTRING(patient_name,1,25),address_1,address_2,
>>>>  City,zipcode,phone_no FROM patients
>>>>  WHERE Record_number = SKID);

>>>>INSERT INTO SD_PATIENT_REG(Patient_ID,
>>>>  Skilled_Care_Pat_ID)
>>>>  (SELECT soc_sec_no,Patient_No
>>>>   FROM patients
>>>>  WHERE Record_number = SKID);
>>>>END



Sat, 16 Jun 2001 03:00:00 GMT
 
 [ 6 post ] 

 Relevant Pages 

1. A little SQL syntax help please

2. Need a little syntax help

3. ? syntax for DAO dsn-less connection to SQL server

4. little sql syntax question

5. SQL Syntax Help - Incorrect syntax near '%'.

6. DSN less connection to server error IM012 Driver keyword syntax error

7. DSN less connection to server error IM012 Driver keyword syntax error

8. little syntax question

9. Old Article: Access SQL syntax versus Transact SQL syntax

10. HELP / HELP / syntax SQL / 'LIKE' clause

11. I need a little help in SQL 2000 Server

12. Denali and SQL Server, a little help


 
Powered by phpBB® Forum Software