| View previous topic :: View next topic |
| Author |
Message |
JaneL
Newbie
Joined: 31 Aug 2008
Posts: 3
Location: Nova Scotia Canada
|
 Posted: Sun Aug 31, 2008 1:43 pm Post subject: [SOLVED] SQL to UPDATE Dates of Mailings and keep history |
 |
|
This is my first database and I'd love some help before I get lost in SQL land.
It's a client data base with addresses, phone numbers,dates of visits and more.
Plan is to selectively mail/email educational material and offers to clients.
I can successfully use either FILTER or QUERY to select a group of clients who get a Reminder, but then...
A. I want to record when these Reminders were sent.
B. I also would like to keep a History of dates reminders were sent if possible.
I think SQL commands might work but then I know nothing about Macros.
I'd like to select the same group as filter paramaters to record mailing, so i tried...
A. The following SQL EXECUTED but data was not updated:
UPDATE "Work June 12 2008"
set "Reminders Sent" = '03/15/08'
where "Last Appt" BETWEEN '01-06-2005' AND '12-12-2007';
I want each new mailing date to replace previous date in "Reminders Sent" column.
I also tried YYYY-MM-DD but got " Wrong data type: java.lang.IllegalArgumentException"
Then I tried { D '2005-01-01' } for dates but got different errors.
B. SECOND challenge is to keep a history. I used SQL for first mailing date
"Work June 12 2008" set "Hx Reminders" = '01/15/08';
For subsequent I used:
UPDATE "Work June 12 2008"
set "Hx Reminders" = "Hx Reminders" + '01/15/08,';
Three problems: 1. this needs same parameters as above UPDATE
2. if there was not a previous date in column new date did not get added
3. I only need last 6 dates but VARCHAR stops accepting when full.
Is there a way to TRIM oldest date (Leading 10 characters) with a conditional statement while adding final date?
Table Name: Work June 12 2008
Columns affected: Last Appt - dates recorded manually in data type DATE, format yyyy/mm/dd
Reminders Sent - data type DATE, same format
Hx Reminders - data type VARCHAR [60], to accept a string of dates
I'm not a programmer and am new to SQL.
All help appreciated.
_________________
Jane
Getting to the Point
Last edited by JaneL on Thu Sep 04, 2008 9:11 pm; edited 1 time in total |
|
| Back to top |
|
 |
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Sep 02, 2008 3:23 am Post subject: how to update a field |
|
|
Hello
Maybe this code helps you
Donot try it on your normal database or make first a copy. It change your table without questions.
I do assume that you use the HSQLDB engine. If you don't I think it is.
The code answer only this | Quote: | Is there a way to TRIM oldest date (Leading 10 characters) with a conditional statement while adding final date?
|
I did not insert a where for a date. I think you can make that part in query design and copy it on the good place. If you make the where in the GUI you can see if there is no error and if it select the records you want.
COALESCE
This is a SQL instruction. If there is a NULL value in the instruction the result of the instruction wil be a NULL value. With COALESCE you can change the NULL value to a zero length string, in this way you get the result you want. The where must be insert before ";"
the ";" ends a SQL instruction
Look in the HSQLDB manual for compleet instructions.
I hope it helps you
Romke
| Code: | -- this is for comment in SQL
--"clininfo2" is the name of the table for testing
--"nietmeer1" is a fieldname who I use for testing
--both you must change
--COALESCE is a SQL command which change a null value to a zero lenght string
--CURDATE() insert the current day if you want have a field change it in a field name
update "clininfo2"
set
"nietmeer1"=
COALESCE( (case
when CHAR_LENGTH("nietmeer1") >45
then
substring ( "nietmeer1" from position('0' in "nietmeer1" )+1 )
end) ,"nietmeer1",'' ) || CURDATE() || ',' ;
|
|
|
| Back to top |
|
|
JaneL
Newbie
Joined: 31 Aug 2008
Posts: 3
Location: Nova Scotia Canada
|
| Posted: Tue Sep 02, 2008 7:26 am Post subject: how to update a field |
|
|
Romke
Big thanks. I entered my names into your SQL & it worked ! I was unsure if I needed to enter the date but found it was done for me.
I do not know how to enter a date of my choice, therefore it will take a week to see what happens after 5 or 6 dates entries. Can you suggest how to enter a past date, ie 2008-08-15 instead of || CURDATE() ||
I used:
| Code: | update "Work June 12 2008 2"
set
"Hx Reminders"=
COALESCE( (case
when CHAR_LENGTH("Hx Reminders") >45
then
substring ( "Hx Reminders" from position('0' in "Hx Reminders" )+1 )
end) ,"Hx Reminders",'' ) || CURDATE() || ',' ; |
I use online resources for HSQL , like http://hsqldb.org/doc/guide/ch09.html#update-section
Have found dates really challenging.
Thanks again,
_________________
Jane
Getting to the Point |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Tue Sep 02, 2008 2:35 pm Post subject: |
|
|
Hello
In stead of curdate() you can insert every string you want. A string is suround by a single quote. This an example.
I did insert the complete code again. I did first search for a zero it was easier for me but when I see your post I did see the zero. Now it is changed in a comma. The same comma as after the date. You can both changed it to an other value
| Code: |
-- this is for comment in SQL
--"clininfo2" is the name of the table for testing
--"nietmeer1" is a fieldname who I use for testing
--both you must change
--COALESCE is a SQL command which change a null value to a zero lenght string
--CURDATE() insert the current day if you want have a field change it in a field name
update "clininfo2"
set
"nietmeer1"=
COALESCE( (case
when CHAR_LENGTH("nietmeer1") >45
then
substring ( "nietmeer1" from position(',' in "nietmeer1" )+1 )
end) ,"nietmeer1",'' ) || '2008-01-01' || ',' ;
|
A date of choice can be done as in this example. I think is also possible to do it with a macro. I did not think about it.
I will point you to one thing what can be important. The date now is stored as a string value and not as a date value. If you want search it is important.
In the beginning it did not realy understand the possibillities of the SQL instructions as more I read the manual I how much you can do with it.
Romke |
|
| Back to top |
|
|
JaneL
Newbie
Joined: 31 Aug 2008
Posts: 3
Location: Nova Scotia Canada
|
| Posted: Wed Sep 03, 2008 3:22 pm Post subject: |
|
|
Romke - thank you again,
I have used your updated code & made changes to solve Part A of problem. Maybe all is solved. I added my filter dates with | Code: | | WHERE "Last Appt" BETWEEN '2006-01-01' AND '2007-12-03' ; |
at the end of both commands. So new dates are only entered on selected rows.
A skilled person might have a better solution OR maybe combine into one statement but it works.
| Code: | UPDATE "Work June 12 2008"
set "Reminders Sent" = '2008-09-01'
WHERE "Last Appt" BETWEEN '2006-01-01' AND '2007-12-03' ;
update "Work June 12 2008"
set
"Hx Reminders"=
COALESCE( (case
when CHAR_LENGTH("Hx Reminders") >56
then
substring ( "Hx Reminders" from position(',' in "Hx Reminders" )+1 )
end) ,"Hx Reminders",'' ) || '2008-09-01' || ','
where "Last Appt" BETWEEN '2006-01-01' AND '2007-12-03' ; |
Works equally well using in place of each 'date'
It is true I can not search in Hx Reminders but I don't need to search there.
Do you see any problem with my attempt? I'm so happy. I should have asked on Forum earlier & had more time to play.
_________________
Jane
Getting to the Point |
|
| Back to top |
|
|
RPG
Super User
Joined: 24 Apr 2008
Posts: 2696
Location: Apeldoorn, Netherland
|
| Posted: Wed Sep 03, 2008 5:02 pm Post subject: |
|
|
Hello JaneL
It nice to hear it is good working. It is also good to change it, most of the times it means that you better understand the working. And the process of step by step is most of the time fast enough. Fast enough for the computer and for us as people.
Romke |
|
| Back to top |
|
|
|
