[rbma]

peterlaursen wrote on Aug 16 2005, 07:12 PM:

to find MySQL-version execute this query

select version();

[post=”6885″]<{POST_SNAPBACK}>[/post]

3.23.58-nt

[rbma]

peterlaursen wrote on Aug 16 2005, 06:53 PM:

MySQL version ??

Table definitions ??

Sample data ??

It also would be very nice if you had a look at the HISTORY-tab and copied in some of the more interesting-looking queries …

[post=”6883″]<{POST_SNAPBACK}>[/post]

How do I find out the MySQL version?

name9 CREATE TABLE `name9` (

`ID` char(10) NOT NULL default '',

`MEMBER_TYPE` char(5) default NULL,

`TITLE` char(80) default NULL,

`COMPANY` char(80) default NULL,

`FIRST_NAME` char(20) default NULL,

`MIDDLE_NAME` char(20) default NULL,

`LAST_NAME` char(30) default NULL,

`SUFFIX` char(10) default NULL,

`DESIGNATION` char(20) default NULL,

`WORK_PHONE` char(25) default NULL,

`HOME_PHONE` char(25) default NULL,

`FAX` char(25) default NULL,

`TOLL_FREE` char(25) default NULL,

`MAIL_ADDRESS_NUM` int(10) default '0',

`BILL_ADDRESS_NUM` int(10) default '0',

`US_CONGRESS` char(20) default NULL,

`STATE_SENATE` char(20) default NULL,

`STATE_HOUSE` char(20) default NULL,

`DATE_ADDED` timestamp(14) NOT NULL,

`LAST_UPDATED` timestamp(14) NOT NULL,

`EMAIL` char(100) default NULL,

`WEBSITE` char(255) default NULL,

`web_login` char(60) NOT NULL default ''

) TYPE=MyISAM

user_information CREATE TABLE `user_information` (

`num` int(11) NOT NULL auto_increment,

`user_type` varchar(255) default 'Alumni',

`first_name` varchar(255) default NULL,

`last_name` varchar(255) default NULL,

`user_name` varchar(255) default NULL,

`password` varchar(255) default NULL,

`email_address` varchar(255) default NULL,

`user_number` varchar(255) default NULL,

`company` varchar(255) default NULL,

`website` varchar(255) default NULL,

`link_website` enum('No','Yes') default 'No',

`designation` varchar(30) default NULL,

`title` varchar(255) default NULL,

`work_phone` varchar(255) default NULL,

`home_phone` varchar(255) default NULL,

`fax` varchar(255) default NULL,

`toll_free` varchar(255) default NULL,

`mailing_preference` enum('Work','Home') default 'Work',

`billing_preference` enum('Work','Home') default 'Work',

`work_address` varchar(255) default NULL,

`work_city` varchar(255) default NULL,

`work_state` varchar(255) default NULL,

`work_zip` varchar(255) default NULL,

`work_country` varchar(255) default NULL,

`home_address` varchar(255) default NULL,

`home_city` varchar(255) default NULL,

`home_state` varchar(255) default NULL,

`home_zip` varchar(255) default NULL,

`home_country` varchar(255) default NULL,

`corporate_partner` enum('Yes','No') default 'No',

`education_level` varchar(255) default NULL,

`position` varchar(255) default NULL,

`age` varchar(255) default NULL,

`certification` varchar(255) default NULL,

`years_of_health_care` varchar(255) default NULL,

`years_of_radiology_business` varchar(255) default NULL,

`responsibilities` text,

`skill_set` text,

`services_offered` text,

`products_offered` text,

`date_entered` varchar(255) default NULL,

`status` enum('Active','New','Changed','Inactive') default 'Active',

`resp` text,

`skills` text,

PRIMARY KEY (`num`)

) TYPE=MyISAM

peterlaursen wrote on Aug 16 2005, 06:53 PM:

It also would be very nice if you had a look at the HISTORY-tab and copied in some of the more interesting-looking queries …

[post=”6883″]<{POST_SNAPBACK}>[/post]

/*[12:09:05 PM][ 0 ms]*/ show variables like '%character%'

/*[12:09:05 PM][ 0 ms]*/ show databases

/*[12:09:27 PM][ 110 ms]*/ show table status from `rbma`

/*[12:09:27 PM][ 0 ms]*/ use `rbma`

/*[12:09:31 PM][2437 ms]*/ select n.id, ui.last_name, ui.first_name from name9 n, user_information ui WHERE n.web_login=ui.user_name and ui.date_entered='' and n.web_login<>'' order by n.id

[rbma]

peterlaursen wrote on Aug 16 2005, 06:37 PM:

Thanks!  I saw it!

But what do you wnat me to do with it ?

My thumbnail is different !  No aliases here.

I am also using 4.1

[post=”6881″]<{POST_SNAPBACK}>[/post]

Just wanted to show you that it is doing this on my end. Hopefully, Ritesh will have an answer. Thanks for your help.

