Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38

    Unanswered: Data changed when columns changed? Ideas?

    I have this very long query (unfortunately I cannot share data due to privacy issues and schema due to company policy). The author of this query is no longer with the company, so that is not an option.

    Synopsis:
    If I remove certain columns from the select clause, it affects the data in other columns. I have never seen this behavior before. I would understand changing joins or conditions, but not the columns themselves.

    Issue:

    When I run this, I get the correct `messageHeader`.`interfaceID`if I remove the IFNULL ... AS `enounterID` or IFNULL( ... AS `interfaceOptions`

    There is more to this, but I need to get this sorted out first.

    When I say nasty I mean nasty:

    Code:
    SELECT
    	`messageHeader`.`interfaceID`,
    	`order`.`orderID`,
    	`order`.`clinicID`,
    	`order`.`patientID`,
    	`order`.`communityID`,
    	`order`.`enterpriseID`,
    	IFNULL(`order`.`encounterID`,'') AS `encounterID`,
    	`orderPriority`.`name` AS `orderPriority`,
    	`community`.`database`,
    	`order`.`insertByUserID`,
    	`messageHeader`.`subType`,
    	`messageHeader`.`superType`,
    	`messageHeader`.`sendingApp`,
    	`messageHeader`.`sendingFac`,
    	`orderRecord`.`orderRecordID`,
    	`messageHeader`.`receivingApp`,
    	`messageHeader`.`receivingFac`,
    	`messageHeader`.`messageHeaderID`,
    	`orderType`.`code` AS `orderType`,
    	`provider`.`title` AS `providerTitle`,
    	`testIdentifier`.`name` AS `testName`,
    	`provider`.`lastName` AS `providerLastName`,
    	`testIdentifier`.`identifier` AS `testCode`,
    	`provider`.`firstName` AS `providerFirstName`,
    	TRIM(`chcClinic`.`clinicname`) AS `clinicName`,
    	`chcInterfaceUser`.`interface` AS `interfaceType`,
    	`chcInterfaceUser`.`downloadpath` AS `interfacePath`,
    	`chcInterfaceUser`.`TimezoneID` AS `TimezoneID`,
    	DATE_FORMAT(`order`.`dueDate`, '%Y-%m-%d') AS `dueDate`,
    	IFNULL(`provider`.`middleName`, '') AS `providerMiddleName`,
    	IFNULL(`providerNameSuffix`.`name`, '') AS `providerNameSuffix`,
    	IFNULL(`codingSystem`.`identifier`, IFNULL(`enterpriseCodingSystem`.`identifier`, '')) AS `codingSystem`,
    	GROUP_CONCAT(DISTINCT `cpt`.`cpt` ORDER BY `orderRecord_cpt`.`sortOrder` SEPARATOR '~') AS `cpts`,
    	IFNULL(GROUP_CONCAT(DISTINCT `chcInterfaceOptions`.`option` SEPARATOR '~'),'') AS `interfaceOptions`,
    	GROUP_CONCAT(DISTINCT `icd9`.`icd9` ORDER BY `orderRecord_icd9`.`sortOrder` SEPARATOR '~') AS `icd9s`
    FROM `orders`.`order`
    JOIN `orders`.`orderPriority` ON `orderPriority`.`orderPriorityID` = `order`.`orderPriorityID`
    LEFT JOIN `orders`.`orderRecord` ON `order`.`orderID` = `orderRecord`.`orderID`
    JOIN `orders`.`orderType` ON `orderType`.`orderTypeID` = `order`.`orderTypeID`
    LEFT JOIN `orders`.`orderRecord_icd9` ON `orderRecord_icd9`.`orderRecordID` = `orderRecord`.`orderRecordID`
    LEFT JOIN `coding`.`icd9` ON `icd9`.`icd9ID` = `orderRecord_icd9`.`icd9ID`
    LEFT JOIN `orders`.`orderRecord_cpt` ON `orderRecord_cpt`.`orderRecordID` = `orderRecord`.`orderRecordID`
    LEFT JOIN `coding`.`cpt` ON	`cpt`.`cptID` = `orderRecord_cpt`.`cptID`
    JOIN `chartconnect`.`community` ON `community`.`communityID` = `order`.`communityID`
    JOIN `chartconnect`.`location` ON 	`location`.`locationID` = `order`.`recipientID`
    JOIN `chartconnect`.`enterprise_interface` ON `enterprise_interface`.`enterpriseID` = `location`.`enterpriseID`
    JOIN `fivek`.`chcInterfaceUser` ON `chcInterfaceUser`.`userid` = `enterprise_interface`.`interfaceID` AND FIND_IN_SET('send', `chcInterfaceUser`.`data_direction`)
    LEFT JOIN `fivek`.`chcInterfaceOptions` ON `chcInterfaceOptions`.`interfaceId` = `chcInterfaceUser`.`userID` AND `chcInterfaceOptions`.`value` = '1'
    JOIN `interface`.`messageHeader` ON `messageHeader`.`interfaceID` = `chcInterfaceUser`.`userid` AND	`messageHeader`.`interfaceActionType` = 'SEND'
    JOIN `orders`.`testIdentifier` ON `testIdentifier`.`testIdentifierID` = `orderRecord`.`recordID`
    JOIN `chartconnect`.`user` AS `provider` ON `provider`.`userID` = `order`.`providerID`
    JOIN `fivek`.`chcClinic` ON	`chcClinic`.`liveClinicId` = `order`.`clinicID` AND `chcClinic`.`liveEnterpriseId` = `order`.`enterpriseID`
    AND	`chcClinic`.`communityid` = `order`.`communityID`
    LEFT JOIN `orders`.`codingSystem` ON `codingSystem`.`codingSystemID` = `testIdentifier`.`codingSystemID`
    LEFT JOIN `orders`.`codingSystem` AS `enterpriseCodingSystem` ON`enterpriseCodingSystem`.`enterpriseID` = `location`.`enterpriseID`
    LEFT JOIN `chartconnect`.`suffix` AS `providerNameSuffix` ON `providerNameSuffix`.`suffixID` = `provider`.`suffixID`
    WHERE `order`.`orderID` IN( xxxx) AND `order`.`orderStatusID` > 4
    GROUP BY `orderRecord`.`orderRecordID` 
    ORDER BY `order`.`orderID`, `orderRecord`.`orderRecordID`
    I know the mixture of natural and left joins can be an issue as they can behave un predictably. I am looking at that now, even if it means changing to correlated sub-queries (which I hate doing), but as this is a batch process and not UI based, it is not as bad.

    Thanks

    Jeff

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Hi Jeff,

    you didn't finish the sentence about the issue.

    When I run this, I get the correct `messageHeader`.`interfaceID`if I remove the IFNULL ... AS `enounterID` or IFNULL( ... AS `interfaceOptions` ?????

    Do you get a syntax error or the incorrect rows returned?
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2003
    Location
    Irvine, CA
    Posts
    38
    Roman,

    I never get an error, I just get diiferent interfaceIDs.

    I am thinking I need to change my left joins in to exists, but that is going to change the recordset that is returned greatly and force me to refactor the php. Don't have a lot of time to do that.

    Jeff

    Edit:

    Actually, that won't work either.
    Last edited by JeffJones72; 01-26-15 at 17:23.
    Jeff Jones
    Software Architect
    http://www.linkedin.om/in/jeffjones1972
    jeff@lordjester.com
    425-345-8293

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Also I would check what the impact of your GROUP BY statement. Should you not be grouping by more columns or if you are only grouping for the GROUP_CONCAT can this be gotten via a subquery in the FROM statement and then avoid doing the group on the entire content.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •