Scriptcase SubQuerie

Applications Grid / Reports
#1

Subqueries in SCRIPTCASE NEED TO IMPLEMENT THE SELECT CLAUSE BUT TO PUT A subquery I get an error, YOU KNOW EVEN IF STILL NO SUPPORT FOR subqueries

Also I have problems placing a join with characters
for example

LEFT OUTER JOIN ON CCGO_FACTUR Z (Z = A. NUM_REFE NUM_REFE) AND (Z. CVE_CPTO = ‘IVAG’) AND (Z. CVE_COMP = ‘CTA’)

if I put this line gives me error if I put 336397208 and DNA (Z. CVE_CPTO = ‘IVAG’) AND (Z. CVE_COMP = ‘CTA’) late in the query WHERE exits without error or that the code is to compare with char? if not apostrophe 'or quotation marks "

I have the following query in the SQL Builder gives me no error. but when placed on a grid

SELECT P.NUM_REFE , P.CVE_IMPO ,P.IMP_EXPO ,P.TIP_PEDI ,P.ADU_DESP ,P.PAT_AGEN ,P.NUM_PEDI ,P.ADU_ENTR ,P.FEC_ENTR ,P.TIP_CAMB ,P.CVE_PEDI ,
P.REG_ADUA ,P.FEC_ESPE ,P.DES_ORIG ,P.MAR_NUME ,P.PES_BRUT ,P.MTR_ENTR ,P.MTR_ARRI ,P.MTR_SALI ,P.MON_VASE ,P.TIP_MOVA ,P.VAL_DLLS ,
P.VAL_COME ,P.TOT_INCR ,P.VAL_NORM ,P.FAC_AJUS ,P.AUT_OBSE ,P.CVE_CAPT ,P.FEC_PAGO ,P.CVE_REPR ,P.TOT_EFEC ,P.TOT_OTRO ,P.FIR_ELEC ,
P.CVE_BANC ,P.NUM_CAJA ,P.DIA_PAGO ,P.HOR_PAGO , P.TOT_PAGO ,P.CAN_BULT ,P.FIR_REME ,P.SEC_DESP ,P.FIR_PAGO ,P.NUM_OPER ,
P.REG_ENTRADA ,P.CVE_CNTA , SUM(A1.TOT_IMPU) AS TDTA, SUM(A2.TOT_IMPU) AS TPRE, SUM(A3.TOT_IMPU) AS TREC,
B.NUM_REFE,B.NUM_CONT,B.CVE_CONT,F.NUM_FACT ,F.FEC_FACT ,F.ICO_FACT ,F.VAL_EXTR ,F.MON_FACT ,F.VAL_DLLS ,F.CVE_PROV ,
B.NUM_REFE,B.NUM_CONT,B.CVE_CONT,F.NUM_FACT ,F.FEC_FACT ,F.ICO_FACT ,F.VAL_EXTR ,F.MON_FACT ,F.VAL_DLLS ,F.CVE_PROV ,
I.NUM_IDE,I.CVE_IDEN,I.COM_IDEN,I.COM_IDEN2,I.COM_IDEN3 ,D.NUM_PART,
A.NUM_REFE,A.EDO_CUEN ,A.IMP_EXPO,A.PES_BRUT,A.VAL_NORM,A.OTRO_IMPU, A.DES_MERC,A.CTR_CUEN,A.CAN_FRAC,A.CAN_CONT,R.NUM_PART ,R.FRACCION ,R.CVE_VALO ,
R.CVE_VINC, LIST(V.NUM_PART) AS NUM_PART3
FROM SAAIO_PEDIME P LEFT OUTER JOIN CTRAC_CLIENT C ON (P.CVE_IMPO=C.CVE_IMP)
LEFT OUTER JOIN CTRAC_REPRE M ON (M.CVE_REPRE=P.CVE_REPR) AND (M.CVE_CLI=P.CVE_IMPO)
LEFT OUTER JOIN SAAIO_FACTUR F ON (F.NUM_REFE=P.NUM_REFE) AND (F.CONS_FACT=1)
LEFT OUTER JOIN SAAIO_FRACCI R ON (R.NUM_REFE=P.NUM_REFE) AND (R.NUM_PART=1)
LEFT OUTER JOIN SAAIO_CONTEN B ON (P.NUM_REFE=B.NUM_REFE)
LEFT OUTER JOIN SAAIO_TRANSP S ON (P.NUM_REFE=S.NUM_REFE)
LEFT OUTER JOIN SAAIO_FACPAR V ON (P.NUM_REFE=V.NUM_REFE)
LEFT OUTER JOIN SAAIO_GUIAS G ON (G.NUM_REFE=P.NUM_REFE) AND (G.IDE_MH=‘M’) AND (G.CONS_GUIA=1)
LEFT OUTER JOIN SAAIO_GUIAS H ON (H.NUM_REFE=P.NUM_REFE) AND (H.IDE_MH=‘H’) AND (H.CONS_GUIA=1)
LEFT OUTER JOIN SAAIO_IDEFRA D ON (D.NUM_REFE=R.NUM_REFE) AND (D.NUM_PART=R.NUM_PART) AND (D.NUM_IDE=1)
LEFT OUTER JOIN SAAIO_IDEPED I ON (I.NUM_REFE=P.NUM_REFE) AND (I.NUM_IDE=(SELECT MIN(NUM_IDE)
FROM SAAIO_IDEPED WHERE (NUM_REFE=I.NUM_REFE)))
LEFT OUTER JOIN CCGO_CUENTA A ON (A.NUM_REFE=P.NUM_REFE)
LEFT OUTER JOIN CCGO_FOLIOS L ON (L.NUM_REFE=A.NUM_REFE) AND (L.TIP_FACT=2)
LEFT OUTER JOIN CCGO_FACTUR Z ON (Z.NUM_REFE=A.NUM_REFE) AND (Z.CVE_CPTO=‘IVAG’) AND (Z.CVE_COMP=‘CTA’)
LEFT OUTER JOIN SAAIO_CONTPED A1 ON (A1.NUM_REFE=P.NUM_REFE) AND (A1.CVE_IMPU=1)
LEFT OUTER JOIN SAAIO_CONTPED A2 ON (A2.NUM_REFE=P.NUM_REFE) AND (A2.CVE_IMPU=15)
LEFT OUTER JOIN SAAIO_CONTPED A3 ON (A3.NUM_REFE=P.NUM_REFE) AND (A3.CVE_IMPU=7)
WHERE (P.NUM_REFE IS NOT NULL) AND (P.CVE_IMPO IN (‘MEI’,‘2365’)) AND (P.FIR_PAGO IS NOT NULL)
GROUP BY P.NUM_REFE , P.CVE_IMPO ,P.IMP_EXPO ,P.TIP_PEDI ,P.ADU_DESP ,P.PAT_AGEN ,P.NUM_PEDI ,P.ADU_ENTR ,P.FEC_ENTR ,P.TIP_CAMB ,P.CVE_PEDI ,
P.REG_ADUA ,P.FEC_ESPE ,P.DES_ORIG ,P.MAR_NUME ,P.PES_BRUT ,P.MTR_ENTR ,P.MTR_ARRI ,P.MTR_SALI ,P.MON_VASE ,P.TIP_MOVA ,P.VAL_DLLS ,
P.VAL_COME ,P.TOT_INCR ,P.VAL_NORM ,P.FAC_AJUS ,P.AUT_OBSE ,P.CVE_CAPT ,P.FEC_PAGO ,P.CVE_REPR ,P.TOT_EFEC ,P.TOT_OTRO ,P.FIR_ELEC ,
P.CVE_BANC ,P.NUM_CAJA ,P.DIA_PAGO ,P.HOR_PAGO , P.TOT_PAGO ,P.CAN_BULT ,P.FIR_REME ,P.SEC_DESP ,P.ADU_TRAN ,P.FIR_PAGO ,P.NUM_OPER ,
P.REG_ENTRADA ,P.CVE_CNTA , B.NUM_REFE,B.NUM_CONT,B.CVE_CONT,F.NUM_FACT ,F.FEC_FACT ,F.ICO_FACT ,F.VAL_EXTR ,F.MON_FACT ,F.VAL_DLLS ,F.CVE_PROV ,
I.NUM_IDE,I.CVE_IDEN,I.COM_IDEN,I.COM_IDEN2,I.COM_IDEN3 ,D.NUM_PART,
A.NUM_REFE,A.EDO_CUEN ,A.IMP_EXPO,A.PES_BRUT,A.VAL_NORM,A.OTRO_IMPU,
A.DES_MERC,A.CTR_CUEN,A.CAN_FRAC,A.CAN_CONT,R.NUM_PART ,R.FRACCION ,R.CVE_VALO ,
R.CVE_VINC
ORDER BY 1

