Unir 2 tablas OITM y OITG

[former_member203638]

Hola a todos, quiero unir 2 tablas la OITM y la OITG por que estoy a la mitad de un reportito y quiero filtrar algunos articulos mediante su propiedad, pero en el generador de consultas no me lo une solo sale OITM T0, OITG T1

Me falto, cuando se "tilda" la propiedad cambia el status ya sea "N" o "Y" como seleccionar los que estan marcados?

Algun tip?

Gracias.

Alessandro.

Edited by: Alessandro Santinelli on Dec 1, 2009 7:38 PM

Edited by: Alessandro Santinelli on Dec 1, 2009 7:42 PM

[former_member188440 · Accepted solution]

lo que puedes hacer es usar solo la oitm, pero apuntando a que el qrygroup este en 'Y' o 'N'

el campo qrygroup(seguido de un numero) es el numero de propiedad en la pestaña del mismo nombre en el dato maestro de articulo.

Lo otro que se me ocurre es que para fines del reporte, es mas facil, crear un campo de usuario el cual contenga el nombre de la propiedad que tiene activa dicho articulo, asi no necesitarias hacer un join a la oitg.

Yo tomaria los datos recopilados al levantar la info del cliente (si fue el caso) y en base a los nombres para los que te basaste al crear las propiedades, con un Data Transfer, actualizar el campo de usuario con el nombre y listo.

Te ahorras lineas de codigo.

Creo que es lo mas practico.

Edited by: Mauricio QUINTANA on Dec 1, 2009 8:07 PM

[Former Member]

Hola,
Un poco tarde pero tal vez a alguien le ayude.
Saludos

select pprop.ItemCode,pprop.ItemName,pprop.ItmsGrpNam

from

  (select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup1 = 'Y' and OITG.ItmsTypCod = 1

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup2 = 'Y' and OITG.ItmsTypCod = 2

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup3 = 'Y' and OITG.ItmsTypCod = 3

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup4 = 'Y' and OITG.ItmsTypCod = 4

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup5 = 'Y' and OITG.ItmsTypCod = 5

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup6 = 'Y' and OITG.ItmsTypCod = 6

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup7 = 'Y' and OITG.ItmsTypCod = 7

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup8 = 'Y' and OITG.ItmsTypCod = 8

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup9 = 'Y' and OITG.ItmsTypCod = 9

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup10 = 'Y' and OITG.ItmsTypCod = 10

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup11 = 'Y' and OITG.ItmsTypCod = 11

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup12 = 'Y' and OITG.ItmsTypCod = 12

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup13 = 'Y' and OITG.ItmsTypCod = 13

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup14 = 'Y' and OITG.ItmsTypCod = 14

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup15 = 'Y' and OITG.ItmsTypCod = 15

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup16 = 'Y' and OITG.ItmsTypCod = 16

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup17 = 'Y' and OITG.ItmsTypCod = 17

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup18 = 'Y' and OITG.ItmsTypCod = 18

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup19 = 'Y' and OITG.ItmsTypCod = 19

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup20 = 'Y' and OITG.ItmsTypCod = 20

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup21 = 'Y' and OITG.ItmsTypCod = 21

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup22 = 'Y' and OITG.ItmsTypCod = 22

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup23 = 'Y' and OITG.ItmsTypCod = 23

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup24 = 'Y' and OITG.ItmsTypCod = 24

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup25 = 'Y' and OITG.ItmsTypCod = 25

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup26 = 'Y' and OITG.ItmsTypCod = 26

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup27 = 'Y' and OITG.ItmsTypCod = 27

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup28 = 'Y' and OITG.ItmsTypCod = 28

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup29 = 'Y' and OITG.ItmsTypCod = 29

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup30 = 'Y' and OITG.ItmsTypCod = 30

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup31 = 'Y' and OITG.ItmsTypCod = 31

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup32 = 'Y' and OITG.ItmsTypCod = 32

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup33 = 'Y' and OITG.ItmsTypCod = 33

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup34 = 'Y' and OITG.ItmsTypCod = 34

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup35 = 'Y' and OITG.ItmsTypCod = 35

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup36 = 'Y' and OITG.ItmsTypCod = 36

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup37 = 'Y' and OITG.ItmsTypCod = 37

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup38 = 'Y' and OITG.ItmsTypCod = 38

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup39 = 'Y' and OITG.ItmsTypCod = 39

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup40 = 'Y' and OITG.ItmsTypCod = 40

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup41 = 'Y' and OITG.ItmsTypCod = 41

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup42 = 'Y' and OITG.ItmsTypCod = 42

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup43 = 'Y' and OITG.ItmsTypCod = 43

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup44 = 'Y' and OITG.ItmsTypCod = 44

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup45 = 'Y' and OITG.ItmsTypCod = 45

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup46 = 'Y' and OITG.ItmsTypCod = 46

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup47 = 'Y' and OITG.ItmsTypCod = 47

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup48 = 'Y' and OITG.ItmsTypCod = 48

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup49 = 'Y' and OITG.ItmsTypCod = 49

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup50 = 'Y' and OITG.ItmsTypCod = 50

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup51 = 'Y' and OITG.ItmsTypCod = 51

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup52 = 'Y' and OITG.ItmsTypCod = 52

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup53 = 'Y' and OITG.ItmsTypCod = 53

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup54 = 'Y' and OITG.ItmsTypCod = 54

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup55 = 'Y' and OITG.ItmsTypCod = 55

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup56 = 'Y' and OITG.ItmsTypCod = 56

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup57 = 'Y' and OITG.ItmsTypCod = 57

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup58 = 'Y' and OITG.ItmsTypCod = 58

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup59 = 'Y' and OITG.ItmsTypCod = 59

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup60 = 'Y' and OITG.ItmsTypCod = 60

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup61 = 'Y' and OITG.ItmsTypCod = 61

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup62 = 'Y' and OITG.ItmsTypCod = 62

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup63 = 'Y' and OITG.ItmsTypCod = 63

union all

  select OITM.ItemCode,OITM.ItemName,OITG.ItmsGrpNam from OITM CROSS JOIN OITG where OITM.QryGroup64 = 'Y' and OITG.ItmsTypCod = 64)

