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
------------------------------
Original Message:
Sent: Mon April 12, 2021 03:41 PM
From: John Dargan
Subject: Fixing awkward-looking view-related queries
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' ) ) ;
| John Dargan, Database Administrator II - Information Technology LAURA E. ROTH, Clerk of the Circuit Court Seventh Circuit, Volusia County, Florida |
Have I helped you today? Please take the Survey! |
#Informix