Unable to Link Two Tables of Different Database

[vinaypurohit]

Hi,

I am new to QlickView. I have two tables of different database. Both the table have two common columns. 

First Table have :

ItemCode, BatchNumber, TotalPrice

Second Table have:

ItemCode, BatchNumber, Cost

I want a result in Table Box :

ItemCode, BatchNumber, TotalPrice, Cost

I am able to fetch the Cost for few rows, rest of the rows i am getting NULL value even Cost price is available for that batch.

[chipinapi]

You have synthetic keys pls check...

[vinaypurohit]

Hi chipinapi,

Thanks for your reply. Please help how can i remove synthetic key so that i can get correct result. I am new to QlickView and not aware about it. After removing it would i able to link both the tables of different database as explained.

Thanks Again,

Vinay Purohit

[Arthur_Fong]

Can you try to load the stock table first and attach the table here?

Just the stock table without bill register table.

[smallbee001]

you could make a combined group like group1 contains ItemCode, BatchNumbe

[vinaypurohit]

I have attached Stock Table.

Thanks.

[Arthur_Fong]

Here's the catch:

In your registry table, Batch is text format.

In stock table , Batch is numeric.

When QV creates association, it is not able to link these two columns with different formats together.

Try this script after you have loaded your stock table.

tempHMS_STOCKGRNITEMS:
load text(Code)as Code, Batch ,Cost
resident "HMS_STOCKGRNITEMS(NOLOCK)";

drop table "HMS_STOCKGRNITEMS(NOLOCK)";

Thanks and regards,

Arthur Fong

[vinaypurohit]

Thank you Very Much Arthur Fong,

This script is not working, may i convert the batch to VARCHAR in SQL statement using Convert() function like:-

SELECT Distinct GI.ITEMDR "Code",
CONVERT(VARCHAR(20),GB.BatchNo) "Batch"

Regards,

Vinay Purohit

[Arthur_Fong · Accepted solution]

Sure.... as long both columns are text field