forums › forums › SQLyog › SQLyog: Bugs / Feature Requests › Notificaton Services Wizard
- This topic is empty.
-
AuthorPosts
-
-
August 15, 2005 at 9:28 pm #9171
rbma
MemberWhen starting a new session, the query window is not cleared of the previous query that was executed.
In the same window, the 'Back' button doesn't go back.
The below query runs fine in the SQL window but using the Notification Services results in “Table 'name2' already exists”
CREATE TABLE name2 SELECT n.*, if(na.preferred_mail=1,”Work”,”Home”) AS mailing_preference, if(na.preferred_bill=1,”Work”,”Home”) AS billing_preference, na.address_1 AS work_address, na.address_2 AS work_address2, na.city AS work_city, na.state_province AS work_state, na.zip AS work_zip, na.country AS work_country, na.phone AS wk_phone, na.fax AS wk_fax, na.email AS work_email, na.last_updated AS last_updatedW FROM name n LEFT JOIN name_address na ON n.id=na.id AND na.purpose='WORK';
Also, it is taking a little over a minute for the next screen to appear after pressing the 'Next' button.
Thanks.
-
August 16, 2005 at 2:25 am #18925
Ritesh
MemberQuote:CREATE TABLE name2 SELECT n.*, if(na.preferred_mail=1,”Work”,”Home”) AS mailing_preference, if(na.preferred_bill=1,”Work”,”Home”) AS billing_preference, na.address_1 AS work_address, na.address_2 AS work_address2, na.city AS work_city, na.state_province AS work_state, na.zip AS work_zip, na.country AS work_country, na.phone AS wk_phone, na.fax AS wk_fax, na.email AS work_email, na.last_updated AS last_updatedW FROM name n LEFT JOIN name_address na ON n.id=na.id AND na.purpose='WORK'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.
Notification Wizard can execute multiple queries. You can place a drop table statement at the beginning.
-
August 16, 2005 at 3:42 am #18926MemberRitesh 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.
-
August 16, 2005 at 6:55 am #18927Member
When you press Next, SQLyog actually executes the query to check for its validity. Just a create table statement will create the table. If you go back and come press NEXT, the query is again checked for its validity and thus the error.
Even if you just save the file as a JOB, it will throw up an error as the table was already created at the checkpoint.
-
August 18, 2005 at 5:30 pm #18928MemberRitesh wrote on Aug 16 2005, 06:55 AM:When you press Next, SQLyog actually executes the query to check for its validity. Just a create table statement will create the table. If you go back and come press NEXT, the query is again checked for its validity and thus the error.
Even if you just save the file as a JOB, it will throw up an error as the table was already created at the checkpoint.
[post=”6871″]<{POST_SNAPBACK}>[/post]I'm not able to edit a saved session because SQLyog keeps thinking that a table exists even though it is not listed in the Object Browser. The query has a CREATE TABLE so it is throwing the error of “Duplicate column name”.
-
August 18, 2005 at 5:36 pm #18929
peterlaursen
ParticipantQuote:though it is not listed in the Object Browserit does not show up when up press the refresh-button ?
-
August 18, 2005 at 5:40 pm #18930MemberQuote:I'm not able to edit a saved session because SQLyog keeps thinking that a table exists even though it is not listed in the Object Browser.
Can you explain this issue a little more?
I think you will need to give more info about what you are trying to do and your current setup.
-
August 18, 2005 at 5:41 pm #18931Memberpeterlaursen wrote on Aug 18 2005, 05:36 PM:it does not show up when up press the refresh-button ?[post=”6903″]<{POST_SNAPBACK}>[/post]
It does not show up.
-
August 18, 2005 at 5:56 pm #18932Participant
I don't understand quite either. Though your first posting shold be clear enough.
What happens if you insert
“drop table if exists …”
before the
“create table select … “
or simply execute
“drop table if exists …”
when you had the error msg
-
August 18, 2005 at 6:04 pm #18933Memberpeterlaursen wrote on Aug 18 2005, 05:56 PM:What happens if you insert
“drop table if exists …”
before the
“create table select … “
No go. 🙁
peterlaursen wrote on Aug 18 2005, 05:56 PM:or simply execute“drop table if exists …”
when you had the error msg
[post=”6906″]<{POST_SNAPBACK}>[/post]I don't understand.
-
August 18, 2005 at 6:08 pm #18934Member
This is what I'm trying to accomplish.
1) Import 4 tables from MSSQL using ODBC Import Wizard:
Name
Address
Individual Profile
Corporate Profile
2) Combine selective columns from Address, Individual Profile, and Corporate Profile into Name with the following queries using Notification Services Wizard:
CREATE TABLE name2 SELECT name.*, address.field1, address.field2 FROM name LEFT JOIN address ON name.id=address.id AND name.date_paid=”12/31/05″
CREATE TABLE name3 SELECT name2.*, indv_profile.field1, indv_profile.field2 FROM name2 LEFT JOIN indv_profile ON name2.id=indv_profile.id
CREATE TABLE name4 SELECT name3.*, corp_profile.field1, corp_profile.field2 FROM name3 LEFT JOIN corp_profile ON name3.id=corp_profile.id
DROP TABLE name
DROP TABLE name2
DROP TABLE name 3
RENAME TABLE name4 TO name
Problem: When editing a saved session under Notification Services Wizard, I'm unable to pass the Query window because of “Duplicate column name” on the query “CREATE TABLE name2…”.
3) Synchronize local copy of Name to copy on hosting company using Database Synchronization Wizard.
-
August 18, 2005 at 6:32 pm #18935ParticipantQuote:Problem: When editing a saved session under Notification Services Wizard, I'm unable to pass the Query window because of “Duplicate column name”.
can you edit the XML-file and execute successfully from commandline ?
I would not mind testing it you pasted in the MySQl table definitions for
Name
Address
Individual Profile
Corporate Profile
.. actually I installed mySQL 3.23 yesterday, so I can have a similar setup.
Do you use tunnelling ?
-
August 18, 2005 at 6:44 pm #18936Memberpeterlaursen wrote on Aug 18 2005, 06:32 PM:can you edit the XML-file and execute successfully from commandline ?
It runs fine in the SQL window. I will get back to you on the XML file and commandline.
peterlaursen wrote on Aug 18 2005, 06:32 PM:I would not mind testing it you pasted in the MySQl table definitions forName
Address
Individual Profile
Corporate Profile
Code:name CREATE TABLE `name` (
`ID` varchar(10) NOT NULL default '',
`MEMBER_TYPE` varchar(5) default NULL,
`TITLE` varchar(80) default NULL,
`COMPANY` varchar(80) default NULL,
`FIRST_NAME` varchar(20) default NULL,
`MIDDLE_NAME` varchar(20) default NULL,
`LAST_NAME` varchar(30) default NULL,
`SUFFIX` varchar(10) default NULL,
`DESIGNATION` varchar(20) default NULL,
`WORK_PHONE` varchar(25) default NULL,
`HOME_PHONE` varchar(25) default NULL,
`FAX` varchar(25) default NULL,
`TOLL_FREE` varchar(25) default NULL,
`MAIL_ADDRESS_NUM` int(10) default '0',
`BILL_ADDRESS_NUM` int(10) default '0',
`US_CONGRESS` varchar(20) default NULL,
`STATE_SENATE` varchar(20) default NULL,
`STATE_HOUSE` varchar(20) default NULL,
`DATE_ADDED` timestamp(14) NOT NULL,
`LAST_UPDATED` timestamp(14) NOT NULL,
`EMAIL` varchar(100) default NULL,
`WEBSITE` varchar(255) default NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAMname_address CREATE TABLE `name_address` (
`ID` varchar(10) NOT NULL default '',
`ADDRESS_NUM` int(10) unsigned NOT NULL default '0',
`PURPOSE` varchar(20) default NULL,
`ADDRESS_1` varchar(40) default NULL,
`ADDRESS_2` varchar(40) default NULL,
`CITY` varchar(40) default NULL,
`STATE_PROVINCE` varchar(15) default NULL,
`ZIP` varchar(10) default NULL,
`COUNTRY` varchar(25) default NULL,
`PHONE` varchar(25) default NULL,
`FAX` varchar(25) default NULL,
`LAST_UPDATED` timestamp(14) NOT NULL,
`PREFERRED_MAIL` tinyint(1) NOT NULL default '0',
`PREFERRED_BILL` tinyint(1) NOT NULL default '0',
`EMAIL` varchar(100) default NULL,
PRIMARY KEY (`ADDRESS_NUM`)
) TYPE=MyISAMindv_profile CREATE TABLE `indv_profile` (
`ID` varchar(10) NOT NULL default '',
`HS` tinyint(1) default '0',
`MA` tinyint(1) default '0',
`PHD` tinyint(1) default '0',
`AA_AS` tinyint(1) default '0',
`MBA` tinyint(1) default '0',
`BA_BS` tinyint(1) default '0',
`JD` tinyint(1) default '0',
`MS` tinyint(1) default '0',
`MD` tinyint(1) default '0',
`Educate_Other` tinyint(1) default '0',
`Pos_CEO` tinyint(1) default '0',
`Pos_CFO` tinyint(1) default '0',
`Pos_Director` tinyint(1) default '0',
`Pos_Manager` tinyint(1) default '0',
`Pos_Administrator` tinyint(1) default '0',
`Pos_Coder` tinyint(1) default '0',
`Position_Other` tinyint(1) default '0',
`Pos_Other` varchar(255) default NULL,
`Age` varchar(6) default NULL,
`Cert_RT` tinyint(1) default '0',
`Cert_RTT` tinyint(1) default '0',
`Cert_RTR` tinyint(1) default '0',
`Cert_RTNM` tinyint(1) default '0',
`Cert_CPA` tinyint(1) default '0',
`Cert_Other` tinyint(1) default '0',
`YRS_Health_Care_Mgt` float default '0',
`YRS_Rad_Bus_Mgt` float default '0',
`Res_Accounting` tinyint(1) default '0',
`Res_Clerical_Staff` tinyint(1) default '0',
`Res_Corp_Invest` tinyint(1) default '0',
`Res_Equip_Purch` tinyint(1) default '0',
`Res_Facility_Design` tinyint(1) default '0',
`Res_Marketing` tinyint(1) default '0',
`Res_Negotiations` tinyint(1) default '0',
`Res_Patient_Billing` tinyint(1) default '0',
`Res_Pension_Plan` tinyint(1) default '0',
`Res_Pract_Bill_Serv` tinyint(1) default '0',
`Res_Supply_Purch` tinyint(1) default '0',
`Res_Tech_Staff` tinyint(1) default '0',
`Loc_Image_Ctr_Prv_Office` tinyint(1) default '0',
`Loc_Hospital` tinyint(1) default '0',
`Loc_University` tinyint(1) default '0',
`Loc_Dia_Breast_Ctr` tinyint(1) default '0',
`Loc_Mobile_Service` tinyint(1) default '0',
`Loc_Billing_Services` tinyint(1) default '0',
`Practice_Image_Private` varchar(25) default NULL,
`Practice_Hospital` varchar(25) default NULL,
`Practice_University` varchar(25) default NULL,
`Practice_Dia_Breast_Ctr` varchar(25) default NULL,
`Practice_Mobile` varchar(25) default NULL,
`Practice_Billing` varchar(25) default NULL,
`Single_Practice_Groups` float default '0',
`Single_Practice_Radiologists` float default '0',
`Single_Practice_Tech_Employees` float default '0',
`Single_Practice_Admin_Clerical` float default '0',
`Multi_Practice_Groups` float default '0',
`Multi_Practice_Radiologists` float default '0',
`Multi_Practice_Tech_Employees` float default '0',
`Multi_Practice_Admin_clerical` float default '0',
`BS_Groups` float default '0',
`BS_Radioloists` float default '0',
`BS_Non_Radiologists` float default '0',
`Billing_Hardware_Vendor` varchar(50) default NULL,
`Billing_Software_Vendor` varchar(50) default NULL,
`MS_AR_Management_Q` tinyint(1) default '0',
`MS_AR_Management_C` tinyint(1) default '0',
`MS_Capitation_Q` tinyint(1) default '0',
`MS_Capitation_C` tinyint(1) default '0',
`MS_Coding_Q` tinyint(1) default '0',
`MS_Coding_C` tinyint(1) default '0',
`MS_Facility_Planning_Q` tinyint(1) default '0',
`MS_Facility_Planning_C` tinyint(1) default '0',
`MS_Financial_Mgnt_Q` tinyint(1) default '0',
`MS_Financial_Mgnt_C` tinyint(1) default '0',
`MS_Hospital_Outsource_Q` tinyint(1) default '0',
`MS_Hospital_Outsource_C` tinyint(1) default '0',
`MS_Leasing_Hosp_Radio_Q` tinyint(1) default '0',
`MS_Leasing_Hosp_Radio_C` tinyint(1) default '0',
`MS_Mammotracking_Q` tinyint(1) default '0',
`MS_Mammotracking_C` tinyint(1) default '0',
`MS_Managed_Care_Q` tinyint(1) default '0',
`MS_Managed_Care_C` tinyint(1) default '0',
`MS_Marketing_Q` tinyint(1) default '0',
`MS_Marketing_C` tinyint(1) default '0',
`MS_Medicare_Inspect_Q` tinyint(1) default '0',
`MS_Medicare_Inspect_C` tinyint(1) default '0',
`MS_Negotiations_Q` tinyint(1) default '0',
`MS_Negotiation_C` tinyint(1) default '0',
`MS_Networks_Q` tinyint(1) default '0',
`MS_Networks_C` tinyint(1) default '0',
`MS_Organization_Devel_Q` tinyint(1) default '0',
`MS_Organization_Devel_C` tinyint(1) default '0',
`MS_OSHA_Q` tinyint(1) default '0',
`MS_OSHA_C` tinyint(1) default '0',
`MS_Personel_Q` tinyint(1) default '0',
`MS_Personel_C` tinyint(1) default '0',
`MS_Retirement_Plan_Q` tinyint(1) default '0',
`MS_Retirement_Plan_C` tinyint(1) default '0',
`MS_Teleradiology_Q` tinyint(1) default '0',
`MS_Teleradiology_C` tinyint(1) default '0',
`MS_Other_Q` tinyint(1) default '0',
`MS_Other_C` tinyint(1) default '0',
`BS_Radiology_Q` tinyint(1) default '0',
`BS_Radiology_C` tinyint(1) default '0',
`BS_Allergy_Q` tinyint(1) default '0',
`BS_Allergy_C` tinyint(1) default '0',
`BS_Anesthesiology_Q` tinyint(1) default '0',
`BS_Anesthesiology_C` tinyint(1) default '0',
`BS_Cardiology_Q` tinyint(1) default '0',
`BS_Cardiology_C` tinyint(1) default '0',
`BS_Chiropractic_Q` tinyint(1) default '0',
`BS_Chiropractic_C` tinyint(1) default '0',
`BS_Dentistry_Q` tinyint(1) default '0',
`BS_Dentistry_C` tinyint(1) default '0',
`BS_Dermatology_Q` tinyint(1) default '0',
`BS_Dermatology_C` tinyint(1) default '0',
`BS_DME_Q` tinyint(1) default '0',
`BS_DME_C` tinyint(1) default '0',
`BS_Emergency_Med_Q` tinyint(1) default '0',
`BS_Emergency_Med_C` tinyint(1) default '0',
`BS_Family_Practice_Q` tinyint(1) default '0',
`BS_Family_Practice_C` tinyint(1) default '0',
`BS_General_Practice_Q` tinyint(1) default '0',
`BS_General_Practice_C` tinyint(1) default '0',
`BS_Gynecology_Q` tinyint(1) default '0',
`BS_Gynecology_C` tinyint(1) default '0',
`BS_Home_Health_Q` tinyint(1) default '0',
`BS_Home_Health_C` tinyint(1) default '0',
`BS_Internal_Medicine_Q` tinyint(1) default '0',
`BS_Internal_Medicine_C` tinyint(1) default '0',
`BS_Neurology_Q` tinyint(1) default '0',
`BS_Neurology_C` tinyint(1) default '0',
`BS_Oncology_Q` tinyint(1) default '0',
`BS_Oncology_C` tinyint(1) default '0',
`BS_Opthamology_Q` tinyint(1) default '0',
`BS_Opthamology_C` tinyint(1) default '0',
`BS_Pathology_Q` tinyint(1) default '0',
`BS_Pathology_C` tinyint(1) default '0',
`BS_Pediatrics_Q` tinyint(1) default '0',
`BS_Pediatrics_C` tinyint(1) default '0',
`BS_Physical_Therapy_Q` tinyint(1) default '0',
`BS_Physical_Therapy_C` tinyint(1) default '0',
`BS_Psychiatry_Q` tinyint(1) default '0',
`BS_Psychiatry_C` tinyint(1) default '0',
`BS_Surgery_Q` tinyint(1) default '0',
`BS_Surgery_C` tinyint(1) default '0',
`BS_Urology_Q` tinyint(1) default '0',
`BS_Urology_C` tinyint(1) default '0',
`BS_Other_Q` tinyint(1) default '0',
`BS_Other_C` tinyint(1) default '0',
`BUS_SERV_OTHER_C` varchar(25) default NULL,
`EDUC_OTHER` varchar(25) default NULL,
`LIC_CERT_OTHER` varchar(25) default NULL,
`MS_OTHER` varchar(25) default NULL,
`HARD_VENDOR` varchar(25) default NULL,
`SOFT_VENDOR` varchar(25) default NULL,
`PACS_VENDOR` varchar(25) default NULL,
`RIS_VENDOR` varchar(25) default NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAMcorp_profile CREATE TABLE `corp_profile` (
`ID` varchar(10) NOT NULL default '',
`Accounting` tinyint(1) default '0',
`Billing` tinyint(1) default '0',
`Collections` tinyint(1) default '0',
`Computer_Office_Furniture` tinyint(1) default '0',
`Computer_Hardware` tinyint(1) default '0',
`Computer_Software` tinyint(1) default '0',
`Computer_Support` tinyint(1) default '0',
`Consulting` tinyint(1) default '0',
`Office_Equipment` tinyint(1) default '0',
`Radiographic_Equipment` tinyint(1) default '0',
`Radiographic_Film` tinyint(1) default '0',
`Financing_Lending` tinyint(1) default '0',
`Medical_Business_Forms` tinyint(1) default '0',
`Insurance` tinyint(1) default '0',
`Investments` tinyint(1) default '0',
`Financial_Management` tinyint(1) default '0',
`Personal_Management` tinyint(1) default '0',
`PACS` tinyint(1) default '0',
`Professional_Staffing` tinyint(1) default '0',
`Service_Bureau` tinyint(1) default '0',
`RIS` tinyint(1) default '0',
`Office_Supplies` tinyint(1) default '0',
`Radiographic_Supplies` tinyint(1) default '0',
`Benefit_Packages` tinyint(1) default '0',
`Publisher` tinyint(1) default '0',
`Marketing` tinyint(1) default '0',
`Other_Desc` varchar(255) default NULL,
`Service_Production_Description` text,
`CEO_ADDRESS` varchar(255) default NULL,
PRIMARY KEY (`ID`)
) TYPE=MyISAMpeterlaursen wrote on Aug 18 2005, 06:32 PM:Do you use tunnelling ?Only for Step 3 – Synchronizing
Thank you.
-
August 18, 2005 at 6:58 pm #18937Participant
something's wrong!
you pasted in
name_adress NOT not adress!
If I rename to adress I get the error
“Unknown column 'address.field1' in 'field list'”
I think the querries that you posted here are not quite exact ?
BTW – what is the MySQL version running on your local ?
-
August 18, 2005 at 7:00 pm #18938Memberpeterlaursen wrote on Aug 18 2005, 06:32 PM:can you edit the XML-file and execute successfully from commandline ?
Yes.
-
August 18, 2005 at 7:03 pm #18939Memberpeterlaursen wrote on Aug 18 2005, 06:58 PM:something's wrong!
you pasted in
name_adress NOT not adress!
If I rename to adress I get the error
“Unknown column 'address.field1' in 'field list'”
I think the querries that you posted here are not quite exact ?
It is not pretty. 😀 Here it is.
Code:CREATE TABLE name2 SELECT n.*, if(na.preferred_mail=1,”Work”,”Home”) AS mailing_preference, if(na.preferred_bill=1,”Work”,”Home”) AS billing_preference, na.address_1 AS work_address, na.address_2 AS work_address2, na.city AS work_city, na.state_province AS work_state, na.zip AS work_zip, na.country AS work_country, na.phone AS wk_phone, na.fax AS wk_fax, na.email AS work_email, na.last_updated AS last_updatedW FROM name n LEFT JOIN name_address na ON n.id=na.id AND na.purpose='WORK';
alter table `rbma`.`name2` ,change `ID` `ID` varchar (10) NOT NULL , add primary key (`ID` );CREATE TABLE name3 SELECT n.*, na.address_1 AS home_address, na.address_2 AS home_address2, na.city AS home_city, na.state_province AS home_state, na.zip AS home_zip, na.country AS home_country, na.phone AS hm_phone, na.fax AS hm_fax, na.email AS home_email, na.last_updated AS last_updatedH FROM name2 n LEFT JOIN name_address na ON n.id=na.id AND na.purpose='HOME';
alter table `rbma`.`name3` ,change `ID` `ID` varchar (10) NOT NULL , add primary key (`ID` );CREATE TABLE name4 SELECT n.*, CONCAT(if(Accounting,”Accounting,”,””),if(Billing,”Billing,”,””),if(Collections,”Collections,”,””),if(Computer_Office_Furniture,”Computer Office Furniture,”,””),if(Computer_Hardware,”Computer Hardware,”,””),if(Computer_Software,”Computer Software,”,””),if(Computer_Support,”Computer Support,”,””),if(Consulting,”Consulting,”,””),if(Office_Equipment,”Office Equipment,”,””),if(Radiographic_Equipment,”Radiographic Equipment,”,””),if(Radiographic_Film,”Radiographic Film,”,””),if(Financing_Lending,”Financing Lending,”,””),if(Medical_Business_Forms,”Medical Business Forms,”,””),if(Insurance,”Insurance,”,””),if(Investments,”Investments,”,””),if(Financial_Management,”Financial Management,”,””),if(Personal_Management,”Personal Management,”,””),if(PACS,”PACS,”,””),if(Professional_Staffing,”Professional Staffing,”,””),if(Service_Bureau,”Service Bureau,”,””),if(RIS,”RIS,”,””),if(Office_Supplies,”Office Supplies,”,””),if(Radiographic_Supplies,”Radiographic Supplies,”,””),if(Benefit_Packages,”Benefit Packages,”,””),if(Publisher,”Publisher,”,””),if(Marketing,”Marketing,”,””),Other_Desc,if(Other_Desc<>'',”,”,””)) as services_offered, Other_Desc, cp.Service_Production_Description AS products_offered FROM name3 n LEFT JOIN corp_profile cp ON n.id=cp.id AND n.member_type IN ('C_I','C_II');
alter table `rbma`.`name4` ,change `ID` `ID` varchar (10) NOT NULL , add primary key (`ID` );CREATE TABLE name5 SELECT n.*, CONCAT(if(HS,”HS,”,””),if(AA_AS,”Associate Degree,”,””),if(MS,”MS,”,””),if(MA,”MA,”,””),if(MBA,”MBA,”,””),if(JD,”JD,”,””),if(MD,”MD,”,””),if(PHD,”PhD,”,””)) AS education_level, ip.EDUC_OTHER, CONCAT(if(Pos_CEO,”CEO,”,””),if(Pos_CFO,”CFO,”,””),if(Pos_Director,”Director,”,””),if(Pos_Manager,”Manager,”,””),if(Pos_Administrator,”Administrator,”,””),if(Pos_Coder,”Coder,”,””)) AS position, ip.Pos_Other, ip.age, CONCAT(if(Cert_RT,”Radiologic Technologist (Radiology),”,””),if(Cert_RTT,”Radiologic Technologist (Radiation Therapy),”,””),if(Cert_RTR,”Radiologic Technologist (Retired),”,””),if(Cert_RTNM,”Radiologic Technologist (Nuclear Medicine),”,””),if(Cert_CPA,”CPA,”,””)) AS certification, ip.LIC_CERT_OTHER, YRS_Health_Care_Mgt AS years_of_health_care, YRS_Rad_Bus_Mgt AS years_of_radiology_business, CONCAT(if(Res_Accounting,”Accounting,”,””),if(Res_Clerical_Staff,”Clerical Staff,”,””),if(Res_Corp_Invest,”Corporate Investment,”,””),if(Res_Equip_Purch,”Equipment Purchasing,”,””),if(Res_Facility_Design,”Facility Design,”,””),if(Res_Marketing,”Marketing,”,””),if(Res_Negotiations,”Negotiations,”,””),if(Res_Patient_Billing,”Patient Billing,”,””),if(Res_Pension_Plan,”Pension Plan/Retirement,”,””),if(Res_Pract_Bill_Serv,”Practice' Billing Service,”,””),if(Res_Supply_Purch,”Supply Purchasing,”,””),if(Res_Tech_Staff,”Technical Staff,”,””)) AS responsibilities, CONCAT(if(MS_AR_Management_Q,”A/R Management,”,””),if(MS_Capitation_Q,”Capitation,”,””),if(MS_Coding_Q,”Coding,”,””),if(MS_Facility_Planning_Q,”Facility Planning & Mgmt.,”,””),if(MS_Financial_Mgnt_Q,”Financial Management,”,””),if(MS_Hospital_Outsource_Q,”Hospital Outsourcing,”,””),if(MS_Leasing_Hosp_Radio_Q,”Leasing Hospital Radiology Depts.,”,””),if(MS_Mammotracking_Q,”Mammotracking,”,””),if(MS_Managed_Care_Q,”Managed Care,”,””),if(MS_Marketing_Q,”Marketing,”,””),if(MS_Medicare_Inspect_Q,”Medicare Inspection,”,””),if(MS_Negotiations_Q,”Negotiations,”,””),if(MS_Networks_Q,”Networks,”,””),if(MS_Organization_Devel_Q,”Organizational Development,”,””),if(MS_OSHA_Q,”OSHA Inspection,”,””),if(MS_Personel_Q,”Personnel,”,””),if(MS_Retirement_Plan_Q,”Retirement_Plan,”,””),if(MS_Teleradiology_Q,”Teleradiology,”,””)) AS skillsQN, CONCAT(if(MS_AR_Management_C,”A/R Management,”,””),if(MS_Capitation_C,”Capitation,”,””),if(MS_Coding_C,”Coding,”,””),if(MS_Facility_Planning_C,”Facility Planning & Mgmt.,”,””),if(MS_Financial_Mgnt_C,”Financial Management,”,””),if(MS_Hospital_Outsource_C,”Hospital Outsourcing,”,””),if(MS_Leasing_Hosp_Radio_C,”Leasing Hospital Radiology Depts.,”,””),if(MS_Mammotracking_C,”Mammotracking,”,””),if(MS_Managed_Care_C,”Managed Care,”,””),if(MS_Marketing_C,”Marketing,”,””),if(MS_Medicare_Inspect_C,”Medicare Inspection,”,””),if(MS_Negotiation_C,”Negotiations,”,””),if(MS_Networks_C,”Networks,”,””),if(MS_Organization_Devel_C,”Organizational Development,”,””),if(MS_OSHA_C,”OSHA Inspection,”,””),if(MS_Personel_C,”Personnel,”,””),if(MS_Retirement_Plan_C,”Retirement_Plan,”,””),if(MS_Teleradiology_C,”Teleradiology,”,””)) AS skillsC, MS_OTHER, CONCAT(if(BS_Radiology_Q,”Radiology,”,””),if(BS_Allergy_Q,”Allergy,”,””),if(BS_Anesthesiology_Q,”Anesthesiology,”,””),if(BS_Cardiology_Q,”Cardiology,”,””),if(BS_Chiropractic_Q,”Chiropractic,”,””),if(BS_Dentistry_Q,”Dentistry,”,””),if(BS_Dermatology_Q,”Dermatology,”,””),if(BS_DME_Q,”DME,”,””),if(BS_Emergency_Med_Q,”Emergency Medicine,”,””),if(BS_Family_Practice_Q,”Family Practice,”,””),if(BS_General_Practice_Q,”General Practice,”,””),if(BS_Gynecology_Q,”Gynecology/Obstetrics,”,””),if(BS_Home_Health_Q,”Home Health,”,””),if(BS_Internal_Medicine_Q,”Internal Medicine,”,””),if(BS_Neurology_Q,”Neurology,”,””),if(BS_Oncology_Q,”Oncology-Medical Radiation,”,””),if(BS_Opthamology_Q,”Opthamology,”,””),if(BS_Pathology_Q,”Pathology,”,””),if(BS_Pediatrics_Q,”Pediatrics,”,””),if(BS_Physical_Therapy_Q,”Physical Therapy,”,””),if(BS_Psychiatry_Q,”Psychiatry,”,””),if(BS_Surgery_Q,”Surgery,”,””),if(BS_Urology_Q,”Urology,”,””)) AS bsoQN, CONCAT(if(BS_Radiology_C,”Radiology,”,””),if(BS_Allergy_C,”Allergy,”,””),if(BS_Anesthesiology_C,”Anesthesiology,”,””),if(BS_Cardiology_C,”Cardiology,”,””),if(BS_Chiropractic_C,”Chiropractic,”,””),if(BS_Dentistry_C,”Dentistry,”,””),if(BS_Dermatology_C,”Dermatology,”,””),if(BS_DME_C,”DME,”,””),if(BS_Emergency_Med_C,”Emergency Medicine,”,””),if(BS_Family_Practice_C,”Family Practice,”,””),if(BS_General_Practice_C,”General Practice,”,””),if(BS_Gynecology_C,”Gynecology/Obstetrics,”,””),if(BS_Home_Health_C,”Home Health,”,””),if(BS_Internal_Medicine_C,”Internal Medicine,”,””),if(BS_Neurology_C,”Neurology,”,””),if(BS_Oncology_C,”Oncology-Medical Radiation,”,””),if(BS_Opthamology_C,”Opthamology,”,””),if(BS_Pathology_C,”Pathology,”,””),if(BS_Pediatrics_C,”Pediatrics,”,””),if(BS_Physical_Therapy_C,”Physical Therapy,”,””),if(BS_Psychiatry_C,”Psychiatry,”,””),if(BS_Surgery_C,”Surgery,”,””),if(BS_Urology_C,”Urology,”,””)) AS bsoC, BUS_SERV_OTHER_C FROM name4 n LEFT JOIN indv_profile ip ON n.id=ip.id AND n.member_type IN ('ACT','2ND','3RD','EME','HON');
alter table `rbma`.`name5` ,change `ID` `ID` varchar (10) NOT NULL , add primary key (`ID` );DROP TABLE name;
DROP TABLE name2;
DROP TABLE name3;
DROP TABLE name4;
RENAME TABLE name5 TO name;peterlaursen wrote on Aug 18 2005, 06:58 PM:BTW – what is the MySQL version running on your local ?3.23.58 NT
-
August 18, 2005 at 7:41 pm #18940Participant
I think I got what I need now!
Did you consider the possibility that this is an ALIAS issue as well ?
-
August 18, 2005 at 7:52 pm #18941Participant
Yes.
It seems that there is a problem with the parser!
Testet on MySQL 5.0.11
And I also confirm that that it all works from SQL-pane!
-
August 18, 2005 at 7:55 pm #18942Memberpeterlaursen wrote on Aug 18 2005, 07:41 PM:I think I got what I need now!
Did you consider the possibility that this is an ALIAS issue as well ?
Good suggestion. The queries are now revised without aliases, but the problem is still there. Thanks.
-
August 19, 2005 at 3:46 pm #18943MemberRitesh wrote on Aug 16 2005, 06:55 AM:When you press Next, SQLyog actually executes the query to check for its validity. Just a create table statement will create the table. If you go back and come press NEXT, the query is again checked for its validity and thus the error.
Even if you just save the file as a JOB, it will throw up an error as the table was already created at the checkpoint.
[post=”6871″]<{POST_SNAPBACK}>[/post]Now I understand what you are saying. The problem is on my end with my queries. Thanks.
-
August 19, 2005 at 4:22 pm #18944Participant
I don't understand now ….
Quote:If you go back and come press NEXT, the query is again checked for its validity and thus the errorI did not “go back”. I imported the table definitions and ran the query from SQL-pane. New table was created as it should. Then I dropped the database, created a new empty one, Started Notification Service Wizard, chose not to send mail – just run maintenaince script. When I got to the SQL window I pasted in the complete query (or rather queries) and pressed the “next”button and error message occurred.
Could someone pliiiiiiiiiise explain to me what was the point I did not get ? 😮
-
August 19, 2005 at 4:48 pm #18945Memberpeterlaursen wrote on Aug 19 2005, 04:22 PM:I don't understand now ….
I did not “go back”. I imported the table definitions and ran the query from SQL-pane. New table was created as it should. Then I dropped the database, created a new empty one, Started Notification Service Wizard, chose not to send mail – just run maintenaince script. When I got to the SQL window I pasted in the complete query (or rather queries) and pressed the “next”button and error message occurred.
Could someone pliiiiiiiiiise explain to me what was the point I did not get ? 😮
[post=”6930″]<{POST_SNAPBACK}>[/post]Look at the structure of the table NAME. Is it the original structure or the new structure with the additional columns? In my case, the Notification Services Wizard executed the queries when the 'Next' was pressed, thus changing the structure of NAME without me realizing this. On the next screen, I can only select “Save as Job File”. If 'Execute Now' is selected, the 'Duplicate column name' error occurred because you are essentially running the queries again on the modified NAME table and not the original NAME table.
-
August 19, 2005 at 5:02 pm #18946Participant
OK .. think I got it …
But it did not behave quite like this here.
Did you have all queries in one JOB as I?
I'd like to hear Ritesh's opinion.
After all it is not documented that a set of queries like these are not possible.
-
August 19, 2005 at 5:08 pm #18947Memberpeterlaursen wrote on Aug 19 2005, 05:02 PM:OK .. think I got it …
But it did not behave quite like this here.
Did you have all queries in one JOB as I?
Yes all queries in one job. This particular queries require that the NAME table be in its original structure each time it is executed.
-
August 19, 2005 at 5:16 pm #18948Participant
hmmm …
One workaround could be creating new table with every query from one JOB.
then run another JOB to clean up.
hmmm … still thinking
read my MOTTO <_<
-
August 19, 2005 at 5:23 pm #18949Memberpeterlaursen wrote on Aug 19 2005, 05:16 PM:hmmm …
One workaround could be creating new table with every query from one JOB.
then run another JOB to clean up.
hmmm … still thinking
read my MOTTO <_<
[post=”6934″]<{POST_SNAPBACK}>[/post]It will not be a problem because the NAME table will be replaced automatically by the Scheduled ODBC Import daily. It is only issue now because of the testing and tweaking of the queries.
-
August 19, 2005 at 5:26 pm #18950Participant
Yes you are right .. it is an issue with GUI functionality only.
From cmd.line/batch-mode there is no problem.
-
August 19, 2005 at 5:34 pm #18951Memberpeterlaursen wrote on Aug 19 2005, 05:26 PM:Yes you are right .. it is an issue with GUI functionality only.
From cmd.line/batch-mode there is no problem.
[post=”6936″]<{POST_SNAPBACK}>[/post]GUI functionality? Are you referring to how the Notification Services Wizard executes the queries when clicking the Next button?
-
August 19, 2005 at 5:40 pm #18952ParticipantQuote:Are you referring to how the Notification Services Wizard executes the queries when clicking the Next button?
Yes that was what I meant. But I am not sure I understand everything perfectly yet!
Might spend some time working with a few more simplified test cases .. just for my own understanding of it!
-
August 19, 2005 at 5:44 pm #18953Memberpeterlaursen wrote on Aug 19 2005, 05:16 PM:hmmm …
One workaround could be creating new table with every query from one JOB.
then run another JOB to clean up.
hmmm … still thinking
The more I think about this…the problem is with the queries rather than the Notification Services Wizard.
-
August 20, 2005 at 5:55 am #18954Member
Whoa! What a discussion 😛
Let me explain how the wizard works –
Let us assume that the target database is empty and there are no tables with the name as specified in the above queries. You pasted the query and press NEXT. Now to check for its validity SQLyog actually executes the query (a step to reduce newbie errors 🙂 ). Thus a CREATE TABLE statement is executed and the table is created. When you select EXECUTE JOB NOW, the CREATE TABLE statement is again executed and thus the TABLE ALREADY EXISTS error.
As for the COLUMN DUPLICATION error, it is definitely an issue with the query. You will not be able to execute it from SQL WINDOW too.
-
August 20, 2005 at 9:16 am #18955ParticipantQuote:you will not be able to execute it from SQL WINDOW too
well yes I think I was! But I'll look deeper into it.
It became all too confused yesterday night!
-
-
AuthorPosts
- You must be logged in to reply to this topic.
