Tdolphin Subselects

[nnicanor]

Cuando hago una consulta con subselects en tdolphin esta funciona bien, pero cuando quiero cambiar la condicion con SetWhere, no cambia la condicion del query principal sin o la del subquery lo cual me produce un error.

Code: Select all  Expand view

SELECT cl.nomcla AS clase,c.codbuq as buque,c.codbuq,c.certif, c.certif AS codigo,
       m.nomcer AS certificado, c.expedi AS expide, c.vencim  AS vence ,c.vencim,
             c.id ,m.tven,m.aplicav,diasven,m.tiempoven,
             ( select fechaven from ventanas v where c.codbuq=v.codbuq and c.certif=v.codcer and ejecutada = 0 order by v.fechaven limit 1 ) as fventana
FROM certifi c
INNER JOIN macerti m ON m.codcer  = c.certif
INNER JOIN clascert cl ON cl.codcla = m.grupo
WHERE [b]c.codbuq ='000016' and c.tipo='B'[/b] ORDER BY c.certif,id



Application
===========
   Path and name: C:\ProyectosFW\Buques\bin\Sistema_Buques.exe (32 bits)
   Size: 4,346,368 bytes
   Time from start: 0 hours 0 mins 18 secs
   Error occurred at: 2011.11.17, 23:14:15
   Error description: Error MYSQL/1054  Unknown column 'c.codbuq' in 'where clause'

Stack Calls
===========
   Called from: .\source\prg\tdolpsrv.prg => DOLPHIN_DEFERROR(2592)
   Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:CHECKERROR(700)
   Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:SQLQUERY(1814)
   Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:LOADQUERY(1101)
   Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SETNEWFILTER(1533)
   Called from: .\source\prg\tdolpqry.prg => (b)TDOLPHINQRY(232)
   Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:SETWHERE(0)
   Called from: flota.prg => TFLOTA:CARGAR(216)
   Called from: flota.prg => (b)TFLOTA_EDITAR(546)
   Called from: flota.prg => (b)TFLOTA_EDITAR(556)
   Called from: Recursosn.prg => (b)_BARRACOM_MENU(1781)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK(466)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP(663)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONDOWN(603)
   Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT(1517)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT(1458)
   Called from: .\source\classes\WINDOW.PRG => _FWH(3418)
   Called from:  => DIALOGBOX(0)
   Called from: .\source\classes\DIALOG.PRG => TDIALOG:ACTIVATE(275)
   Called from: flota.prg => TFLOTA:EDITAR(591)
   Called from: buques.prg => (b)MAIN(57)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:CLICK(466)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONUP(663)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:LBUTTONDOWN(603)
   Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT(1517)
   Called from: .\source\classes\BTNBMP.PRG => TBTNBMP:HANDLEEVENT(1458)
   Called from: .\source\classes\WINDOW.PRG => _FWH(3418)
   Called from:  => WINRUN(0)
   Called from: .\source\classes\WINDOW.PRG => TMDIFRAME:ACTIVATE(993)
   Called from: buques.prg => MAIN(90)

System
======
   CPU type: Intel(R) Core(TM) i5 CPU       M 460  @ 2.53GHz 2533 Mhz
   Hardware memory: -200 megs

   Free System resources: 90 %
        GDI    resources: 90 %
        User   resources: 90 %

   Compiler version: Harbour 3.1.0dev (Rev. 17025)
   Windows version: 6.1, Build 7601 Service Pack 1

   
