Join two tables in script with ODBC

[#1]

Hello!

I have a problem and I don´t know as solve it .. I´ll try explain it ...

I connect with ODBC to my database but my ODBC dont´t allow the sentence LEFT JOIN.

I have two tables join with a field and sql sentence should be:

SELECT F_ECRITUREC.CG_NUM, P_REGLEMENT.R_INTITULE, F_ECRITUREC.EC_MONTANT, F_ECRITUREC.EC_ECHEANCE,
P_REGLEMENT.R_CODE, (F_ECRITUREC.EC_ECHEANCE+P_REGLMENT.R_CODE) AS FechaValor
FROM F_ECRITUREC LEFT JOIN P_REGLEMENT ON F_ECRITUREC.N_REGLEMENT = P_REGLEMENT.CBINDICE

The field "FechaValor" is calculate with fields from both tables ...

How can I do it in QV?

Thanks and  best regards

[#2]

Hi,

You can also use subqueries here, if join is not supported. something like this will give you the same result.

select

F_ECRITUREC.CG_NUM,

(select P_REGLEMENT.R_INTITULE from P_REGLEMENT where F_ECRITUREC.N_REGLEMENT=P_REGLEMENT.CBINDICE) as

          R_INTITULE,

F_ECRITUREC.EC_MONTANT,

F_ECRITUREC.EC_ECHEANCE,

(F_ECRITUREC.EC_ECHEANCE+(select P_REGLEMENT.R_CODE from P_REGLEMENT where F_ECRITUREC.N_REGLEMENT=P_REGLEMENT.CBINDICE)) AS FechaValor

FROM F_ECRITUREC;

[rbecher]

Right, but row based sub selects could be very slow in some databases.. depending on the amount of records of the outer select.

- Ralf

[#4]

yeah...that is true and decision on this can only be taken from the person who knows that system.

[#5]

Hello!!

I  tried your code but "Fecha Valor" is not calculated well;

My code is:

F_ECRITUREC:
LOAD *, IF (Apunte_Sentido=0,Apunte_Importe,0) AS Apunte_Debe,
IF (Apunte_Sentido=1,Apunte_Importe,0) AS Apunte_Haber;
SQL SELECT "CG_NUM" as General_Numero,
    "CT_NUM" as Tercero_Numero,
    (EC_JOUR + JM_DATE)-1 AS Fecha,
    "EC_MONTANT" as Apunte_Importe,
    "EC_SENS" as Apunte_Sentido,
    "JO_NUM" as Diario,
    "EC_PIECE" as Apunte_Asiento,
    "EC_INTITULE" as Apunte_Descripcion,
    "EC_REFPIECE" as Apunte_Factura,
    "EC_ECHEANCE" AS Apunte_Vencimiento,
    "N_REGLEMENT" AS CodPago,
   "EC_LETTRE" AS Compensacion,
   "EC_NO" AS NumInterno
FROM F_ECRITUREC ORDER BY JM_DATE ASC;

EcriturecToReglementMap:
MAPPING LOAD CodPago,
     Apunte_Vencimiento
RESIDENT F_ECRITUREC;

P_REGLEMENT:
LOAD *,
     date((ApplyMap('EcriturecToReglementMap', CBINDICE) + R_CODE),'DD/MM/YYYY') AS FechaValor;
sql SELECT CBINDICE, R_INTITULE, R_CODE FROM P_REGLEMENT;

F_ECRITUREC is a table with many rows and each row has a different due date (EC_ECHEANCE) and different form of payment (N_REGLEMENT). Depending on the method of payment, I have to calculate the "Fecha Valor" adding a number (R_CODE).

P_REGLEMENT is the table with the payment (CBINDICE = N_REGLEMENT).

When you run the script calculates the same value date for each payment, for example, for payment 1 "Fecha Valor" is 29/01/2008 always, for payment is 2/2/ 2008, for payment 3 is 30/01/2008.

I tye to explain better:

The correct is:

N_REGLEMENT     EC_ECHEANCE     CBINDICE     R_CODE     FECHA_VALOR

1                              22/07/2008               1                    1               23/07/2008

1                              25/08/2008               1                    1               26/08/2008

1                              03/06/2009               1                    1               04/06/2009

2                              02/03/2008               2                    2               04/03/2008

2                              05/05/2008               2                    2               07/05/2008

Script does:

N_REGLEMENT     EC_ECHEANCE     CBINDICE     R_CODE     FECHA_VALOR

1                              22/07/2008               1                    1               23/07/2008

1                              25/08/2008               1                    1               23/07/2008

1                              03/06/2009               1                    1              23/07/2008

2                              02/03/2008               2                    2               04/03/2008

2                              05/05/2008               2                    2               04/03/2008

I hope I explained well, my English is not very good ....

Thanks in advance for your help !!!

M.C.

[Miguel_Angel_Baeyens]

Hi,

The ApplyMap() function needs at least two parameters, being the first the name of the mapping table and the second the "input value" for what it will return the "output value" that is, the second field loaded into the mapping table.

So you may need to add some other field to create a unique key field in both the first field of the mapping table and the second parameter of the ApplyMap() to get the expected result. It seems that CBINDICE is not enough to return a unique value from table F_ECRITUREC or in other words, there is no 1:1 correspodence between F_ECRITUREC.N_REGLEMENT and P_REGLEMENT.CBINDICE

Hope that makes sense.

Miguel

[#7]

Gracias Miguel!

Tal y como están los datos no puedo conseguir que haya una única clave entre las dos tablas ya que F_ECRITUREC guarda el indice de P_REGLEMENT y ésta es la que tiene el valor que hay que sumar pero, muchos registros de F_ECRITUREC pueden tener el mismo valor (F_ECRITUREC es una tabla de puntes contables y P_REGLEMENT es la tabla de formas de pago).

Con un simple LEFT JOIN estaria solucionado pero como ya he explicado anteriormente el ODBC de la aplicación no lo permite ...

Voy a ver si puedo hacerlo de otra forma pero de todas formas ....

Muchisimas gracias por la ayuda que me habeis prestado !!!

Thanks everybody!

[#8]

Gracias Miguel!

Tal y como están los datos no puedo conseguir que haya una única clave entre las dos tablas ya que F_ECRITUREC guarda el indice de P_REGLEMENT y ésta es la que tiene el valor que hay que sumar pero, muchos registros de F_ECRITUREC pueden tener el mismo valor (F_ECRITUREC es una tabla de puntes contables y P_REGLEMENT es la tabla de formas de pago).

Con un simple LEFT JOIN estaria solucionado pero como ya he explicado anteriormente el ODBC de la aplicación no lo permite ...

Voy a ver si puedo hacerlo de otra forma pero de todas formas ....

Muchisimas gracias por la ayuda que me habeis prestado !!!

Thanks everybody!

[#9]

Finally, I found solution:

F_ECRITUREC:

LOAD *, IF (Apunte_Sentido=0,Apunte_Importe,0) AS Apunte_Debe,

IF (Apunte_Sentido=1,Apunte_Importe,0) AS Apunte_Haber,

iF (Codigo_Nat>= 1 and Codigo_Nat <=5, General_Numero, null()) as Cuenta_Tesoreria;

SQL SELECT "F_ECRITUREC"."CG_NUM" as General_Numero,

   "CT_NUM" as Tercero_Numero,

   (EC_JOUR + JM_DATE)-1 AS Fecha,

   "EC_MONTANT" as Apunte_Importe,

   "EC_SENS" as Apunte_Sentido,

   "JO_NUM" as Diario,

   "EC_PIECE" as Apunte_Asiento,

   "EC_INTITULE" as Apunte_Descripcion,

   "EC_REFPIECE" as Apunte_Factura,

   "EC_ECHEANCE" AS Apunte_Vencimiento,

   "N_REGLEMENT" AS CodPago,

   "EC_LETTRE" AS Compensacion,

   "EC_NO" AS NumInterno,

   "N_NATURE" AS Codigo_Nat

FROM F_ECRITUREC INNER JOIN F_COMPTEG ON F_ECRITUREC.CG_NUM = F_COMPTEG.CG_NUM ORDER BY JM_DATE ASC;

LEFT JOIN LOAD *;

SQL SELECT "R_INTITULE" AS FormaPago,

"CBINDICE" as CodPago,

"R_Code" as Aplazamiento

FROM P_REGLEMENT;

Apuntes:

LOAD General_Numero,Tercero_Numero, Fecha,Apunte_Importe,Apunte_Sentido,Diario,Apunte_Asiento, Apunte_Descripcion,Apunte_Factura,Apunte_Vencimiento,

CodPago,Compensacion, NumInterno, Codigo_Nat,FormaPago,Aplazamiento,Apunte_Debe,Apunte_Haber,Cuenta_Tesoreria,

if(isnull(Aplazamiento),date(Apunte_Vencimiento,'DD-MM-YYYY'),date(Apunte_Vencimiento+Aplazamiento,'DD-MM-YYYY')) as FechaValor

resident F_ECRITUREC;

drop table F_ECRITUREC;

I hope it is useful..

thanks everyone

[#10]

I encountered the similar issue but with little bit of tweaking  ODBC left join worked.

The main point that I noticed is use of alias instead of absolute path. When I was referring to the absolute path (ie database.tablename) the statement worked perfectly fine in database but ODBC connection in QLikview threw me an error .(DB2 is my backend )

SELECT

    Table1.Field1,

    Table2.Field2

FROM Database1.Table1  LEFT OUTER JOIN Database1.Table2 

ON Table1.Field1 = Table2.Field1

WITH UR ;

So I modifed the query to use an alias and wrote the following query. It worked with out any error through ODBC connection.

//Example code

//Field 3 exist in only one of the table

[Example]:

Load

Field1,

Field2,

Field3;

SQL SELECT

  Field3,

  A.Field1,

    B.Field2

FROM Database1.Table1 as A  LEFT OUTER JOIN Database1.Table2  as B

ON A.Field1 = B.Field1

WITH UR ;

Note: Aliasing is not required in case the field exists in only one table

NB: Please note that the Query is just to illustrate the idea. Please check the syntax according to the database that you are using