DEFQUERYSET

QUERYSET29.1DesktopWeb

Permette di creare espressioni tra insiemi. In SQL queste operazioni sono: UNION, UNION ALL, EXCEPT e INTERSECT.
L'analoga operazione in BC mediante la definizione di una espressione che coinvolge i suddetti operatori e le query BC identificate dalla parola chiave QRY.

Parametri

Parametro	Definizione	I/O		Versione traduttore	SMP
IDQUERY	Long
ESPR

IDQUERY

LongInputValore non necessarioDesktopWeb

Identificatore della query ottenuto tramite @DEFQUERY.

ESPR

InputObbligatorioValore non necessarioDesktopWeb

Espressione delle operazioni tra insiemi.
E' possibile comporre una espressione libera usando parentesi tonde, gli operatori UNION, UNION ALL, EXCEPT e INTERSECT e query BC usando la parola chiave QRY.

Esempi

Definizione

 '@DEFQUERY IDQUERY[IdQry] NOME["BCRIF_DEFQUERYSET_25"]
 
    ' Query della prima tabella BLD_Articoli
    '@DEFQUERY IDQUERY[IdQry] NOME["BCRIF_DEFQUERYSET_3"]
    
       '@DEFQUERYFROM IDQUERY[IdQry] TABELLA[BLDART]
       
       '@DEFQUERYCOLUMN IDQUERY[IdQry] ESPR[BLDART.COD AS [CodArt]] _
                ESPR[BLDART.DES AS [DesArt]]
                            
    
    ' Query della seconda tabella BLD_Righe
    '@DEFQUERY IDQUERY[IdQry2] NOME["BCRIF_DEFQUERYSET_13"]
    
       '@DEFQUERYFROM IDQUERY[IdQry2] TABELLA[BLDRIGA]
       
       '@DEFQUERYJOIN IDQUERY[IdQry2] TABELLA[BLDART] TABELLAPADRE[BLDRIGA] _
              REL[BLDART.COD=BLDRIGA.CODART] _
              OPERATOREJOIN["INNER"]
                      
       '@DEFQUERYCOLUMN IDQUERY[IdQry2] ESPR[BLDRIGA.CODART  AS [CodArt]] _
                ESPR[BLDART.DES      AS [DesArt]]
    
 
    '@DEFQUERYSET IDQUERY[IdQry] ESPR[QRY(IdQry) UNION QRY(IdQry2)] ' Aggiungere eventuali UNION per unioni tra piu' query
               
            
'   ' Query della prima tabella BLD_Articoli
'   '@DEFQUERY QUERY[QueryObject1] NOME["BCRIF_DEFQUERYSET_3"]
'   
'      '@FROM TABELLA[BLDART]
'      
'      '@COLUMN ESPR[BLDART.COD AS [CodArt]] _
'               ESPR[BLDART.DES AS [DesArt]]
'                           
'   '@ENDQUERY 
'   
'   ' Query della seconda tabella BLD_Righe
'   '@DEFQUERY QUERY[QueryObject2] NOME["BCRIF_DEFQUERYSET_13"]
'   
'      '@FROM TABELLA[BLDRIGA]
'      
'      '@JOIN TABELLA[BLDART] TABELLAPADRE[BLDRIGA] _
'             REL[BLDART.COD=BLDRIGA.CODART] _
'             OPERATOREJOIN["INNER"]
'                     
'      '@COLUMN ESPR[BLDRIGA.CODART  AS [CodArt]] _
'               ESPR[BLDART.DES      AS [DesArt]]
'   
'   '@ENDQUERY
'   
'   '@DEFQUERY QUERY[queryDefQuerySetId] NOME["BCRIF_DEFQUERYSET_25"]
'   
'      '@DEFQUERYSET ESPR[QRY(QueryObject1) UNION QRY(QueryObject2)]
'                 
'   '@ENDQUERY 
 
'   Statement SQL generato           
'                                                          
'   SELECT BLDArticoli_1.CodArt AS [CodArt] 
'      ,BLDArticoli_1.DesArt AS [DesArt] 
'   FROM BLDArticoli AS [BLDArticoli_1] 
'   WHERE (BLDArticoli_1.DBGruppo = 'GG') 
'    
'   UNION 
'    
'   SELECT BLDRighe.CodArt AS [CodArt] 
'      ,BLDArticoli_1.DesArt AS [DesArt] 
'   FROM BLDRighe AS [BLDRighe] 
'   INNER JOIN BLDArticoli AS [BLDArticoli_1] ON ( 
'         BLDArticoli_1.CodArt = BLDRighe.CodArt 
'         AND (BLDArticoli_1.DBGruppo = BLDRighe.DBGruppo) 
'         ) 
'   WHERE (BLDRighe.DBGruppo = 'GG') 
'   ORDER BY [CodArt] ASC                                  
'                                                          
'   Risultato della query                                  
'                                                          
'   CodArt    DesArt                                       
'   --------- ---------------                              
'   ...                                                    
'   002       Articolo 002                                 
'   003       Articolo 003                                 
'   004       Articolo 004                                 
'   005       Articolo 005                                 
'   006       Articolo 006                                 
'   007       Articolo 007                                 
'   008       Articolo 008                                 
'   009       Articolo 009                                 
'   010       Articolo 010                                 
'   011       Articolo 011                                 
'   ...

