CODEWITHSUNDEEP
oracle-databasestored-proceduresplsqlcase
bradleykins
bradleykins

Reputation: 97

How to identify a null parameter and deal with it SQL Developer Stored Function

Basically I have a stored function on a DB, I have recreated it quickly here, and what I have done and need to do is this:

The P_METHOD parameter is a new one, which I added because I want only the rows of data specific to the calling parameters to be displayed. For example the test T1 is the method, and I want only the results associated with the T1 to be displayed, currently we have all results being put into a string and output for all the methods, obviously this is incorrect and I really want to sort it so its by the method.

I thought it could be easily done by making this method parameter and doing a where clause statement but apparently we also have null methods, where the result is not associated with any method and for my needs I need to output these as a completely separate result string. So null is a valid method.

I figured I need to use case statements to do this but can't quite figure it out also, I cannot add any data to the DB at all only a stored function as this is running through ireports and its outputting a report based on the results.

Any help would be appreciated please find some example code below:

New Code

CREATE OR REPLACE FUNCTION GETRESULTS(p_sampleid VARCHAR2, p_fam NUMBER, p_method VARCHAR2) RETURN VARCHAR2
IS
  CURSOR res_cursor IS
    SELECT
      res.result,
      tests.libelle
    FROM results res
      INNER JOIN tests
        ON res.code = tests.code
           AND tests.fam = p_fam
    WHERE res.sampleid = p_sampleid
          AND res.result NOT LIKE '%#%'
          AND CASE p_method
              WHEN IS NULL THEN (res.methodid IS NULL)
              WHEN IS NOT NULL THEN (res.methodid = (SELECT methodid FROM TEST_METHOD WHERE lib_method = P_METHOD))
    ORDER BY tests.code;

  singleres VARCHAR2(100);
  rescode   VARCHAR2(100);
  resultstr VARCHAR2(2000);
  sep       VARCHAR(3);
  BEGIN
    sep := '';

    OPEN res_cursor;
    FETCH res_cursor INTO singleres, rescode;

    WHILE res_cursor%FOUND
    LOOP
      resultstr := resultstr || sep || singleres;
      sep := '; ';
      FETCH res_cursor INTO singleres, rescode;
    END LOOP;

    CLOSE res_cursor;

    RETURN (resultstr);
  END getresults;

Current Code:

CREATE OR REPLACE FUNCTION getresults(p_sampleid VARCHAR2, p_fam NUMBER) RETURN VARCHAR2
IS
  CURSOR res_cursor
  IS
    SELECT
      res.result,
      tests.libelle
    FROM results res
      INNER JOIN tests ON res.code = tests.code
                          AND tests.fam = p_fam
    WHERE res.sampleid = p_sampleid
          AND res.result NOT LIKE '%#%'
    ORDER BY tests.code;
  singleres VARCHAR2(100);
  rescode   VARCHAR2(100);
  resultstr VARCHAR2(2000);
  sep       VARCHAR(3);

  BEGIN
    sep := '';

    OPEN res_cursor;
    FETCH res_cursor INTO singleres, rescode;

    WHILE res_cursor%FOUND
    LOOP
      resultstr := resultstr || sep || singleres;
      sep := '; ';
      FETCH res_cursor INTO singleres, rescode;
    END LOOP;

    CLOSE res_cursor;

    RETURN (resultstr);
  END getresults;

Upvotes: 1

Views: 136

Answers (1)

Matas Vaitkevicius
Matas Vaitkevicius

Reputation: 61431

you can use this, keep in mind it will ruin your execution plan.

  WHERE res.sampleid = P_SAMPLEID AND res.result NOT LIKE '%#%'
    AND ((P_METHOD IS NULL AND res.methodid is null)   
        OR (P_METHOD IS NOT NULL AND res.methodid = (SELECT methodid FROM TEST_METHOD WHERE lib_method = P_METHOD)))

Upvotes: 2

Related Questions