Fixing awkward-looking view-related queries
John Dargan asked for a tool to turn the parenthesis-heavy view text the engine stores back into readable SQL. Scott Pickett and Art Kagel knew of none; Art said his myschema tool reformats only partly. Doug Lawry noted GUI SQL editors can beautify the text and suggested an RFE, which John then filed. Paul Watson suggested a sed script. No full solution.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
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' ) ) ;#Informix
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:
https://community.ibm.com/community/user/hybriddatamanagement/viewdocument/informix-1410xc2-features?CommunityKey=cf5a1f39-c21f-4bc4-9ec2-7ca108f0a365&tab=librarydocuments
All presentations and the agenda used by the Roadshow can be found there.
Website for Internet Of Things
https://www.ibm.com/internet-of-things/
Website for Informix
https://www.ibm.com/analytics/us/en/technology/informix/
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
------------------------------
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
------------------------------
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 ------------------------------
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
------------------------------
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
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 ------------------------------