Hi All ,

I need you to discuss and share your experience & thought about my query.
Background :
Our setup is multi company. We have Finance Company & Logistic Company.
So, when user need to simplify their reports for checking, we will create query using session ttadv3580m000 .

Situation :
My user request to have a report for goods receipt together with its Integration ID . The shall able to display total amount for selected date range.

Issue :
I have created the report, with help of google, I manage to create the script to calculate the total amount. But, my report displays repeatingly same line of receipt with different Integration ID.
This making me confuse where to fine tune my query.

I have attached the query results with its text for better view :)

Thank you in advance

Can you post just the query in this thread? Someone might see the issue with it. I am not too familiar with the gld tables and such - plus I know nothing about ln.

Mark,

The query is on Sheet 2 of the attached file.

Datin,

You can try refers to for your query instead of using comparison "=" operator.

Refer this (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_references)

Hi,

I guess you might be doing it wrong by taking whinr110 as your base table. To improve this, make whina113 as your base table and get other information from the related tables using refers to and see if that works for you.

In that way, you will have cost component wise breakup of the data.

Original Query
select
whinr110.item,
tcibd001.dsca,
tcibd001.citg,
whinr110.trdt,
whinr110.cwar,
whina113.amnt(1),
whinr110.qstk,
whinr110.itid,
whina112.orno,
tdipu001.prip
from
tcibd001,
tdipu001,
whina112,
whina113,
whinr110
where
whinr110.trdt >= whinr110.trdt.f and
whinr110.trdt <= whinr110.trdt.t and
whinr110.cwar >= whinr110.cwar.f and
whinr110.cwar <= whinr110.cwar.t and
whinr110.item >= whinr110.item.f and
whinr110.item <= whinr110.item.t and
whinr110.orno >= whinr110.orno.f and
whinr110.orno <= whinr110.orno.t and
tdipu001.item = whinr110.item and
whina113.cpcp = "MAT" and
whinr110.cwar = whina113.cwar and
whinr110.cwar = whina112.cwar and
whinr110.item = tcibd001.item and
whinr110.item = whina113.item and
whinr110.item = whina112.item and
whinr110.itid = whina112.itid and
whinr110.orno > "TRN000001" and
whinr110.orno <= "TRN999999" and
whinr110.trdt = whina113.trdt and
whinr110.trdt = whina112.trdt


Modified Query
select
whinr110.item,
tcibd001.dsca,
tcibd001.citg,
whinr110.trdt,
whinr110.cwar,
whina113.amnt(1),
whinr110.qstk,
whinr110.itid,
whina112.orno,
tdipu001.prip
from
tcibd001,
tdipu001,
whina112,
whina113,
whinr110
where
whina113.trdt >= whina113.trdt.f and
whina113.trdt <= whina113.trdt.t and
whina113.cwar >= whina113.cwar.f and
whina113.cwar <= whina113.cwar.t and
whina113.item >= whina113.item.f and
whina113.item <= whina113.item.t and
whina113._index1 refers to whinr110 unref skip and
whina113._index1 refers to whina112 unref skip and
whina113._index1 refers to tcibd001 unref skip and
whina113._index1 refers to tdipu001 unref skip and
whinr110.orno > "TRN000001" and
whinr110.orno <= "TRN999999" and
whina113.cpcp = "MAT"

Mark,

The query is on Sheet 2 of the attached file.

Datin,

You can try refers to for your query instead of using comparison "=" operator.

Refer this (http://www.baanboard.com/programmers_manual_baanerp_help_functions_database_handling_references)

None of my "stuff" liked the .xls spreadsheet so I did not open it - not sure what was up with it. And it would not have mattered since I do not know ln. :)

It will be so much better to inculcate the whole query into a Normal Print Session.


string rep.mat

rep.mat = "MAT"

select whinr110.item, |Item
tcibd001.dsca, |Description
tcibd001.citg, |Item Group
whinr110.trdt, |Transaction Date
whinr110.cwar, |Warehouse
whinr110.qstk, |Quantity (Inventory Unit)
whinr110.itid, |Inventory Transaction ID
tdipu001.prip |Purchase Price
from
whinr110 |Inventory Transactions by Item and where
tcibd001, |Items - General
tdipu001, |Item Purchase Data
where whinr110.trdt >= :form.trdt.f and
whinr110.trdt <= form.trdt.t and
whinr110.cwar >= :form.cwar.f and
whinr110.cwar <= :form.cwar.t and
whinr110.item >= :form.item.f and
whinr110.item <= :form.item.t and
whinr110.orno >= :form.orno.f and
whinr110.orno <= :form.orno.t and
whinr110.item refers to tdipu001 and
whinr110.item refers to tcibd001 and
whinr110.item refers to tdipu001
selectdo
select whina113.amnt(1)
from whina113
where whina113._index1 = {:whina110.item}
and whina113.mat = :rep.mat
and whina113.trdt = :whina110.trdt
selectdo
endselect
select whina112.orno
from whina112
where whina112._index1 = {:whina110.item}
and whina112.cwar = :whina110.cwar
and whina112.itid = :whina110.itid
and whina112.trdt = :whina110.trdt
selectdo
endselect
rprt_send()
endselect



Also please use Indexes for selection as much as possible. the system will take lesser time.

Hi All,

thank you for all information that being helpful .

Bushanchanda ,

Am doing your suggested text ..

select
whina113.item, | Item
tcibd001.dsca, | Description
tcibd001.citg, | Item Group
whina113.trdt, | Transaction Date
whina113.cwar, | Warehouse
whina113.amnt(1), | Amount
whinr110.qstk, | Quantity (Inventory Unit)
whina112.itid, | Inventory Transaction ID
whinr110.orno, | Order
tdipu001.prip | Purchase Price
from
tcibd001, | Items - General
tdipu001, | Item Purchase Data
whina112, | Inventory Receipt Transactions
whina113, | Inventory Receipt Transaction - Cost Details
whinr110 | Inventory Transactions by Item and Warehouse
where
whina113.trdt >= whina113.trdt.f and
whina113.trdt <= whina113.trdt.t and
whina113.cwar >= whina113.cwar.f and
whina113.cwar <= whina113.cwar.t and
whina113.item >= whina113.item.f and
whina113.item <= whina113.item.t and
whina113._index1 refers to whinr110 and
whina113._index1 refers to whina112 and
whina113._index1 refers to tcibd001 and
whina113._index1 refers to tdipu001 and
whinr110.orno <= "TRN000001" and
whinr110.orno >= "TRN999999" and
whina113.cpcp = "MAT"

but it gave me no data ..