Good day, I'm trying to execute a query that throws me the auxiliary codes other than two tables that have the same description and can group them so that I show the same description (name) auxiliary with the different auxiliary codes. It should be noted that the same auxiliary description has more than 4 different auxiliary codes. I was trying these consultations without a result that satisfies me:
SELECT T018.NOMBRE RAZON018, T018.AUXILIAR AUX018, TCUE.auxiliar AUXCUE
FROM CUE004II TCUE, I02018 T018, I02017 T017
WHERE T018.NOMBRE = TCUE.razon AND T018.AUXILIAR <> TCUE.auxiliar
ORDER BY T018.NOMBRE;
SELECT MAX(T018.NOMBRE) RAZON018,MIN(TCUE.razon) RAZONTCUE ,T018.AUXILIAR AUX018,TCUE.auxiliar AUXCUE
FROM CUE004II TCUE, I02018 T018
WHERE T018.NOMBRE = TCUE.razon AND T018.AUXILIAR <> TCUE.auxiliar
GROUP BY T018.AUXILIAR, TCUE.razon
in the second I do not know if I'm throwing the data I want, I also want to use a order by
to order for the reason but it does not let me tell me:
Mens. 8127, Level 16, State 1, Line 6 The column I02018.NAME of the
ORDER BY
clause is not valid, because it is not contained in a aggregate function or in theGROUP BY
clause.
In addition to the clause group by
that I'm grouping I imagine shows me only the maximum and lowest value of the records grouped by auxiliary codes, this would work if it had 2 codes for each reason (description, name), but I have up to 3 and 4 codes (possibly more) for reason and I would like to see them all.
If anyone knows the answer I would greatly appreciate it, in case I can solve my problem I will collaborate with the answer myself.
Greetings.
Note: Structure of the 3 tables:
USE [] GO
/****** Object: Table [dbo].[I02017] Script Date: 12/21/2017
10:28:54 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[I02017]( [DOCUMENTO] [char](3) NOT NULL,
[NUMERO] [char](8) NOT NULL, [CONTROL] [numeric](3, 0) NULL,
[PRODUCTO] [varchar](24) NOT NULL, [AUXILIAR] [varchar](8) NULL,
[TRANSACCIO] [char](3) NULL, [UNIDAD] [varchar](3) NULL, [FECHA]
[datetime] NULL, [COSTO] [numeric](16, 4) NULL, [COSTOCOMPR]
[numeric](16, 4) NULL, [DESCUENTO] [numeric](16, 4) NULL,
[EXISTUNIDA] [numeric](16, 4) NULL, [EXISTMONED] [numeric]
(16,4) NULL, [QUANTITY] [numeric] (16, 2) NULL, [BOLIVARES] [numeric] (16, 4) NULL, [ADDITIONAL] [numeric] (16, 2) NULL, [MONTOISV] [numeric] (16, 4) NULL, [ISV] [numeric] (1, 0) NULL, [UNIDAVENTA] varchar NULL, [CONSEMAEST] [numeric] (10, 0) NULL, [DOLLARS] [numeric] (16, 2) NULL, [COSTODOLAR] [numeric] (16, 2) NULL, [WEIGHT] [numeric] (10, 0) NULL, [LOT] [numeric] (10, 0) NULL, [IVATIPOEXE] char NULL, [SELLER] varchar NULL, [IDORDEN] [numeric] (3, 0) NOT NULL, [PRICE] [numeric] (10, 2) NULL, [PERIOD] char NULL, [TIPOPROD] varchar NULL, [CLASEAUXI] varchar NULL, [CANTSCAN] [numeric] (10, 0) NULL, CONSTRAINT [PK_I02017] PRIMARY KEY CLUSTERED ([DOCUMENT] ASC, [NUMBER] ASC, [PRODUCT] ASC, [IDORDEN] ASC) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]
GO
SET ANSI_PADDING OFF GO
ALTER TABLE [dbo].[I02017] ADD CONSTRAINT [DF_I02017_ADICIONAL]
DEFAULT ((0)) FOR [ADICIONAL] GO
____________________
tabla 2018
USE [] GO
/****** Object: Table [dbo].[I02018] Script Date: 12/21/2017
10:35:29 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[I02018]( [DOCUMENTO] [char](3) NOT NULL,
[NUMERO] [char](8) NOT NULL, [TRANSACCIO] [char](3) NULL, [TIPO]
[varchar](2) NOT NULL, [FECHA] [datetime] NULL, [FECHAVENCE]
[datetime] NULL, [AUXILIAR] [varchar](8) NULL, [NOMBRE]
[varchar](60) NULL, [RIF] [varchar](15) NULL, [NIT] [varchar](15)
NULL, [DIRECCION] [varchar](70) NULL, [VENDEDOR] [varchar](8)
NULL, [REFERENCIA] [varchar](15) NULL, [PEDIDO] [varchar](12)
NULL, [ORDEN] [varchar](12) NULL, [FACTURA] [varchar](12) NULL,
[REQUISICIO] [varchar](12) NULL, [ENTREGA] [varchar](12) NULL,
[TASAISV] [numeric](6, 2) NULL, [MONTO] [numeric](16, 2) NULL,
[MONTOISV] [numeric](16, 2) NULL, [TASA] [numeric](5, 2) NULL,
[ALIASUSUA] [varchar](15) NULL, [IMPRESA] [char](1) NULL,
[NROCONTROL] [varchar](12) NULL, [FECHAORIGI] [datetime] NULL,
[IVARETENCI] [numeric](16, 2) NULL, [IVACONTRIB] [char](1) NULL,
[GIROS] [char](1) NULL, [ZONAENTR] [varchar](10) NULL, [CAMION]
[varchar](2) NULL, [PRESUPUEST] [varchar](12) NULL, [OBSERVACIO]
[text] NULL, [TELEFONO] [varchar](25) NULL, [CONSECUNIC]
[varchar](12) NULL, [RETIMPTO] [numeric](9, 2) NULL, [IVASERIE]
[varchar](5) NULL, [APLICORIVA] [bit] NOT NULL, [SCANEADO]
[char](1) NULL, [SERIMPFIS] [varchar](15) NULL, [Decreto34]
[char](1) NULL, [DESCUENTO] [numeric](16, 2) NULL, CONSTRAINT
[PK_I02018] PRIMARY KEY CLUSTERED ( [DOCUMENTO] ASC, [NUMERO]
ASC, [TIPO] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE =
OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS
= ON) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
SET ANSI_PADDING OFF GO
ALTER TABLE [dbo].[I02018] ADD CONSTRAINT [DF_I02018_APLICORIVA]
DEFAULT ((1)) FOR [APLICORIVA] GO
ALTER TABLE [dbo].[I02018] ADD CONSTRAINT [DF_I02018_Decreto34]
DEFAULT ('N') FOR [Decreto34] GO
ALTER TABLE [dbo].[I02018] ADD CONSTRAINT [DF_I02018_DESCUENTO]
DEFAULT ((0)) FOR [DESCUENTO] GO
____________________________________
tabla 3: tcue
USE [DAT01LAVILLA2] GO
/****** Object: Table [dbo].[CUE004II] Script Date: 12/21/2017
10:37:02 ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
SET ANSI_PADDING ON GO
CREATE TABLE [dbo].[CUE004II]( [auxiliar] [varchar](8) NULL,
[razon] [varchar](100) NULL, [rif] [varchar](15) NULL ) ON
[PRIMARY]
GO
SET ANSI_PADDING OFF GO