Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: ASP.NET
DROP PROCEDURE IF EXISTS hms.Select_Parameter1;
CREATE PROCEDURE hms.`Select_Parameter1`(_Branch_Id     int,
                                         _Tenant_Id     int,
                                         _disease_Id    int,_ParameterId int)
BEGIN
      IF EXISTS
            (SELECT  b.Target_Value
               FROM patient_progress_tracking_header b
              WHERE b.DiseaseId = _disease_Id)
      THEN
         SELECT a.ParameterName,
                b.Target_Value,
                b.Measurement_Frequency,
                date_format(c.Last_Date,"%y/%d/%m") as 'Last_Date',
                c.Result,
                 date_format(c.Next_Due_Date,"%y/%d/%m") as 'Next_Due_Date',
                c.Remarks,
                date_format(c.Current_Test_Date,"%y/%d/%m") as 'Current_Test_Date',
                b.ParameterId 
                
           FROM patient_progress_tracking_header b
                RIGHT JOIN Disease_parameter a
                   ON a.ParameterId = b.ParameterId
                INNER JOIN patient_progress_tracking_details c
                   ON b.ParameterId = c.ParameterId
          WHERE     b.Branch_Id = _Branch_Id
                AND b.Tenant_Id = _Tenant_Id
                AND b.DiseaseId = _disease_Id;
      ELSE
         SELECT a.ParameterName,
                a.Target_Value,
                b.Measurement_Frequency,
                date_format(c.Last_Date,"%y/%d/%m") as 'Last_Date',
                c.Result,
                 date_format(c.Next_Due_Date,"%y/%d/%m") as 'Next_Due_Date',
                c.Remarks,
                date_format(c.Current_Test_Date,"%y/%d/%m") as 'Current_Test_Date',
                a.ParameterId
                
           FROM patient_progress_tracking_header b
                RIGHT JOIN Disease_parameter a
                   ON  b.ParameterId =a.ParameterId
                INNER JOIN patient_progress_tracking_header b
                   ON b.ParameterId = c.ParameterId
          WHERE     a.Branch_Id = _Branch_Id
                AND a.Tenant_Id = _Tenant_Id
                AND a.DiseaseId = _disease_Id
                AND a.ParameterId=_ParameterId;
      END IF;
   END;
this is my sp. Problem is else part is not executing ...because ON b.ParameterId = c.ParameterId this condition is not present in table..i want when there is no data in patient_progress_tracking_header b table and patient_progress_tracking_header b table ,Target value and parameter name should come from Disease_parameter and displayed in grid..
Posted 29-Dec-12 18:43pm
ujali428
Edited 29-Dec-12 20:25pm
v2

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Hi,
 
In your else part query where is alias table C, c is not declared.
 
SELECT a.ParameterName,
                a.Target_Value,
                b.Measurement_Frequency,
                date_format(c.Last_Date,"%y/%d/%m") as 'Last_Date',
                c.Result,
                 date_format(c.Next_Due_Date,"%y/%d/%m") as 'Next_Due_Date',
                c.Remarks,
                date_format(c.Current_Test_Date,"%y/%d/%m") as 'Current_Test_Date',
                a.ParameterId
                
           FROM patient_progress_tracking_header b
                RIGHT JOIN Disease_parameter a
                   ON  b.ParameterId =a.ParameterId
                INNER JOIN patient_progress_tracking_header b
                   ON b.ParameterId = c.ParameterId
          WHERE     a.Branch_Id = _Branch_Id
                AND a.Tenant_Id = _Tenant_Id
                AND a.DiseaseId = _disease_Id
                AND a.ParameterId=_ParameterId;
  Permalink  
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 Schatak 394
1 OriginalGriff 355
2 Sergey Alexandrovich Kryukov 119
3 _Amy 115
4 Rob Philpott 100
0 OriginalGriff 7,097
1 Sergey Alexandrovich Kryukov 5,623
2 Maciej Los 3,504
3 Peter Leow 3,373
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web02 | 2.8.140721.1 | Last Updated 31 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100