#2

Re: Scriptcase SubQuerie

try making a database VIEW of your query. Base your grid on a select * from <view> instead of your query.

#3

Re: Scriptcase SubQuerie

Hi, good afternoon

I am having the same problem, I’m using Oracle and my code is correctly interpreted in both the interface and in the Oracle SQL builder, see:

select t.TRAM_IDDOCUMENTO, DINT_NUMERO, DINT_IDENTIFICACAO,
(SELECT UNID_UNIDADE.UNID_DESCRICAO FROM UNID_UNIDADE
INNER JOIN DINT_DOCUMENTOINTERNO ON UNID_UNIDADE.UNID_ID = DINT_DOCUMENTOINTERNO.DINT_IDUNIDADEORIGEM
WHERE DINT_DOCUMENTOINTERNO.DINT_ID = t.TRAM_IDDOCUMENTO AND ROWNUM=1) as unidadeorigem,(SELECT UNID_UNIDADE.UNID_DESCRICAO
FROM UNID_UNIDADE
INNER JOIN TRAM_TRAMITE ON UNID_UNIDADE.UNID_ID = TRAM_TRAMITE.TRAM_IDUNIDADE
WHERE TRAM_TRAMITE.TRAM_IDDOCUMENTO = t.TRAM_IDDOCUMENTO AND ROWNUM=1 AND TRAM_TRAMITE.TRAM_ID IN(SELECT MAX(TRAM_ID) FROM TRAM_TRAMITE)) as unidadeatual,
di.DINT_DATAABERTURA, t.TRAM_DATAACEITO, t.TRAM_DATASAIDA, di.DINT_IDREQUERENTE, di.DINT_SENHA
from TRAM_TRAMITE t
INNER JOIN DINT_DOCUMENTOINTERNO di ON t.TRAM_IDDOCUMENTO = di.DINT_ID
where t.TRAM_TRAMITADO = 1 and ( t.TRAM_IDUNIDADE = 10 )
order by
t.TRAM_DATAACEITO desc

In the Scriptcase platform i find the error:

Erro (256)
Mensagem: Erro ao recuperar os campos da tabela. ORA-00921: fim inesperado do comando SQLselect t.TRAM_IDDOCUMENTO, DINT_NUMERO, DINT_IDENTIFICACAO, (SELECT UNID_UNIDADE.UNID_DESCRICAO FROM UNID_UNIDADE INNER JOIN DINT_DOCUMENTOINTERNO ON UNID_UNIDADE.UNID_ID = DINT_DOCUMENTOINTERNO.DINT_IDUNIDADEORIGEM

Instead of the value of 10, I compare with a global variable in SQL Scriptcase!!! Help me, please!

#4

Re: Scriptcase SubQuerie

Hi if you dont want use views use the entire name of your table that`s the problem

select

customers.cu_id as Customer

from customers

do not use

select

c.cu_id as Customer

from customer c