[Dbix-class] how to avoid "select =>... as => ..." syntax with simple but long queries e.g. M fields from N tables with decodes and sql-date operations

[Igor Longagnani]

Hi,
a newbie is here again :)
I come from a DBIx::Class RTFM session and I am a little sad because it
doesnt seem helpful with simple stuff like

select
    T1.field1,T1.field2,T2,field3,...,TN.fieldM             ---  (M
fields from N tables)
from
    T1 table1,...TN tableN
where
    joins....and conditions...

I can make nice joins and conditions through the  "search" syntax...
BUT
n cases like the one cited above it seems like you have to use the
"select" syntax to pick fields you need:

        select =3D> qw/ fields...../
        as =3D> qw/alias of fields/

When you have "VERY LONG" number of fields and tables, this last syntax
becomes quite "error-prone".

I am using a "well-formed" and consistent set of DBIx::Classes built
from an Oracle schema, with relations
and has_xxx methods and so on ... Catalyst 5.7006 framework environment.

Any suggestion for something like the following?  I am somehow stuck,
hoping not to have to re-type in "Perl-DBix-Class-mode" this sql which i
didnt built the first time myself :)
Especially for things like (TO_CHAR(SYSDATE, 'J') - TO_CHAR(RF.FIRMADT,
'J')) or some other DECODE?
I even thought to work on the "Controller side" methods of Catalyst to
make some operations, but still i would like to avoid to "prefetch" a
huge amount of data when not strictly necessary and still I'd like to
avoid the "select...as ..." syntax which would force me to re-type every
single field twice ( the as section as well ).

Thanks in advance for your support and ...below follows some SQL to
translate into DBIx::Syntax...
... maybe not all fields are necesasry but most of them are.
Igor

        SELECT DISTINCT
EE.CENTER, R.ALTNO, R.CODPAT , EE.USERID, EE.RAGIONE_SOCIALE,
R.COD_PROGETTO, TO_CHAR(R.FIRMADT,'DD/MM/YYYY'), R.FIRMA,  IQ.DATA,
IQ.NF_PROTOCOLLO_ITA_FILE,     IQ.NF_PROTOCOLLO_ITA, IQ.FILE_NAME,
R.MVALUTAZIONE , IQ.PROPOSTA_RIF, IQ.PROGR, IQ.ID_INFO, =

I.SOTTOTIPO_INT, R.STATO_EVC, R.NOTE_EVC, 'SERVIZIO',  R.FIRMADT,
(TO_CHAR(SYSDATE, 'J') - TO_CHAR(R.FIRMADT, 'J')), R.CLONATO,    =

TO_NUMBER(DECODE(RF.PROGRESSIVO_RICHIESTA,NULL,
'-1',RF.PROGRESSIVO_RICHIESTA,RF.PROGRESSIVO_RICHIESTA)),
DECODE(TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'),NULL,TO_CHAR(R.FIRMADT,'DD/MM/YYYY'=
),
TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'), TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'))        =

,(TO_CHAR(SYSDATE, 'J') - TO_CHAR(RF.FIRMADT, 'J')), =

RF.CONT_INT,RF.STATO_EVC,RF.NOTE_EVC,
TO_DATE(DECODE(TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'),NULL,TO_CHAR(R.FIRMADT,'DD/=
MM/YYYY'),
TO_CHAR(RF.FIRMADT,'DD/MM/YYYY'),TO_CHAR(RF.FIRMADT,'DD/MM/YYYY')),'DD/MM/Y=
YYY')
,
TO_NUMBER(DECODE(RF.STATO_EVC,NULL,R.STATO_EVC,RF.STATO_EVC,RF.STATO_EVC)),
EE.ID_ESERCENTE
        FROM
ANA_ESERCENTI_EE EE , RICHIESTE R, INF_QUANTITATIVE IQ,
RICHIESTE_COORDINATE C, INTERVENTO I, RICHIESTE_FIRMA RF
       WHERE
R.CENTER =3D IQ.CENTER
    AND R.ALTNO =3D IQ.ALTNO
    AND R.CODPAT =3D IQ.CODPAT
    AND R.CENTER =3D I.CENTER
    AND R.ALTNO =3D I.ALTNO
    AND R.CODPAT =3D I.CODPAT
    AND TO_CHAR(R.CENTER) =3D EE.CENTER
    AND R.CENTER=3DC.CENTER
    AND R.ALTNO=3DC.ALTNO
    AND R.CODPAT=3DC.CODPAT
    AND RF.CENTER(+)=3DIQ.CENTER
    AND RF.ALTNO(+)=3DIQ.ALTNO
    AND RF.CODPAT(+)=3DIQ.CODPAT
    AND RF.FIRMA(+)=3DIQ.ID_INFO
    AND RF.FIRMA_PROGR(+)=3DIQ.PROGR
    AND I.ID_INT=3DIQ.PROGR
    AND C.VISITNUM=3D0 AND C.ESAM=3D0
     AND R.FIRMA IS NOT NULL
    AND I.DELETED IS NULL






-- =

Igor Longagnani c/o Synervis
-------------------------------------------------------------------
e-mail: i.longagnani at synervis.it             phone : +39 059 558442

sede operativa: via Pirandello, 49/51 - 41043 Formigine (Mo) Italia

-------------- next part --------------
A non-text attachment was scrubbed...
Name: i.longagnani.vcf
Type: text/x-vcard
Size: 159 bytes
Desc: not available
Url : http://lists.scsys.co.uk/pipermail/dbix-class/attachments/20061227/cc=
925f92/i.longagnani.vcf