Announcement

Collapse
No announcement yet.

NAB: not possible to filter on fields from an SQL With statement

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • NAB: not possible to filter on fields from an SQL With statement

    Not really sure if this topic should be a new feature request or a bug report..


    I'm trying to convert an existing sql-QMquery into an NAB-app,
    as you know, when launching an QMQRY, a user gets prompted for the values of the "&" fields... in my case a from-to date.. (user needs to enter f.e "2018-01-01'' (with the quotes included... users tend to mistype, or not understand how to enter the values.. Therefore, i wanted to convert this one into a Nitro App, in order to allow a user to filter on the more userfriendly calender fields.


    i could create my Datasource, and wanted to create the Grid Widget, however when i want to specify filter fields, i can only select fields from the basic sql files .. (not those from the WITH)

    is there a solution to this??


    the existing query :
    PHP Code:
    WITH wpl (Wntoolwnartkwnprodwqamagwdtrlg_Fwdtrlf_T,        
    wnmachLog) as                                                      
       (
    SELECT PlntoolPlnartkplnprod,                                
              
    decimal(max(plqatop), 110),                              
              
    max(pldpstr),                                              
              
    max(pldpein),                                              
              
    max(plnmach), 'LOG'                                        
        
    FROM  cohplpf                                                    
        WHERE plqatop
    <> and                                            
              
    pldpstr between &ALF_DATEFROM_JJJJ_MM_DD and                 
                              &
    ALF_DATETILL_JJJJ_MM_DD                     
        GROUP BY plntool
    plnartkplnprod                               
      Union                                                              
        Select PoNtool
    PonartkPoNprodPoQatopPoDpstrPoDpein,     
               
    PoNmach'ACT'                                            
        
    From Cohpopf exception join CoHplpf on                           
               Plnprod 
    Ponprod                                         
        Where podpstr between 
    &ALF_DATEFROMJJJJ_MM_DD and                 
                              &
    ALF_DATETILL_JJJJ_MM_DD),
      
    Waf (W2NartkW2Qty) as                               
        (
    Select Afnartkdecimal(sum(AfQafrp-AfQafrl), 90)
         
    From Cohafpf                                       
         Group By Afnartk                                   
         Having Sum
    (AfQafrp AfQafrl) > 0)                 
    Select wpl.*, W2Qty as AfroepHvh                        
     FROM Wpl INNER JOIN COBARPF on                         
           ARNARTK 
    WNARTK AND ARSTYPR <> 'P'              
          
    Left outer join Waf on                            
           W2Nartk 
    WNartk
    order by Wntool
    WnartkWdtrlf_T 


  • #2
    Hi Thierry,

    This would be a feature request and we have added it to our tracking system. Thanks

    Comment


    • #3
      Hi Thierry,

      As a workaround, assuming you have a calendar file, join that to the field from your CTE. Then you can filter on that date and effectively be filtering on the CTE.

      Note, if you need to transform a numeric date to a true date as well, I think I just found a bug in the generated SQL created by the initial date value... It is inserting a DISTINCT keyword in the Where clause portion added for the initial date value.

      Being able to select fields from the CTE will be good too, but in the meantime maybe this will help.

      David

      Comment


      • #4
        Thierry,

        This will be available in the next upcoming update to Valence 5.2. You will be able to pull in filter values so they can be used in your Data Source. Any filters will be available to vvIn_parm. The filter parameter names will always be "filter_" plus the field name.

        Example Data Source using a filter value:
        Code:
        WITH wpl(cusno, cname, ccountry) AS (
          SELECT
            cusno,
            cname,
            ccountry
          FROM
            democmast
          WHERE
            (ccountry = vvin_parm('filter_CCOUNTRY'))
        )
        SELECT
          wpl.*
        FROM
          wpl

        Comment

        Working...
        X