Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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

0

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?

asked 11 Apr '14, 04:44

ctlavender's gravatar image

ctlavender
109●4●4●10
accept rate: 0%

edited 11 Apr '14, 04:54

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...

(11 Apr '14, 05:18) Volker Barth
Replies hidden

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:

Result

(11 Apr '14, 05:37) ctlavender

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...

(11 Apr '14, 06:09) Volker Barth

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.

(14 Apr '14, 02:30) ctlavender

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?

(14 Apr '14, 04:47) Volker Barth

Hi Volker,

It's a single value.

(14 Apr '14, 06:09) ctlavender
showing 1 of 6 show all flat view
3 Answers:
active answersoldest answersnewest answerspopular answers
2 
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'
permanent link

answered 11 Apr '14, 08:06

Breck%20Carter's gravatar image

Breck Carter
32.5k●541●726●1050
accept rate: 20%

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.

(14 Apr '14, 02:27) ctlavender
0

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.

permanent link

answered 11 Apr '14, 07:15

Martin's gravatar image

Martin
9.0k●130●169●257
accept rate: 14%

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.

(14 Apr '14, 02:33) ctlavender
0

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.

permanent link

answered 14 Apr '14, 05:03

Volker%20Barth's gravatar image

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 14 Apr '14, 05:04

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,

alt text

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.

(14 Apr '14, 06:08) ctlavender

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
(14 Apr '14, 07:47) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×34
×14

question asked: 11 Apr '14, 04:44

question was seen: 2,285 times

last updated: 14 Apr '14, 07:48

Related questions

How to use Out or INOUT Variables

Order of passing parameters into a procedure

Finding what connection level variables have been defined

Global variables and constants

Procedure with multiple return values

EDM Type vs. DB Type - integer not supported

object o = myCmd.ExecuteScalar() unexpectedly returns null?

Product Suggestion: Please implement collections (arrays)

create procedure without defined result

Calling A Procedure For Every Row In A Select Statement Inside A Stored Proc