[rbma]

Please see attached thumbnail. Thanks.

Using the latest version v4.1

[rbma]

Ritesh wrote on Aug 16 2005, 05:26 PM:

Can you provide me a sample query?

[post=”6877″]<{POST_SNAPBACK}>[/post]

select n.id, ui.last_name, ui.first_name from name9 n, user_information ui WHERE n.web_login=ui.user_name and ui.date_entered='' and n.web_login<>'' order by n.id

[rbma]

Ritesh wrote on Aug 16 2005, 02:25 AM:

Are you user that the table does not exist? If you are able to execute the query from the SQL window then the table will be created successfully. Next time when you execute the query from the Notification Wizard,  it will throw up an error.

The table does not exist. I changed the table name in the query and ran it throught the Notification Wizard only and it gave the error message.

[rbma]

peterlaursen wrote on Jul 31 2005, 08:50 PM:

use db_name;

create table corp_profile_cleanup select corp_profile.id, corp_profile.corp_desc from corp_profile,name where type ='corporate' and paid_thru = '2005-12-31' and corp_profile.id = name.id;

drop table corp_profile;

rename table corp_profile_cleanup to corp_profile;

This does not carry over the primary key info to the new table. How to modify this code to do this? Thank you.

[rbma]

peterlaursen wrote on Aug 4 2005, 06:29 PM:

Very strange that it is opened in Excel.  You did not use the export as .CSV option this time?  When M$-Office is installed it sets Excel as the default program for .csv-files!

Prior to the HTML export, a CSV export was done. I forgot to change/select a different filename so it had the extension .csv thus Excel was used.

[rbma]

peterlaursen wrote on Aug 2 2005, 01:09 PM:

could you confirm that SQL_WHERE is not truncated any more on your system ?

It is fixed. 😀

[rbma]

peterlaursen wrote on Jul 31 2005, 10:54 PM:

give the boy a lollipop and he'll want a candy shop  😀

😛

peterlaursen wrote on Jul 31 2005, 10:54 PM:

You have far more possiblities with SQLyog import tool than any other import tool!

Agree 100%. Thank you Webyog!!!!

peterlaursen wrote on Jul 31 2005, 10:54 PM:

BTW: can't you avouid those redundant rows by using “ON CASCADE DELETE” ?

Unfortunately, it is a custom application in which we have no control on how it operates.

peterlaursen wrote on Jul 31 2005, 10:54 PM:

If you need those rows for some reason you might also consider copying the DB on the SQLserver and to delete the unwnated rows there with the copy!

Yes but we are attempting to automate the whole process of importing records from the internal system and then synchronizing the local copy to the hosting company without manual internvention.

[rbma]

peterlaursen wrote on Jul 31 2005, 08:50 PM:

But I have another solution. Just import everything (no SQL_WHERE) from table 'corp_profile'.  And after import then delete the rows that should not be there with this SQL

🙁 I came to the same conclusion. I was hoping not to have to do this. There is another table linked to NAME with 15,000 records that will have to be downloaded. Hopefully the Scheduled ODBC Import will be enhanced to accomodate this situation.

Again, thank you for all your help.

[rbma]

These are in MSSQL:

NAME:

1,Name1,Corporate,12/31/2005

2,Name2,Corporate,12/31/2005

3,Name3,Corporate,12/31/2004

4,Name4,Active,12/31/2005

CORP_PROFILE

1,Desc1

2,Desc2

3,Desc3

4,NULL

They are related by ID

The resultant tables should be after the Scheduled ODBC Import:

NAME:

1,Name1,Corporate,12/31/2005

2,Name2,Corporate,12/31/2005

CORP_PROFILE

1,Desc1

2,Desc2

Thank you.

[rbma]

I am not at work to test your suggestion. This is what I want to achieve with the following tables during the ODBC Import:

NAME

id

name

member_type

paid_thru

CORP_PROFILE

id

comp_desc

In NAME, import only member_type is Corporate and paid_thru is 12/31/2005.

In CORP_PROFILE, only import records that have records in NAME linked by id.

Can the Scheduled ODBC Import do this?

Thanks.

[rbma]

peterlaursen wrote on Jul 29 2005, 07:23 PM:

but the compound statement

service_production_description != '' OR service_production_description IS NOT NULL

what is that?

Received the error message “The SQL Statement is Invalid”

peterlaursen wrote on Jul 29 2005, 07:23 PM:

service_production_description <> ''

and

service_production_description <> '' AND service_production_description IS NOT NULL

Received the error message “The SQL Statement is Invalid”

[rbma]

peterlaursen wrote on Jul 29 2005, 07:03 PM:

BTW

I think the SQL should be:

service_production_description != '' OR service_production_description IS NOT NULL

to test for both empty strings and NULL values.

[post=”6641″]<{POST_SNAPBACK}>[/post]

This statement is not accepted:

service_production_description != ''

This statement is accepted:

service_production_description IS NOT NULL

[Author]

Posts