HI,

I need to do the next join but I'm having problems with the execution.

I have version of llblgen 2.6.11.1105

This is my SQL statement:

  SELECT *   FROM AR4_DIAN_1001_PAGOS_RETEFTE
    FULL outer JOIN AR4_DIAN_1001_NITS_REPORTADOS  ON AR4_DIAN_1001_PAGOS_RETEFTE.NIT = AR4_DIAN_1001_NITS_REPORTADOS.NIT  
   WHERE  AR4_DIAN_1001_PAGOS_RETEFTE.FUENTE_DATOS <> 'CONSOL' AND AR4_DIAN_1001_NITS_REPORTADOS.NIT   IS NULL   
    order by AR4_DIAN_1001_NITS_REPORTADOS.NIT  

These are the tables I'm using:

CREATE TABLE [dbo].[AR4_DIAN_1001_PAGOS_RETEFTE](
    [IDCONSECUTIVO] [bigint] IDENTITY(1,1) NOT NULL,
    [FUENTE_DATOS] [varchar](50) NOT NULL,
    [FECHACORTE] [date] NOT NULL,
    [CONCEPTO] [numeric](5, 0) NOT NULL,
    [TIPO_ID] [numeric](3, 0) NOT NULL,
    [NIT] [varchar](25) NOT NULL,
    [PRIMER_APELLIDO] [varchar](100) NOT NULL,
    [PAGO_ABONO_DEDUC] [numeric](22, 3) NULL,
    [PAGO_ABONO_NO_DEDUC] [numeric](22, 3) NULL,
    [IVA_DEDUC] [numeric](22, 3) NULL,
    [IVA_NO_DEDUC] [numeric](22, 3) NULL,
    [RETEFTE_PRACT_RENTA] [numeric](22, 3) NULL,
    [RETEFTE_ASUM_RENTA] [numeric](22, 3) NULL,
    [RETEFTE_IVA_REGCOMUN] [numeric](22, 3) NULL,
    [RETEFTE_IVA_NO_DOMIC] [numeric](22, 3) NULL,
    [NRO_LINEA] [numeric](8, 0) NOT NULL,
 CONSTRAINT [PK_AR4_DIAN_1001_PAGOS_RETEFTE] PRIMARY KEY CLUSTERED 
(
    [IDCONSECUTIVO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)  


CREATE TABLE [dbo].[AR4_DIAN_1001_NITS_REPORTADOS](
    [IDCONSECUTIVO] [bigint] IDENTITY(1,1) NOT NULL,
    [NIT] [varchar](25) NOT NULL,
 CONSTRAINT [PK_AR4_DIAN_1001_NITS_REPORTADOS] PRIMARY KEY CLUSTERED 
(
    [IDCONSECUTIVO] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
)

This is my code:

            Dim tlistMenor As New DataTable()
            Dim OrdenGralMenor As New SortExpression()   'Ar4Dian1001PagosRetefteFields.Concepto Or SortOperator.Ascending)

            Dim fieldsMenor As New Resultsetfields(9)
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.Concepto, 0, "Concepto", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.PagoAbonoDeduc, 1, "PagoAbonoDeduc", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.PagoAbonoNoDeduc , 2, "PagoAbonoNoDeduc", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.IvaDeduc, 3, "IvaDeduc", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.IvaNoDeduc , 4, "IvaNoDeduc", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.ReteftePractRenta, 5, "ReteftePractRenta", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.RetefteAsumRenta , 6, "RetefteAsumRenta", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.RetefteIvaRegcomun, 7, "RetefteIvaRegcomun", "NIT_SINREP")
            fieldsMenor.DefineField(Ar4Dian1001PagosReteftefields.RetefteIvaNoDomic , 8, "RetefteIvaNoDomic", "NIT_SINREP")

            fieldsMenor(1).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.PagoAbonoDeduc})
            fieldsMenor(2).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.PagoAbonoNoDeduc})
            fieldsMenor(3).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.IvaDeduc})
            fieldsMenor(4).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.IvaNoDeduc})
            fieldsMenor(5).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.ReteftePractRenta})
            fieldsMenor(6).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.RetefteAsumRenta})
            fieldsMenor(7).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.RetefteIvaRegcomun})
            fieldsMenor(8).ExpressionToApply = New DbFunctionCall("CAST({0} AS FLOAT  )", New Object() {Ar4Dian1001PagosRetefteFields.RetefteIvaNoDomic})
                  
            fieldsMenor(1).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(2).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(3).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(4).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(5).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(6).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(7).AggregateFunctionToApply = AggregateFunction.Sum
            fieldsMenor(8).AggregateFunctionToApply = AggregateFunction.Sum

            Dim groupByClauseMenor As New GroupByCollection()
            groupByClauseMenor.Add(fieldsMenor(0))


            Dim FilterGralMenor As IPredicateExpression = New PredicateExpression()
            FilterGralMenor.Add(Ar4Dian1001PagosReteftefields.Fechacorte = localdtfechacorte  And 
                                Ar4Dian1001PagosReteftefields.FuenteDatos <> "CONSOL" )

            Dim dtDefinition As New DerivedTableDefinition(fieldsMenor, "NIT_SINREP", FilterGralMenor, groupByClauseMenor)

            Dim relation As New DynamicRelation(dtDefinition, JoinHint.Inner, _
                                       EntityType.Ar4Dian1001NitsReportadosEntity, "O", _
                                      (New EntityField2(Ar4Dian1001PagosRetefteFieldIndex.Nit.ToString(), "NIT_SINREP", GetType(String)) = _
                                       Ar4Dian1001NitsReportadosFields.Nit.SetObjectAlias("O")))

            Dim bucket_Join As New RelationPredicateBucket()
            bucket_Join.Relations.Add(relation)
            bucket_Join.SelectListAlias = "O"

            Using adapterGral As New DataAccessAdapter(sConnect)
                adapterGral.FetchTypedList(fieldsMenor, tlistMenor, bucket_Join, 0, OrdenGralMenor, True)
            End Using

