Store a value into a variable & use it among statements & functions

[Former Member]

Hi Guys,

Here I come again to bother you. My script as below:

    UNLOAD

        SELECT

            REPLACE((SUBSTRING(dbo.dly_srv_prd_trk_ttl.business_date,1,7)),'-','0'),
            DATEFORMAT(dbo.dly_srv_prd_trk_ttl.business_date,'dd/mm/yyyy'),
            dbo.dly_srv_prd_trk_ttl.trk_ttl_01,
            **IF '110' IN (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) THEN 'BS'
             ELSE
             IF '120' IN (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) THEN 'SM'
              ELSE
              IF '130' IN (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) THEN 'LV'
               ELSE
               IF '140' IN (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) THEN 'LFC'
                ELSE
                IF '150' IN (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) THEN 'CF'
                 ELSE
                 IF '160' IN (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) THEN 'LFC'
                  ELSE 'OTHERS'
                 ENDIF
                ENDIF
               ENDIF
              ENDIF
             ENDIF
            ENDIF**

    WHERE cast((getDate())-1 As Date) = cast(dbo.dly_srv_prd_trk_ttl.business_date As Date) --Parameter for yesterday

TO 'D:\\DATA.txt' FORMAT FIXED


I want all the IF functions to be run and stored into a variable so that I can re-use the variable over and over again in the script without having to re-write/re-run every IF functions like above. I have no idea how to start, can you please guide me on this?

[VolkerBarth]

What does your subquery "SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl" return - a list of values or a single value?

If it is a single value, you would usually just select the value once and then use a CASE expression to map the desired output to the expected values, such as

SELECT (SELECT rvc_seq FROM dbo.dly_srv_prd_trk_ttl) AS ttl,
CASE ttl
  WHEN '110' THEN 'BS'
  WHEN '120' THEN 'SM'
  ...
END CASE,


A completely different approach would be a (temporary?) table to map the rvc_seq values to the output values, so that you could build them by joining...

[Former Member]

Hi Volker,

It'll be a list of value. Not sure I can paste a image over the comment, I'll just try. The result will be like below when pulling the value from rvc_seq:

[VolkerBarth]

Hm, to refine my question: Will "SELECT DISTINCT rvc_seq FROM dbo.dly_srv_prd_trk_ttl" return more than one value, say, "110" and "120" for the same query?

If so, is my understanding correct that you have a "priority rule", i.e. if anyone of these values is "110" then return "BS" else if anyone of these values is "120" then "SM" and the like? - That would mean the order of conditions is important here.

---

I'm asking this as a function that would only map one input value to one output value is apparently easier to write than one function that would need a set (or list) of input values...

[Former Member]

Hi Volker,

Sorry for the late reply. I appreciate your answer. But by doing this I have to repeat the function of CASE and I have total of 64 sets in UNION to output a data file and this will be a lot for me and will become harder for the person to troubleshoot on this next time.

[VolkerBarth]

Could you please answer my latest question - i.e. whether "SELECT DISTINCT rvc_seq FROM dbo.dly_srv_prd_trk_ttl" may return more than one value, say, "110" and "120", for the same query?

[Former Member]

Hi Volker,

It's a single value.

[Breck_Carter · Accepted solution]

CREATE TABLE dbo.dly_srv_prd_trk_ttl (
   rvc_seq VARCHAR ( 100 ) );
INSERT dly_srv_prd_trk_ttl VALUES ( 'XXX' );
INSERT dly_srv_prd_trk_ttl VALUES ( '160' );
INSERT dly_srv_prd_trk_ttl VALUES ( '120' );
INSERT dly_srv_prd_trk_ttl VALUES ( '140' );
COMMIT;

BEGIN
DECLARE @rvc_seq_list LONG VARCHAR;
DECLARE @mapped_value VARCHAR ( 100 );

SELECT LIST ( DISTINCT STRING ( '[', rvc_seq, ']' ) )
  INTO @rvc_seq_list
  FROM dbo.dly_srv_prd_trk_ttl
 WHERE rvc_seq IN ( '110', '120', '130', '140', '150', '160' );

SET @mapped_value = CASE
   WHEN LOCATE ( @rvc_seq_list, '[110]' ) > 0 THEN 'BS'
   WHEN LOCATE ( @rvc_seq_list, '[120]' ) > 0 THEN 'SM'
   WHEN LOCATE ( @rvc_seq_list, '[130]' ) > 0 THEN 'LV'
   WHEN LOCATE ( @rvc_seq_list, '[140]' ) > 0 THEN 'LFC'
   WHEN LOCATE ( @rvc_seq_list, '[150]' ) > 0 THEN 'CF'
   WHEN LOCATE ( @rvc_seq_list, '[160]' ) > 0 THEN 'LFC'
   ELSE 'OTHERS'
END CASE;

SELECT @rvc_seq_list, @mapped_value;
END;

@rvc_seq_list       @mapped_value
[120],[140],[160]   'SM'

[Former Member · Accepted solution]

Hi Breck,

Thanks for giving this fantastic solution. I have a question, if using your solution, I must put every statements and functions in between of "BEGIN...END;", am I correct?

The reason behind is I am actually making a script to export a few columns of data from a table and will insert some hard-coded logical function so am wondering do these work inside BEGIN and END?

Many thanks.

[VolkerBarth]