Definizione con Except nel parametro ESPR

 '@DEFQUERY IDQUERY[IdQry] NOME["BCRIF_DEFQUERYSET_41"]
 
    '@DEFQUERY IDQUERY[IdQry2] NOME["BCRIF_DEFQUERYSET_2"]
    
       '@DEFQUERYFROM IDQUERY[IdQry2] TABELLA[BLDART]
       
       '@DEFQUERYCOLUMN IDQUERY[IdQry2] ESPR[BLDART.COD AS [CodArt]] _
                ESPR[BLDART.DES AS [DesArt]]
                        
       '@DEFQUERYWHERE IDQUERY[IdQry2] ESPR[INLIST(BLDART.COD,"001,003")>0]
                    
                      
    '@DEFQUERY IDQUERY[IdQry3] NOME["BCRIF_DEFQUERYSET_14"]
    
       '@DEFQUERYFROM IDQUERY[IdQry3] TABELLA[BLDRIGA]
       
       '@DEFQUERYJOIN IDQUERY[IdQry3] TABELLA[BLDART] TABELLAPADRE[BLDRIGA] _
              REL[BLDART.COD=BLDRIGA.CODART] _ 
              OPERATOREJOIN["INNER"]
                           
       '@DEFQUERYCOLUMN IDQUERY[IdQry3] ESPR[BLDRIGA.CODART  AS [CodArt]] _
                ESPR[BLDART.DES      AS [DesArt]]
                               
       '@DEFQUERYWHERE IDQUERY[IdQry3] ESPR[ INLIST(BLDRIGA.CODART,"002,004")>0]
                      
                    
    '@DEFQUERY IDQUERY[IdQry] NOME["BCRIF_DEFQUERYSET_29"]        
       
       '@DEFQUERYFROM IDQUERY[IdQry] TABELLA[BLDART]
       
       '@DEFQUERYCOLUMN IDQUERY[IdQry] ESPR[BLDART.COD AS [CodArt]] _
                ESPR[BLDART.DES AS [DesArt]]
                               
       '@DEFQUERYWHERE IDQUERY[IdQry] ESPR[INLIST(BLDART.COD,"001,004")>0]
                            
    
    '@DEFQUERYSET IDQUERY[IdQry] ESPR[QRY(IdQry) UNION QRY(IdQry2) UNION QRY(IdQry3)] ' Aggiungere eventuali UNION per unioni tra piu' query
                         
    '@DEFQUERYORDERBY IDQUERY[IdQry] ESPR[[CodArt]]
    
                
'   Statement SQL generato           
'                                                          
'   SELECT BLDArticoli_1.CodArt AS [CodArt]
'      ,BLDArticoli_1.DesArt AS [DesArt]
'   FROM BLDArticoli AS [BLDArticoli_1]
'   WHERE (
'         (
'            BLDArticoli_1.CodArt IN (
'               '001'
'               ,'003'
'               )
'            )
'         )
'      AND (BLDArticoli_1.DBGruppo = 'GG')
'    
'   UNION
'   
'   (
'      SELECT BLDRighe.CodArt AS [CodArt]
'         ,BLDArticoli_1.DesArt AS [DesArt]
'      FROM BLDRighe AS [BLDRighe]
'      INNER JOIN BLDArticoli AS [BLDArticoli_1] ON (
'            BLDArticoli_1.CodArt = BLDRighe.CodArt
'            AND (BLDArticoli_1.DBGruppo = BLDRighe.DBGruppo)
'            )
'      WHERE (
'            (
'               BLDRighe.CodArt IN (
'                  '002'
'                  ,'004'
'                  )
'               )
'            )
'         AND (BLDRighe.DBGruppo = 'GG')
'      
'      EXCEPT
'      
'      SELECT BLDArticoli_1.CodArt AS [CodArt]
'         ,BLDArticoli_1.DesArt AS [DesArt]
'      FROM BLDArticoli AS [BLDArticoli_1]
'      WHERE (
'            (
'               BLDArticoli_1.CodArt IN (
'                  '001'
'                  ,'004'
'                  )
'               )
'            )
'         AND (BLDArticoli_1.DBGruppo = 'GG')
'      )
'   ORDER BY [CodArt] ASC 
'
'   Risultato della query                                  
'                                                          
'   CodArt    DesArt                                       
'   --------- ---------------                                                      
'   001       Articolo 001                                 
'   002       Articolo 002                                 
'   003       Articolo 003