This is the error that is always generating in me

"An exception was caught during the execution of a retrieval query: El identificador formado por varias partes 
""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.FECHACORTE"" no se pudo enlazar." & vbCrLf & 
"El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.FUENTE_DATOS"" no se pudo enlazar." & vbCrLf & 
"El identificador formado por varias partes ""LPA_N1.Concepto"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""LPA_N1.Concepto"" no se pudo enlazar." & vbCrLf & 
"El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.PAGO_ABONO_DEDUC"" no se pudo enlazar." & vbCrLf & 
"El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.PAGO_ABONO_NO_DEDUC"" no se pudo enlazar." & vbCrLf & 
"El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.IVA_DEDUC"" no se pudo enlazar." & vbCrLf & 
"El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.IVA_NO_DEDUC"" no se pudo enlazar
." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_PRACT_RENTA"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_ASUM_RENTA"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_IVA_REGCOMUN"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_IVA_NO_DOMIC"" no se pudo enlazar." & vbCrLf & "El nombre de columna 'Nit' no es válido." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.PAGO_ABONO_DEDUC"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.PAGO_ABONO_NO_DEDUC"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.IVA_DEDUC"" no se pudo enlazar." & vbCrLf & "El identificador
 formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.IVA_NO_DEDUC"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_PRACT_RENTA"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_ASUM_RENTA"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_IVA_REGCOMUN"" no se pudo enlazar." & vbCrLf & "El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.RETEFTE_IVA_NO_DOMIC"" no se pudo enlazar.. Check InnerException, QueryExecuted and Parameters of this exception to examine the cause of this exception."

I appreciate your collaboration

Could you post the SQL query that's actually generated? As the query you give at the top of your post doesn't match what you create with the code. You can obtain the query using Tracing: https://www.llblgen.com/Documentation/2.6/hh_goto.htm#Using%20the%20generated%20code/gencode_troubleshootingdebugging.htm#conventions

Hi Otis,

The query doesn't get generated, it always can't seem to bind the fields.

El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.FECHACORTE"" no se pudo enlazar."

I have done many tests but I have not been able to get the query.

Can you give me an example in which you join two tables using the SUM function, make a group by and finally generate the results in a FetchTypedList?

That example will surely help me

Thank you.

Manu1212 wrote:

Hi Otis,

The query doesn't get generated, it always can't seem to bind the fields.

El identificador formado por varias partes ""AYR.dbo.AR4_DIAN_1001_PAGOS_RETEFTE.FECHACORTE"" no se pudo enlazar."

I have done many tests but I have not been able to get the query.

Can you give me an example in which you join two tables using the SUM function, make a group by and finally generate the results in a FetchTypedList?

the error is coming from the database, so the query is generated and executed, and sql server throws an error. It's likely an alias error where you forgot to specify an alias. Please set up tracing like it's described in the documentation. Alternatively you could use the SQL Profiler that comes with SQL Server and see what query is executed.