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 => qw/ fields...../
as => 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/YYYY')
,
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 = IQ.CENTER
AND R.ALTNO = IQ.ALTNO
AND R.CODPAT = IQ.CODPAT
AND R.CENTER = I.CENTER
AND R.ALTNO = I.ALTNO
AND R.CODPAT = I.CODPAT
AND TO_CHAR(R.CENTER) = EE.CENTER
AND R.CENTER=C.CENTER
AND R.ALTNO=C.ALTNO
AND R.CODPAT=C.CODPAT
AND RF.CENTER(+)=IQ.CENTER
AND RF.ALTNO(+)=IQ.ALTNO
AND RF.CODPAT(+)=IQ.CODPAT
AND RF.FIRMA(+)=IQ.ID_INFO
AND RF.FIRMA_PROGR(+)=IQ.PROGR
AND I.ID_INT=IQ.PROGR
AND C.VISITNUM=0 AND C.ESAM=0
AND R.FIRMA IS NOT NULL
AND I.DELETED IS NULL