Variables in use
================
   Procedure     Type   Value
   ==========================
   DOLPHIN_DEFERROR
     Param   1:    O    Class: ERROR
   TDOLPHINSRV:CHECKERROR
     Param   1:    O    Class: TDOLPHINSRV
     Param   2:    N    1054
     Param   3:    L    .F.
     Param   4:    C    ""
     Local   1:    C    ""
     Local   2:    O    Class: ERROR
   TDOLPHINSRV:SQLQUERY
     Local   1:    N    1054
     Local   2:    U    
     Local   3:    L    .F.
   TDOLPHINQRY:LOADQUERY
     Param   1:    C    "SELECT fechaven FROM ventanas v WHERE  c.codbuq ='000016' and c.tipo='B'  ORDER BY v.fechaven"
     Local   1:    N    93
     Local   2:    N    1
   TDOLPHINQRY:SETNEWFILTER
     Param   1:    L    .F.
     Local   1:    O    Class: TDOLPHINSRV
     Local   2:    C    "SELECT fechaven FROM ventanas v WHERE  c.codbuq ='000016' and c.tipo='B'  ORDER BY v.fechaven"
     Local   3:    U    
     Local   4:    U    
     Local   5:    L    .F.
   (b)TDOLPHINQRY
     Param   1:    N    1
     Param   2:    C    " c.codbuq ='000016' and c.tipo='B' "
     Param   3:    L    .T.
     Local   1:    C    "c.codbuq = v.codbuq and c.certif = v.codcer and ejecutada = 0"
     Local   2:    L    .T.
   TDOLPHINQRY:SETWHERE
     Param   1:    O    Class: TDOLPHINQRY
     Param   2:    C    " c.codbuq ='000016' and c.tipo='B' "
     Local   1:    U    
   TFLOTA:CARGAR
     Param   1:    C    " c.codbuq ='000016' and c.tipo='B' "
   (b)TFLOTA_EDITAR
   (b)TFLOTA_EDITAR
   (b)_BARRACOM_MENU
   TBTNBMP:CLICK
     Param   1:    O    Class: TBTNBMP
   TBTNBMP:LBUTTONUP
   TBTNBMP:LBUTTONDOWN
     Param   1:    N    8
     Param   2:    N    25
     Local   1:    U    
     Local   2:    L    .T.
   TCONTROL:HANDLEEVENT
     Param   1:    N    8
     Param   2:    N    25
     Param   3:    N    1
   TBTNBMP:HANDLEEVENT
     Param   1:    N    513
     Param   2:    N    1
     Param   3:    N    524313
     Local   1:    U    
   _FWH
     Param   1:    N    513
     Param   2:    N    1
     Param   3:    N    524313
   DIALOGBOX
     Param   1:    N    524313
     Param   2:    N    513
     Param   3:    N    1
     Param   4:    N    524313
     Param   5:    N    114
     Local   1:    O    Class: TBTNBMP
   TDIALOG:ACTIVATE
     Param   1:    N    4194304
     Param   2:    C    "FOLDBUQ2"
     Param   3:    N    2885662
     Param   4:    O    Class: TDIALOG
   TFLOTA:EDITAR
     Param   1:    U    
     Param   2:    U    
     Param   3:    U    
     Param   4:    L    .T.
     Param   5:    U    
     Param   6:    L    .T.
     Param   7:    B    {|| ... }
     Param   8:    U    
     Param   9:    U    
     Param  10:    L    .F.
     Param  11:    L    .F.
     Local   1:    N    2885662
     Local   2:    S    
     Local   3:    O    Class: TDIALOG
   (b)MAIN
     Local   1:    L    .F.
     Local   2:    U    
     Local   3:    O    Class: TFLOTA
     Local   4:    O    Class: TDIALOG
     Local   5:    O    Class: TXBROWSE
     Local   6:    A    Len:   70
     Local   7:    O    Class: TMENU
     Local   8:    O    Class: TFLOTA
     Local   9:    B    {|| ... }
     Local  10:    B    {|| ... }
     Local  11:    B    {|| ... }
     Local  12:    B    {|| ... }
     Local  13:    B    {|| ... }
     Local  14:    B    {|| ... }
     Local  15:    B    {|| ... }
     Local  16:    B    {|| ... }
     Local  17:    B    {|| ... }
     Local  18:    B    {|| ... }
     Local  19:    O    Class: TBAR
     Local  20:    A    Len:    3
     Local  21:    A    Len:   10
   TBTNBMP:CLICK
     Param   1:    O    Class: TBTNBMP
   TBTNBMP:LBUTTONUP
   TBTNBMP:LBUTTONDOWN
     Param   1:    N    39
     Param   2:    N    26
     Local   1:    U    
     Local   2:    L    .T.
   TCONTROL:HANDLEEVENT
     Param   1:    N    39
     Param   2:    N    26
     Param   3:    N    1
   TBTNBMP:HANDLEEVENT
     Param   1:    N    513
     Param   2:    N    1
     Param   3:    N    2555930
     Local   1:    U    
   _FWH
     Param   1:    N    513
     Param   2:    N    1
     Param   3:    N    2555930
   WINRUN
     Param   1:    N    2555930
     Param   2:    N    513
     Param   3:    N    1
     Param   4:    N    2555930
     Param   5:    N    4
     Local   1:    O    Class: TBTNBMP
   TMDIFRAME:ACTIVATE
     Param   1:    N    2885662
   MAIN
     Param   1:    C    "MAXIMIZED"
     Param   2:    U    
     Param   3:    U    
     Param   4:    U    
     Param   5:    U    
     Param   6:    B    {|| ... }
     Param   7:    U    
     Param   8:    B    {|| ... }
     Param   9:    U    
     Param  10:    U    
     Param  11:    U    
     Param  12:    U    
     Param  13:    U    
     Param  14:    U    
     Param  15:    U    
     Param  16:    U    
     Param  17:    B    {|| ... }
     Param  18:    U    
     Param  19:    U    
     Local   1:    O    Class: TMDIFRAME
     Local   2:    U    
     Local   3:    U    

