Running a where clause from a SQL table

Fri, 08 Jan 2010 10:47:35 GMT

Hi all, I am current using this load statement to create QVD's:

General_Ledger:

////=============================================================================================

// TABLE NAME: F0911

////=============================================================================================

LOAD

// GLKCO as [Company],

GLDCT as [Document Type],

GLDOC as [GL Document No],

// GLDGJ,

date(MakeDate(1900+left(text(GLDGJ),len(GLDGJ)-3))+right(text(GLDGJ),3)-1) as [GL Date],

GLAID as [Account ID],

// GLMCU as [Cost Centre],

// GLOBJ as [Object Account],

TEXT (GLPN) as [Period],

GLAA/100 as [GL Amount],

GLEXA as [GL Description],

GLEXR as [GL Remark],

GLAN8 as [Address No],

GLPO as [Purchase Order No],

GLFY,

GLLT;

SQL SELECT *

FROM TRDTA.F0911;

Store General_Ledger INTO Data/General_Ledger.qvd;

However, there are some 9 million records in this table, what I want to be able to do is only see the records that relate to a list of Cost Centre's that I have held in a SQL table, the reason for them being held in the SQL table is so that the list can be updated and changed. Therefore what I need to find out is, is there a way to have the where clause look at and include the values held in this sql table so the final version looks something like this:

General_Ledger:

////=============================================================================================

// TABLE NAME: F0911

////=============================================================================================

LOAD

// GLKCO as [Company],

GLDCT as [Document Type],

GLDOC as [GL Document No],

// GLDGJ,

date(MakeDate(1900+left(text(GLDGJ),len(GLDGJ)-3))+right(text(GLDGJ),3)-1) as [GL Date],

GLAID as [Account ID],

// GLMCU as [Cost Centre],

// GLOBJ as [Object Account],

TEXT (GLPN) as [Period],

GLAA/100 as [GL Amount],

GLEXA as [GL Description],

GLEXR as [GL Remark],

GLAN8 as [Address No],

GLPO as [Purchase Order No],

GLFY,

GLLT;

SQL SELECT *

FROM TRDTA.F0911 WHERE "SQLTABLE1.COST_CENTRE" = 'Cost Centre';

Store General_Ledger INTO Data/General_Ledger.qvd;

Any ideas anyone?

Cheers

Ben

SV:Running a where clause from a SQL table

Fri, 08 Jan 2010 10:58:43 GMT

Is the table in the same database? If so:

SELECT * FROM TRDTA.F0911 WHERE GLMCU IN (select distinct COST_CENTRE from SQLTABLE1)

/Michael

SV:Running a where clause from a SQL table

Fri, 08 Jan 2010 11:14:40 GMT

Unfortunately not, the database where the F0911 is held is in a different place to the SQL table, any ideas?

Cheers

Ben

SV:Re: SV:Running a where clause from a SQL table

Fri, 08 Jan 2010 13:25:41 GMT

You could do it with the exists function. Load the sqltable1 first and then only load the existing ones from F0911.

Not excactly sure if the code below works as is but something like that.


Load COST_CENTRE
SQL SELECT distinct COST_CENTRE from SQLTABLE1;
Load .....(Other columns),
 GLMCU as [Cost Centre]
WHERE EXISTS(COST_CENTRE,GLMCU)
SQL SELECT *
FROM TRDTA.F0911;

/Michael

topic SV:Running a where clause from a SQL table in QlikView

Running a where clause from a SQL table

SV:Running a where clause from a SQL table

SV:Running a where clause from a SQL table

SV:Re: SV:Running a where clause from a SQL table