Hitachi Vantara Pentaho Community Forums
Page 1 of 3 123 LastLast
Results 1 to 10 of 26

Thread: SQL Case to Pentaho... best way?

  1. #1
    Join Date
    Feb 2011
    Posts
    840

    Default SQL Case to Pentaho... best way?

    so, I have this piece of... clouding things around here. I'm still working on adapting old SQL stored procedures to Pentaho, and the one I'm working on right now uses a function created here that has this case:
    Code:
    CASE @Caracter    WHEN 'A' THEN CAST(@CadeiaValor + '1' AS numeric)/100
        WHEN 'B' THEN CAST(@CadeiaValor + '2' AS numeric)/100
        WHEN 'C' THEN CAST(@CadeiaValor + '3' AS numeric)/100
        WHEN 'D' THEN CAST(@CadeiaValor + '4' AS numeric)/100
        WHEN 'E' THEN CAST(@CadeiaValor + '5' AS numeric)/100
        WHEN 'F' THEN CAST(@CadeiaValor + '6' AS numeric)/100
        WHEN 'G' THEN CAST(@CadeiaValor + '7' AS numeric)/100
        WHEN 'H' THEN CAST(@CadeiaValor + '8' AS numeric)/100
        WHEN 'I' THEN CAST(@CadeiaValor + '9' AS numeric)/100
        WHEN 'J' THEN CAST(@CadeiaValor + '1' AS numeric)/(-100)
        WHEN 'K' THEN CAST(@CadeiaValor + '2' AS numeric)/(-100)
        WHEN 'L' THEN CAST(@CadeiaValor + '3' AS numeric)/(-100)
        WHEN 'M' THEN CAST(@CadeiaValor + '4' AS numeric)/(-100)
        WHEN 'N' THEN CAST(@CadeiaValor + '5' AS numeric)/(-100)
        WHEN 'O' THEN CAST(@CadeiaValor + '6' AS numeric)/(-100)
        WHEN 'P' THEN CAST(@CadeiaValor + '7' AS numeric)/(-100)
        WHEN 'Q' THEN CAST(@CadeiaValor + '8' AS numeric)/(-100)
        WHEN 'R' THEN CAST(@CadeiaValor + '9' AS numeric)/(-100)
        WHEN '{' THEN CAST(@CadeiaValor + '0' AS numeric)/100
        WHEN '}' THEN CAST(@CadeiaValor + '0' AS numeric)/(-100)
        else cast(@CadeiaValor + @Caracter as numeric)/100
    END
    My first thought was: okay, Switch/Case... but then, 21 choices for a switch/case seemed a bit too much. You guys have any better ideas?
    Oh yeah, worst thing: this isn't used only once. There are at least 3 fields using this "code something on field X alters the value from field Y"...
    Last edited by joao.ciocca; 06-25-2012 at 05:02 PM.
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  2. #2
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    Optimally you would perform some ASCII arithmetic.
    Worst case you have a piece of JavaScript with the switch/case in it.

  3. #3

    Default

    Try following for mysql

    SELECT (-2 * LOCATE('@Caracter','{ABCDEFGHI-JKLMNOPQR}') DIV 11 + 1) * CONCAT('@CadeiaValor',((LOCATE('@Caracter','{ABCDEFGHI-JKLMNOPQR}') % 11) - 1)) /100
    explanation:
    -2 * LOCATE('@Caracter','{ABCDEFGHI-JKLMNOPQR}') DIV 11 + 1) maps the following chars {ABCDEFGHI to 1 and }JKLMNOPQR to -1
    LOCATE(@Caracter,'{ABCDEFGHI-}JKLMNOPQR') % 11 - 1 maps to 0..9
    example
    SELECT (-2 * LOCATE('A','{ABCDEFGHI-JKLMNOPQR}') DIV 11 + 1) * CONCAT('123',((LOCATE('A','{ABCDEFGHI-JKLMNOPQR}') % 11) - 1)) /100
    result:12.31

    You can figure out what to do with the numerical characters and do note that I'm using '-' to separate the 2 ranges
    Last edited by mario_daniels; 06-26-2012 at 11:21 AM.

  4. #4
    Join Date
    Feb 2011
    Posts
    840

    Default

    hm... I never used mysql... this stored procedure runs on MS SQL Server 8.0 - and since I'm not that good with SQL (I know the basics, but I suck at programming in general... I'm still trying to understand your select, Mario), that was one of the reasons to migrate this routine to PDI.

    Matt, do you have any examples on that ASCII arithmetic you mentioned?
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  5. #5

    Default

    I'll break it down in 2 steps:
    the first step should provide the mapping to a 0:9 and the second step should determine whether it is
    positive or negative.

    step 1: (just imagine that the following 2 lines are in a fixed width font)
    { A B C D E F G H I - { J K L M N O P Q R
    1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21
    Position of B in string is 3, therefore 3 mod 11 = 3. However you need to subtract 1 to get the correct result ( 3 mod 11 - 1)
    Position of K in string is 14, therefore 14 mod 11 = 3 etc etc etc
    sql: patindex('{ABCDEFGHI-{JKLMNOPQR',@Caracter) - 1
    javascript: foo = '{ABCDEFGHI-{JKLMNOPQR';foo.indexof(@Caracter) (<== you don't have to substract because indexof starts at 0)

    step 2:
    You can map 0 -> 1 and 1 -> -1, by the -2 * x + 1
    Position of B in string is 3, i.e within the first 11 position, therefore 3 div 11 = 0.
    Position of K in string is 14, i.e in the last 11 position, which is the second part of your string 14 div 11 = 1
    sql: -2 * patindex('{ABCDEFGHI-{JKLMNOPQR','@Caracter) div 11 + 1

    If you're doing your transformation in postgresql, the obviously you will have to replace patindex with position.
    cheers
    Last edited by mario_daniels; 06-26-2012 at 05:59 PM.

  6. #6
    Join Date
    Apr 2009
    Posts
    21

    Default

    An alternative way would be:
    1. put the constants of each CASE in a Data Grid:

    key add div
    'A' 1 100
    'B' 2 100
    'C' 3 100
    ...
    '}' 0 -100

    2. hook up a Stream lookup to the incoming data using the datagrid as Lookup step

    Field: Caracter
    LookupField: Key

    set default value for div to 100

    3. split stream using Filter rows, condition: [add] IS NULL

    4. use Set field value to copy value of [Caracter] to [add] if condition is true

    5. join streams again in Select values to cast [CadeiaValor] values to Number
    At this point all the values you need to calculate the result are on the same row.

    6. use a Calculator (or Formula or javascript) to calculate values

    ==
    Not as compact as a small javascript script, but true to the nature of PDI graphic programming.


    enjoy,
    Thijs Verhagen

  7. #7
    Join Date
    Nov 1999
    Posts
    9,729

    Default

    I like this idea a lot actually :-)

  8. #8
    Join Date
    Feb 2011
    Posts
    840

    Default

    wow, that was awesome Thijs! I could actually picture it while I read! I'm gonna try this and get back to you =) thanks!
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  9. #9
    Join Date
    Feb 2011
    Posts
    840

    Default

    okay, I'm implementing this now but... let's check it out, I'm having a couple doubts:

    function is called like this:
    Code:
    FUNCTION ValorCaracter (@Caracter char(1) , @CadeiaValor as varchar(16))RETURNS Numeric(16,2) AS  
    BEGIN 
    DECLARE @Valor as Numeric(16,2)
    SET @Valor = 
    CASE @Caracter
    	WHEN 'A' THEN CAST(@CadeiaValor + '1' AS numeric)/100
    	WHEN 'B' THEN CAST(@CadeiaValor + '2' AS numeric)/100
    	WHEN 'C' THEN CAST(@CadeiaValor + '3' AS numeric)/100
    	WHEN 'D' THEN CAST(@CadeiaValor + '4' AS numeric)/100
    	WHEN 'E' THEN CAST(@CadeiaValor + '5' AS numeric)/100
    	WHEN 'F' THEN CAST(@CadeiaValor + '6' AS numeric)/100
    	WHEN 'G' THEN CAST(@CadeiaValor + '7' AS numeric)/100
    	WHEN 'H' THEN CAST(@CadeiaValor + '8' AS numeric)/100
    	WHEN 'I' THEN CAST(@CadeiaValor + '9' AS numeric)/100
    	WHEN 'J' THEN CAST(@CadeiaValor + '1' AS numeric)/(-100)
    	WHEN 'K' THEN CAST(@CadeiaValor + '2' AS numeric)/(-100)
    	WHEN 'L' THEN CAST(@CadeiaValor + '3' AS numeric)/(-100)
    	WHEN 'M' THEN CAST(@CadeiaValor + '4' AS numeric)/(-100)
    	WHEN 'N' THEN CAST(@CadeiaValor + '5' AS numeric)/(-100)
    	WHEN 'O' THEN CAST(@CadeiaValor + '6' AS numeric)/(-100)
    	WHEN 'P' THEN CAST(@CadeiaValor + '7' AS numeric)/(-100)
    	WHEN 'Q' THEN CAST(@CadeiaValor + '8' AS numeric)/(-100)
    	WHEN 'R' THEN CAST(@CadeiaValor + '9' AS numeric)/(-100)
    	WHEN '{' THEN CAST(@CadeiaValor + '0' AS numeric)/100
    	WHEN '}' THEN CAST(@CadeiaValor + '0' AS numeric)/(-100)
    	else cast(@CadeiaValor + @Caracter as numeric)/100
    END
    RETURN(convert(Numeric(16,2),@Valor))
    END
    
    Vlr_Contrato = convert(numeric(16,2),CONTROLE_CARGA.dbo.ValorCaracter(SUBSTRING(Col001,191,1),substring(Col001,177,14))),
    since all the substrings have already been treated to fields, I created a "Cod_Vlr_Contrato" that contains substring(Col001,191,1), and a field "Vlr_Contrato" containing substring(Col001,177,14).
    You said that "if add is null, copy value of Cod_Vlr_Contrato. Okay, since add will be null if Cod_Vlr_Contrato doesn't match any of the listed options, the add field will containg Cod_Vlr_Contrato.

    I don't need to cast, since Vlr_Contrato and other fields are already being read as integers... ... okay, if they're int I need to, since they'll be divided by 100/-100.

    and I just found a problem here. This function is called many times, for different fields. I mentioned the field "Vlr_Contrato" before. There are, on this input I'm testing, other 45 fields. So, unless I add a huge overhead by creating 45 new fields... that goes kaboom. New ideas? =p
    Join us on IRC! =)

    Twitter / Google+ / Timezone: BRT-BRST
    BI Server & PDI 5.4 / MS SQL 2012 / Learning CDE & CTools
    Windows 8 64-bit / Java 7 (jdk1.8.0_75)

    Quote Originally Posted by gutlez
    PLEASE NOTE: No forum member is going to do your work for you. We will help you sort out how to do a specific part of the work, as best we can, in the timelines that our work will allow us.

    I'm no expert.Take my comments at your own risk.

  10. #10

    Default

    An slight change to Thijs's alternative way would be:

    1. put the constants of each CASE in a Data Grid:

    key add div
    'A' 1 100
    'B' 2 100
    'C' 3 100
    ...
    '}' 0 -100
    '0' 0 100
    .
    .
    '9' 9 100

    2. use Join rows(cartesian products) using the datagrid and your input table/stream
    whereby caracter = key

    3. use Formula to calculate values
    ((int([cadeivalor])* 10) +[add])/[div]
    Last edited by mario_daniels; 06-29-2012 at 09:48 AM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Privacy Policy | Legal Notices | Safe Harbor Privacy Policy

Copyright © 2005 - 2019 Hitachi Vantara Corporation. All Rights Reserved.