Linked RDDs
===========
   DBF
   DBFFPT
   DBFBLOB
   DBFNTX

DataBases in use
================

Classes in use:
===============
     1 ERROR
     2 HBCLASS
     3 HBOBJECT
     4 TBRUSH
     5 TWINDOW
     6 TMDIFRAME
     7 TMENU
     8 TMENUITEM
     9 TCONTROL
    10 TICON
    11 TMDICLIENT
    12 TFONT
    13 TBAR
    14 TRECT
    15 TBTNBMP
    16 TMSGBAR
    17 TMSGITEM
    18 TTIMER
    19 TBITMAP
    20 TDIALOG
    21 TDOLPHINSRV
    22 TDOLPHINQRY
    23 TGET
    24 GET
    25 TCLIPGET
    26 TBUTTON
    27 TDOLPHINROW
    28 TTRAYICON
    29 TSTRUCT
    30 TFLOTA
    31 TFOLDEREX
    32 TCOMBOBOX
    33 TMULTIGET
    34 TIMAGE
    35 TXBROWSE
    36 TXBRWCOLUMN
    37 TBUTTONBMP
    38 TSCROLLBAR
    39 TCURSOR
    40 TWBROWSE
    41 TCBROWSE
    42 TCCOLUMN
    43 TREG32

Memory Analysis
===============
      375 Static variables

   Dynamic memory consume:
      Actual  Value:          0 bytes
      Highest Value:          0 bytes


 

Slds

[Daniel Garcia-Gil]

Hola

Exacto nicanor, asi trabaja dolphin, como una vez dije, la clase como tal sirve para principiantes y expertos, ella misma te exigira a aprender un poco mas de mysql,
Dolphin no cuenta con un reconocedor de sintaxis avanzado, los methodos Set... se usan para sentencias no complejas, como lo comente una vez en el blog.

http://tdolphin.blogspot.com/2010/07/modificar-valor-de-sentencias-modify.html

Para query "simples" Dolphin separa individualmente el valos de las sentencias y poder asignarlas de manera individual (WHERE,  GROUP, HAVING, LIMIT, ORDER)
Puede dejar que dolphin construya  las sentencias "simples" por ud

podrias crear una plantilla de la consulta y cambiar los valores que te interecen...
ejemplo

Code: Select all  Expand view

cQryTmp = " SELECT * FROM [TABLE]"
cQry = StrTran( cQryTmp, "[TABLE]", "NombreTabla" )

[nnicanor]

Listo Daniel,

Muchas gracias lo tendre en cuenta, ya lo solucione con una funcion enviandole los parametros.


Slds

[CARLOS ATUNCAR]

Buenas tardes, Daniel una consulta y a este problema como le doy solución?, Gracias

Time from start: 0 hours 0 mins 17 secs
Error occurred at: 08/03/2016, 14:57:47
Error description: Error MYSQL/1054 Unknown column 'mes' in 'where clause'