pprop order by pprop.ItemCode

[Former Member]

Hola....

No es posible cruzar ambas tablas.....

Lo que podrías hacer sería generar un query en el cual se defina como variable de entrada la o la(s) propiedades que deseas utilizar como filtro.

Ahora, si deseas que aparezcan todas, tendrás que generar un query tal como:

SELECT *  FROM [dbo].[OITM]  T0 WHERE T0.[QryGroup1] = [%0] or  T0.[QryGroup2] = [%1] or  T0.[QryGroup3] = [%2] or
 T0.[QryGroup4] = [%3] or  T0.[QryGroup5] = [%4] or  T0.[QryGroup6] = [%5] or  T0.[QryGroup7] = [%6] or  
T0.[QryGroup8] = [%7] or  T0.[QryGroup9] = [%8] or  T0.[QryGroup10] = [%9] or  T0.[QryGroup11] = [%10] or
T0.[QryGroup12] = [%11] or  T0.[QryGroup13] = [%12] or  T0.[QryGroup14] = [%13] or T0.[QryGroup15] = [%14] or 
T0.[QryGroup16] = [%15] or  T0.[QryGroup17] = [%16] or  T0.[QryGroup18] = [%17] or  T0.[QryGroup19] = [%18] or
T0.[QryGroup20] = [%19] or  ..................................................
............................... or T0.[QryGroup64] = [%63]

Sin embargo, esto no te indicaría el nombre de cada propiedad...

El hecho de que uses el conector "or" permitirá que con solo indicar uno de los datos de entrada, te traiga resultados. No es necesario indicar en cada variable un valor.

Saludos Cordiales,

[former_member203638]

Segui el consejo de Mauricio y ya va tomando forma el reportito.

SELECT Distinct T0.DocNum, T0.DocDate Fecha, T0.NumAtCard Factura, T1.Dscription Descripcion, SUM(T1.Quantity), T1.Price PL,T0.DiscPrcnt 'Desc' 
FROM OPCH T0  INNER JOIN PCH1 T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode WHERE T0.DocDate between [%1] and [%2] and 
( T2.QryGroup13 ='Y' or  T2.QryGroup14 ='Y' or  T2.QryGroup15 ='Y') and  T0.CardCode=[%3] and T1.DocEntry IN (Select Distinct DocEntry FROM PCH1 WHERE TargetType != 19) 
group by T0.DocDate,T0.DocNum,T0.NumAtCard, T1.Dscription,T1.Quantity, T1.Price,T0.DiscPrcnt

No me esta arrojando ni parecido con lo que trae nativo sap.No se si mi error este en la validación de las facturas de proveedor canceladas.

Lo valido asi:

and T1.DocEntry IN (Select Distinct DocEntry FROM PCH1 WHERE TargetType != 19)

Algun tip?

Gracias

Alessandro.

[former_member188440]

Creo que no necesitas el Select que pones al final, si de cualquier forma es la pch1 pues solo pon un And trgetentry not in (19)

algo asi

[former_member203638]

No me quedo claro.

Gracias y Saludos.

[former_member210784]

Hola

Puede intentar con lo siguiente:

SELECT T0.[ItemCode], T0.[ItemName], T0.[ItmsGrpCod],
   'Propiedad 01'=(SELECT ItmsGrpNam FROM OITG WHERE ItmsTypCod = 1),
   'Propiedad 02'=(SELECT ItmsGrpNam FROM OITG WHERE ItmsTypCod = 2)
FROM OITM T0

Habría que repetir la línea para cada propiedad (son 64).

Otro asunto es colocar el valor del campo, así que habría que crear una subconsulta que traiga los datos de OITM, luego desde la consulta que contiene la subconsulta, cambiar las descripciones de cada propiedad (un poco dispendioso).

Saludos.

Edited by: Hector Daniel Hernandez Bacca on Dec 1, 2009 2:22 PM