Based on Breck's table and sample, here's a sample to use a function to calculate the desired category (or whatever you call it) from the list of rcv_seq values:

-- sample table from Breck's sample
CREATE TABLE dbo.dly_srv_prd_trk_ttl (
   rvc_seq VARCHAR ( 100 ) );
INSERT dly_srv_prd_trk_ttl VALUES ( 'XXX' );
INSERT dly_srv_prd_trk_ttl VALUES ( '160' );
INSERT dly_srv_prd_trk_ttl VALUES ( '120' );
INSERT dly_srv_prd_trk_ttl VALUES ( '140' );
COMMIT;


-- create the function that returns the according catecory from a list of rvc_seq values
CREATE FUNCTION dbo.FN_rvc_seg_category( @rvc_seq_list LONG VARCHAR )
RETURNS VARCHAR (100)
BEGIN
   DECLARE @return_value VARCHAR ( 100 );
   SET @return_value = CASE
      WHEN LOCATE ( @rvc_seq_list, '[110]' ) > 0 THEN 'BS'
      WHEN LOCATE ( @rvc_seq_list, '[120]' ) > 0 THEN 'SM'
      WHEN LOCATE ( @rvc_seq_list, '[130]' ) > 0 THEN 'LV'
      WHEN LOCATE ( @rvc_seq_list, '[140]' ) > 0 THEN 'LFC'
      WHEN LOCATE ( @rvc_seq_list, '[150]' ) > 0 THEN 'CF'
      WHEN LOCATE ( @rvc_seq_list, '[160]' ) > 0 THEN 'LFC'
      ELSE 'OTHERS'
   END CASE;
   RETURN @return_value;
END;

-- use the function within a select statement (where you have to build an according rvc_seq list)
SELECT LIST ( DISTINCT STRING ( '[', rvc_seq, ']' ) ) as rvc_seq_list,
   dbo.FN_rvc_seg_category( rvc_seq_list ) as category
 FROM dbo.dly_srv_prd_trk_ttl
 WHERE rvc_seq IN ( '110', '120', '130', '140', '150', '160' );

-- returns
rvc_seq_list, category
[120],[140],[160], 'SM'


That way, you can use the function in several queries and several query parts...
However, as already asked, it would be way easier to call that function if there would be just one single rcv_seq value and not a whole list per call, so you should clarify this requirement.

[Former Member]

Hi Volker,

Many thanks for the solution. It's helpful.

I've just received information that the logical part has to be even more. For example,

If rvc_seq='1' and (trk_ttl_01>0 or trk_ttl_02>0 or trk_ttl_03>0), value='BS'. This is killing me and I am just a beginner at Sybase. I wonder is it possible to write in more logical options into the function or into a variable as what Breck has done.

[VolkerBarth]

In case your condition needs only to evaluate one "rvq_seq" value at once (and possibly one value for each of these other columns like trk_ttl_01), it's quite easy to establish a common function for that, such as

-- create the function that returns the according catecory based on several input values
CREATE FUNCTION dbo.FN_rvc_seg_category( @rvc_seq VARCHAR (100), @trk_ttl_01 INT, @trk_ttl_02 INT, @trk_ttl_03 INT )
RETURNS VARCHAR (100)
BEGIN
   DECLARE @return_value VARCHAR ( 100 );
   SET @return_value = CASE
      WHEN @rvc_seq = '110' AND (@trk_ttl_01 > 0 OR @trk_ttl_02 > 0 OR @trk_ttl_03 > 0) THEN 'BS'
      -- enhance the following conditions similarly
      WHEN @rvc_seq = '120' THEN 'SM'
      WHEN @rvc_seq = '130' THEN 'LV'
      WHEN @rvc_seq = '140' THEN 'LFC'
      WHEN @rvc_seq = '150' THEN 'CF'
      WHEN @rvc_seq = '160' THEN 'LFC'
      ELSE 'OTHERS'
   END CASE;
   RETURN @return_value;
END;


-- enhanced sample table
CREATE TABLE dbo.dly_srv_prd_trk_ttl (
   rvc_seq VARCHAR ( 100 ),
   trk_ttl_01 INT,
   trk_ttl_02 INT,
   trk_ttl_03 INT );
INSERT dly_srv_prd_trk_ttl VALUES ( 'XXX', 0, 0, 1 );
INSERT dly_srv_prd_trk_ttl VALUES ( '110', 1, 0, 1  );
INSERT dly_srv_prd_trk_ttl VALUES ( '160', 0, 1, 0  );
INSERT dly_srv_prd_trk_ttl VALUES ( '120', 0, 0, 0  );
INSERT dly_srv_prd_trk_ttl VALUES ( '140', 1, 1, 1  );
COMMIT;

-- call the function and use the column values as function arguments
select *, dbo.FN_rvc_seg_category(rvc_seq, trk_ttl_01, trk_ttl_02, trk_ttl_03)
from dbo.dly_srv_prd_trk_ttl;

-- returns
XXX,0,0,1,OTHERS
160,0,1,0,LFC
140,1,1,1,LFC
120,0,0,0,SM
110,1,0,1,BS

[MCMartin]

A case expression would be simpler than your "ifs", but it wouldn't overcome your principal retyping need.

Best would be to write your own function: check create function, then you can reuse that function in all other selects as you like.

[Former Member]

Hi Martin,

I had tried that but unfortunately I am weak on this. Despite of creating a function I need to write other functions in order to make the concept works.