Transcribe the SQL to Arel

Question

Could you help me with this case?? I need to transcribe the SQL below to Arel:

            select sum(X.l_c_v_case) as case
            from fc_low F1,
            TABLE (pg_fg001.fmjc(f1.rowid,trunc(SYSDATE))) V1
            WHERE V1.fic = ? and v1.fiu = ?
            AND V1.fst IN ('A', 'B', 'C')
            AND NVL(V1.ftm,0) NOT IN (701, 711, 721, 731)

Thanks.

Answer 1

I am not sure how this query is working at the moment due to 2 issues:

• You have not defined the multipart identifier X.l_c_v_case; and
• CASE is a keyword so I anticipate this would cause a syntax error

That being said the AST does not care from an assembly standpoint so we can convert your query to Arel as follows: (this does not mean the query will work when executed See Above Comments)

# Table Definitions 
# If these are rails models you could use ModelName#arel_table instead
f1 = Arel::Table.new('fc_low',as: 'f1')
x = Arel::Table.new('X')
# Fake V1 table so we can use the Object like a table reference
faux_v1 = Arel::Table.new('V1')

# Define the table definition for V1
v1_table_def = Arel::Nodes::NamedFunction.new('TABLE',[
  Arel::Nodes::NamedFunction.new('pg_fg001.fmjc',[
    f1[:rowid],
    Arel::Nodes::NamedFunction.new('TRUNC',[Arel.sql('SYSDATE')])
  ])
]).as(faux_v1.table_name)

# Build a Select Manager with the query conditions
# I substituted 1 and 2 for your ?
query = Arel::SelectManager.new([f1,v1_table_def])
  .project(x[:l_c_v_case].sum.as('case')
  .where(
    faux_v1[:fic].eq(1).and(
      faux_v1[:fiu].eq(2)
    ).and( 
      faux_v1[:fst].in(['A','B','C'])
    ).and(
      Arel::Nodes::NamedFunction.new('NVL',[faux_v1[:ftm],0]).not_in([701,711,721,731])
    )
  )

Output of query.to_sql

SELECT 
  SUM("X"."l_c_v_case") AS case 
FROM 
  "fc_low" "f1", 
  TABLE(pg_fg001.fmjc("f1"."rowid", TRUNC(SYSDATE))) AS V1 
WHERE 
  "V1"."fic" = 1 
  AND "V1"."fiu" = 2 
  AND "V1"."fst" IN ('A', 'B', 'C') 
  AND NVL("V1"."ftm", 0) NOT IN (701, 711, 721, 731)