Informix

 View Only
  • 1.  Fixing awkward-looking view-related queries

    Posted Mon April 12, 2021 03:41 PM

    When we create a view using a sensible-looking query, the database converts it into a version that has lots of extra parentheses and odd-looking table names. Below is an example of one of these converted queries. This is a relatively simple example compared to some of the other views we have. My question is whether a tool exists that can convert one of these awkward-looking view-related queries back into a sensible-looking query for ease of understanding.

     

    create view "ifxcita".vwcitationbalance (i_citation_id,i_accounting_id,

           i_transactiontype,c_description,dc_balance,dc_disbursed) as

       select x0.i_citation_id ,x0.i_accounting_id ,x0.i_transactiontype ,

           x1.c_description ,((x1.i_creditdebit * x0.dc_transamount ) + NVL ((

          select sum((x3.i_creditdebit * x2.dc_transamount ) )

          from "ifxcita".citationaccounting x2 ,"ifxcita".transactiontype x3

          where ((x2.i_transactiontype = x3.i_transactiontype ) AND (x2.i_accountingnum = x0.i_accounting_id ) )

        ) ,0. )) ,NVL ((

          select sum(x5.dc_amount )

          from "ifxcita".receiptdetail x4 ,"ifxcita".distributions x5

          where ((x4.i_detail_id = x5.i_detail_id ) AND (x4.i_accounting_id = x0.i_accounting_id ) )

        ) ,0. )

       from "ifxcita".citationaccounting x0 ,"ifxcita".transactiontype x1

       where (((x0.i_transactiontype = x1.i_transactiontype ) AND (x1.i_creditdebit < 0 ) ) AND (x1.c_offsetting = 'N' ) ) ;

     

     

    clerk11s

    John Dargan, Database Administrator II - Information Technology

    LAURA E. ROTH, Clerk of the Circuit Court

    Seventh Circuit, Volusia County, Florida

     

    image002.jpg@01D3169F.07A71D40

    Have I helped you today? Please take the Survey!

     

     

     

     


    #Informix


  • 2.  RE: Fixing awkward-looking view-related queries

    Posted Mon April 12, 2021 04:46 PM
    John,
     
    None that I am aware of. My apologies for your difficulties. To look something like this I presume (I left the upper case/lower case mix for key syntax, could be either or both):
     
    create view "ifxcita".vwcitationbalance (i_citation_id, i_accounting_id, i_transactiontype, c_description, dc_balance, dc_disbursed) as
    select x0.i_citation_id,
              x0.i_accounting_id,
              x0.i_transactiontype,
              x1.c_description,
              ((x1.i_creditdebit * x0.dc_transamount ) +
              NVL (( select sum((x3.i_creditdebit * x2.dc_transamount ) )
                            from "ifxcita".citationaccounting x2 ,"ifxcita".transactiontype x3
                          where ((x2.i_transactiontype = x3.i_transactiontype )
                            AND (x2.i_accountingnum = x0.i_accounting_id ) ) ) ,0. )),
              NVL (( select sum(x5.dc_amount )
                            from "ifxcita".receiptdetail x4 ,"ifxcita".distributions x5
                          where ((x4.i_detail_id = x5.i_detail_id )
                            AND (x4.i_accounting_id = x0.i_accounting_id ) )  ) ,0. )
      from "ifxcita".citationaccounting x0 ,"ifxcita".transactiontype x1
    where (((x0.i_transactiontype = x1.i_transactiontype )
      AND (x1.i_creditdebit < 0 ) )
      AND (x1.c_offsetting = 'N' ) ) ;
     
    Scott Pickett
    IBM Informix WW Technical Sales
    IBM Informix WW Cloud Technical Sales
    IBM Informix WW Cloud Technical Sales ICIAE
    IBM Informix WW Informix Warehouse Accelerator Sales
    Boston, Massachusetts USA
    spickett@us.ibm.com
    617-899-7549
    33 Years Informix User
     
    The current Informix Roadshow presentations are here:
     
     
     





  • 3.  RE: Fixing awkward-looking view-related queries

    IBM Champion
    Posted Mon April 12, 2021 05:42 PM
    John:

    My dbschema replacement tool, myschema attempts to do some reformatting of VIEW text, but it has limited success. The ugly text you see out of dbschema is what was saved by the engine in the sysviews.viewtext column in the rows containing the view definition. Parsing through that and reformatting it is non-trivial. Myschema does some, but it is far from what I would consider "good" at it. I don't know of any other tool that even tries.

    If you don't have it, you can get myschema by downloading the utils2_ak package from my web site (www.askdbmgt.com/my-utilities), extract the shell archive, and build it. It builds out of the box on Linux and Solaris and there are instructions for modifying the make file to get it to compile under AIX and HP/UX embedded in the makefile and in the BUILDING doc file. Myschema's many options are self-documenting if you execute it with no options.

    Art

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 4.  RE: Fixing awkward-looking view-related queries

    Posted Tue April 13, 2021 07:06 AM

    Hi John.

    Most GUI SQL editors can reformat or beautify highlighted statements. Here are some examples applied to your view code:

    AGS Server Studio

    CREATE VIEW "ifxcita".vwcitationbalance(
         i_citation_id,  
         i_accounting_id,
         i_transactiontype,  
         c_description,
         dc_balance,  
         dc_disbursed) AS
      SELECT x0.i_citation_id,
         x0.i_accounting_id,
         x0.i_transactiontype,
         x1.c_description,
        ((x1.i_creditdebit * x0.dc_transamount) + NVL((
            SELECT sum((x3.i_creditdebit * x2.dc_transamount))
            FROM "ifxcita".citationaccounting x2, "ifxcita".transactiontype x3
            WHERE((x2.i_transactiontype = x3.i_transactiontype)
                 AND(x2.i_accountingnum = x0.i_accounting_id))), 0.)),
         NVL(
          (
            SELECT sum(x5.dc_amount)
            FROM "ifxcita".receiptdetail x4, "ifxcita".distributions x5
            WHERE((x4.i_detail_id = x5.i_detail_id)
                 AND(x4.i_accounting_id = x0.i_accounting_id))),
           0.)
      FROM "ifxcita".citationaccounting x0, "ifxcita".transactiontype x1
      WHERE(((x0.i_transactiontype = x1.i_transactiontype)
           AND(x1.i_creditdebit < 0))
           AND(x1.c_offsetting = 'N'));


    SQuirreL SQL Client

    create view "ifxcita".vwcitationbalance
    (
       i_citation_id,
       i_accounting_id,
       i_transactiontype,
       c_description,
       dc_balance,
       dc_disbursed
    )
    as
    select
    x0.i_citation_id,
    x0.i_accounting_id,
    x0.i_transactiontype,
    x1.c_description,
    ((x1.i_creditdebit * x0.dc_transamount ) + NVL (( select sum((x3.i_creditdebit * x2.dc_transamount ) ) from "ifxcita".citationaccounting x2,"ifxcita".transactiontype x3 where ((x2.i_transactiontype = x3.i_transactiontype ) AND (x2.i_accountingnum = x0.i_accounting_id ) ) ),0. )),
    NVL
    (
       ( select sum(x5.dc_amount ) from "ifxcita".receiptdetail x4,"ifxcita".distributions x5 where ((x4.i_detail_id = x5.i_detail_id ) AND (x4.i_accounting_id = x0.i_accounting_id ) ) ),
       0.
    )
    from "ifxcita".citationaccounting x0,"ifxcita".transactiontype x1
    where
    (
       ((x0.i_transactiontype = x1.i_transactiontype ) AND (x1.i_creditdebit < 0 ) )
       AND (x1.c_offsetting = 'N' )
    )
    ;


    DBeaver

    create view "ifxcita".vwcitationbalance (i_citation_id,
    i_accounting_id,
    i_transactiontype,
    c_description,
    dc_balance,
    dc_disbursed) as
    select
    	x0.i_citation_id ,
    	x0.i_accounting_id ,
    	x0.i_transactiontype ,
    	x1.c_description ,
    	((x1.i_creditdebit * x0.dc_transamount ) + NVL ((
    	select
    		sum((x3.i_creditdebit * x2.dc_transamount ) )
    	from
    		"ifxcita".citationaccounting x2 ,
    		"ifxcita".transactiontype x3
    	where
    		((x2.i_transactiontype = x3.i_transactiontype )
    			and (x2.i_accountingnum = x0.i_accounting_id ) ) ) ,
    	0. )) ,
    	NVL ((
    	select
    		sum(x5.dc_amount)
    	from
    		"ifxcita".receiptdetail x4 ,
    		"ifxcita".distributions x5
    	where
    		((x4.i_detail_id = x5.i_detail_id )
    			and (x4.i_accounting_id = x0.i_accounting_id ) ) ) ,
    	0. )
    from
    	"ifxcita".citationaccounting x0 ,
    	"ifxcita".transactiontype x1
    where
    	(((x0.i_transactiontype = x1.i_transactiontype )
    		and (x1.i_creditdebit < 0 ) )
    		and (x1.c_offsetting = 'N' ) ) ;


    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 5.  RE: Fixing awkward-looking view-related queries

    IBM Champion
    Posted Tue April 13, 2021 08:18 AM
    Edited by System Fri January 20, 2023 04:10 PM
    Doug:

    But that's not a whole lot better than what the engine saves and about what myschema accomplishes. I think what the OP, and me for that matter, would rather see is something closer to what the original text looked like. If I hand coded it (OK, I did) it would look something like what Scott presented or like this:

    create view "ifxcita".vwcitationbalance(
                i_citation_id,
                i_accounting_id,
                i_transactiontype,
                c_description,
                dc_balance,
                dc_disbursed )
    as
    select x0.i_citation_id,
           x0.i_accounting_id,
           x0.i_transactiontype,
           x1.c_description,
           ((x1.i_creditdebit * x0.dc_transamount ) + NVL(
                  select sum( x3.i_creditdebit * x2.dc_transamount )
                  from "ifxcita".citationaccounting x2,
                       "ifxcita".transactiontype x3
                  where x2.i_transactiontype = x3.i_transactiontype
                    and x2.i_accountingnum = x0.i_accounting_id,
               0. )),
           NVL(
                  select sum(x5.dc_amount)
                  from "ifxcita".receiptdetail x4,
                       "ifxcita".distributions x5
                  where x4.i_detail_id = x5.i_detail_id
                    and x4.i_accounting_id = x0.i_accounting_id,
               0. )
    from "ifxcita".citationaccounting x0,
         "ifxcita".transactiontype x1
    where x0.i_transactiontype = x1.i_transactiontype
      and x1.i_creditdebit < 0
      and x1.c_offsetting = 'N';

    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------



  • 6.  RE: Fixing awkward-looking view-related queries

    IBM Champion
    Posted Tue April 13, 2021 08:34 AM
    I simple sed script should do most of the work in most instances

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 7.  RE: Fixing awkward-looking view-related queries

    Posted Tue April 13, 2021 07:27 AM
    You could also create an RFE for the original view source to be retained in a system catalog table like SQL Server :-)

    https://ibm-data-and-ai.ideas.aha.io/?project=INFX

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------



  • 8.  RE: Fixing awkward-looking view-related queries

    Posted Tue April 13, 2021 09:32 AM
    I have found your new RFE and voted for it, John. :-)

    https://ibm-data-and-ai.ideas.aha.io/ideas/INFX-I-403

    ------------------------------
    Doug Lawry
    Oninit Consulting
    ------------------------------