Stack Calls
===========
Called from: .\source\prg\tdolpsrv.prg => DOLPHIN_DEFERROR( 2901 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:CHECKERROR( 838 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:SQLQUERY( 2099 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:LOADQUERY( 1230 )
Called from: .\source\prg\tdolpqry.prg => TDOLPHINQRY:NEW( 310 )
Called from: .\source\prg\tdolpsrv.prg => TDOLPHINSRV:QUERY( 1867 )
Called from: Bin\mando.prg => MANDOA( 250 )
Called from: Bin\mando.prg => MANDO( 31 )
Called from: Bin\Menu.prg => (b)BUILDRIBBONBAR( 345 )
Called from: .\source\classes\TRBTN.PRG => TRBTN:CLICK( 715 )
Called from: .\source\classes\TRBTN.PRG => TRBTN:LBUTTONUP( 915 )
Called from: .\source\classes\CONTROL.PRG => TCONTROL:HANDLEEVENT( 1723 )
Called from: .\source\classes\TRBTN.PRG => TRBTN:HANDLEEVENT( 1573 )
Called from: .\source\classes\WINDOW.PRG => _FWH( 3544 )
Called from: => WINRUN( 0 )
Called from: .\source\classes\WINDOW.PRG => TWINDOW:ACTIVATE( 1062 )
Called from: Bin\Menu.prg => MAIN( 152 )

System
======
CPU type: Intel(R) Core(TM) i3-2350M CPU @ 2.30GHz 2300 Mhz
Hardware memory: 2723 megs

Free System resources: 90 %
GDI resources: 90 %
User resources: 90 %

Windows total applications running: 7
1 ,
2 , C:\Windows\WinSxS\x86_microsoft.windows.common-controls_6595b64144ccf1df_6.0.7601.18837_none_41e855
3 , C:\Windows\syswow64\SHLWAPI.dll
4 Conmutación de tareas, G:\MySql\nStp2015\Integrador\demo\Ca-nStp.Exe
5 DDE Server Window, C:\Windows\syswow64\OLE32.DLL
6 GDI+ Window, C:\Windows\WinSxS\x86_microsoft.windows.gdiplus_6595b64144ccf1df_1.1.7601.19061_none_72d6d48d866497
7 HideForm, G:\MySql\nStp2015\Integrador\demo\FrSystH.dll

Variables in use
================
Procedure Type Value
==========================
DOLPHIN_DEFERROR
Param 1: O Class: TDOLPHINSRV
Param 2: N 1054
Param 3: L .F.
Param 4: C ""
Local 1: C ""
Local 2: O Class: ERROR
TDOLPHINSRV:CHECKERROR
Local 1: N 1054
Local 2: U
Local 3: O Class: TDOLPHINSRV
Local 4: L .F.
TDOLPHINSRV:SQLQUERY
Param 1: C "SELECT MONTH(m.fecha_add) AS mes,YEAR(m.fecha_add) AS ano,count(m.fecha_add) AS captados,(SELECT COUNT(status) FROM maestro WHERE status='Elegible' AND empresa = '01' AND MONTH(fecha_add) = mes AND id_md = 'CSP') AS elegible ,COUNT(m.fecha_docc) AS contrato,COUNT(d.plano_reci) AS planos,COUNT(d.muni_prese) AS licencia,COUNT(m.fecha_envi) AS registro,COUNT(m.id_ct) AS codigo,COUNT(m.fecha_ahor) AS ahorro FROM maestro m INNER JOIN documentos d ON m.id_nu=d.id_nu AND m.empresa = '01' and d.empresa = '01' AND m.id_md = 'CSP' LEFT JOIN constructor c ON m.id_nu=c.codigo AND m.empresa = '01' AND c.empresa = '01' AND m.id_md = 'CSP' GROUP BY ano,mes ORDER BY ano,mes"
Local 1: U
Local 2: O Class: TDOLPHINSRV
Local 3: N 669
Local 4: N 1
TDOLPHINQRY:LOADQUERY
Local 1: L .T.
Local 2: O Class: TDOLPHINQRY
Local 3: O Class: TDOLPHINSRV
Local 4: C "SELECT MONTH(m.fecha_add) AS mes,YEAR(m.fecha_add) AS ano,count(m.fecha_add) AS captados,(SELECT COUNT(status) FROM maestro WHERE status='Elegible' AND empresa = '01' AND MONTH(fecha_add) = mes AND id_md = 'CSP') AS elegible ,COUNT(m.fecha_docc) AS contrato,COUNT(d.plano_reci) AS planos,COUNT(d.muni_prese) AS licencia,COUNT(m.fecha_envi) AS registro,COUNT(m.id_ct) AS codigo,COUNT(m.fecha_ahor) AS ahorro FROM maestro m INNER JOIN documentos d ON m.id_nu=d.id_nu AND m.empresa = '01' and d.empresa = '01' AND m.id_md = 'CSP' LEFT JOIN constructor c ON m.id_nu=c.codigo AND m.empresa = '01' AND c.empresa = '01' AND m.id_md = 'CSP' GROUP BY ano,mes ORDER BY ano,mes"
Local 5: U
Local 6: U
Local 7: U
Local 8: L .T.
TDOLPHINQRY:NEW
Param 1: C "SELECT MONTH(m.fecha_add) AS mes,YEAR(m.fecha_add) AS ano,count(m.fecha_add) AS captados,(SELECT COUNT(status) FROM maestro WHERE status='Elegible' AND empresa = '01' AND MONTH(fecha_add) = mes AND id_md = 'CSP') AS elegible ,COUNT(m.fecha_docc) AS contrato,COUNT(d.plano_reci) AS planos,COUNT(d.muni_prese) AS licencia,COUNT(m.fecha_envi) AS registro,COUNT(m.id_ct) AS codigo,COUNT(m.fecha_ahor) AS ahorro FROM maestro m INNER JOIN documentos d ON m.id_nu=d.id_nu AND m.empresa = '01' and d.empresa = '01' AND m.id_md = 'CSP' LEFT JOIN constructor c ON m.id_nu=c.codigo AND m.empresa = '01' AND c.empresa = '01' AND m.id_md = 'CSP' GROUP BY ano,mes ORDER BY ano,mes"
Param 2: O Class: TDOLPHINSRV
Param 3: U
Local 1: O Class: TDOLPHINQRY
Local 2: U
Local 3: U
Local 4: U
TDOLPHINSRV:QUERY
Param 1: C "SELECT MONTH(m.fecha_add) AS mes,YEAR(m.fecha_add) AS ano,count(m.fecha_add) AS captados,(SELECT COUNT(status) FROM maestro WHERE status='Elegible' AND empresa = '01' AND MONTH(fecha_add) = mes AND id_md = 'CSP') AS elegible ,COUNT(m.fecha_docc) AS contrato,COUNT(d.plano_reci) AS planos,COUNT(d.muni_prese) AS licencia,COUNT(m.fecha_envi) AS registro,COUNT(m.id_ct) AS codigo,COUNT(m.fecha_ahor) AS ahorro FROM maestro m INNER JOIN documentos d ON m.id_nu=d.id_nu AND m.empresa = '01' and d.empresa = '01' AND m.id_md = 'CSP' LEFT JOIN constructor c ON m.id_nu=c.codigo AND m.empresa = '01' AND c.empresa = '01' AND m.id_md = 'CSP' GROUP BY ano,mes ORDER BY ano,mes"
Local 1: U
Local 2: O Class: TDOLPHINSRV
Local 3: U
MANDOA
Param 1: A Len: 5
Local 1: U
Local 2: U
Local 3: U
Local 4: U
Local 5: C "SELECT MONTH(m.fecha_add) AS mes,YEAR(m.fecha_add) AS ano,count(m.fecha_add) AS captados,(SELECT COUNT(status) FROM maestro WHERE status='Elegible' AND empresa = '01' AND MONTH(fecha_add) = mes AND id_md = 'CSP') AS elegible ,COUNT(m.fecha_docc) AS contrato,COUNT(d.plano_reci) AS planos,COUNT(d.muni_prese) AS licencia,COUNT(m.fecha_envi) AS registro,COUNT(m.id_ct) AS codigo,COUNT(m.fecha_ahor) AS ahorro FROM maestro m INNER JOIN documentos d ON m.id_nu=d.id_nu AND m.empresa = '01' and d.empresa = '01' AND m.id_md = 'CSP' LEFT JOIN constructor c ON m.id_nu=c.codigo AND m.empresa = '01' AND c.empresa = '01' AND m.id_md = 'CSP' GROUP BY ano,mes ORDER BY ano,mes"
Local 6: O Class: TDIALOG
Local 7: N 4
Local 8: C "G:\MYSQL\NSTP2015\INTEGRADOR\DEMO\Temp\cmandoa.xlsx"
Local 9: A Len: 7
Local 10: N 1
Local 11: C "TITULOS/MESES"
Local 12: C "C:\Users\Help\Downloads\meses.xlsx"
MANDO
Local 1: O Class: TDIALOG