Serialnr query

[Former Member]

Hello all,

I started working on my serialnr query again, since it's not finished yet.

What my training period speculator wants is an overview of the serialnumbers. He wants the ability to search on a serial number. When you search for it he want to see the result of "who brought the serialnumber in" and to "who is the serialnr sold to"

Currently I only have the "who brought the serialnumber in" part working, but I also want the "sold to part" working. So like this information, but than you can search on the serialnr:

http://www.comm-co.com/images/serienr.jpg

This is my sql code so far:

SELECT T1.IntrSerial, T1.ItemCode, T1.ItemName, T1.WhsCode, T1.CardCode AS 'Leverancier code' , T1.InDate AS ' Inkoopdatum' , T2.CardName AS 'Leverancier',

FROM OSRI T1 LEFT OUTER JOIN OCRD T2 ON T1.CardCode = T2.CardCode

WHERE T1.IntrSerial = '[%0]' FOR BROWSE AND

T2.CardCode IN

( SELECT T3.Direction

FROM SRI1 T3 LEFT OUTER JOIN OCRD T2 ON T3.CardCode = T2.CardCode );

-

---

T2.CardCode IN

( SELECT T3.Direction

FROM SRI1 T3 LEFT OUTER JOIN OCRD T2 ON T3.CardCode = T2.CardCode );

This part is quite messie and it's far from good, but I've been trying some things out

Anyone who can help me?

Thanks in advance!

Martijn Ivens

[Former Member · Accepted solution]

Hi Matjin,

Hope this is more or less what you want...

SELECT T1.IntrSerial, T1.ItemCode, T1.ItemName, T1.WhsCode, T0.CardCode, T0.CardName, T0.DocDate, T0.Direction
FROM SRI1 T0
LEFT OUTER JOIN OSRI T1 ON T1.SysSerial = T0.SysSerial and T1.ItemCode = T0.ItemCode
WHERE T1.IntrSerial = '[%0]'

Hope it helps,

Adele

[Former Member]

Hi Martijn,

Do you want the result in one line or in two lines? By two lines I mean one for the supplier and one for the customer or do you want both the in and out parts in one line?

Thanks,

Adele

[Former Member]

Hey Adele,

Thanks for the reply. I was trying to get the result in 2 lines. So as you said, 1 for the supporter and 1 for the supplier.

Thanks,

Martijn

[Former Member]

As always you're my query problem solver

Thanks Adele, it works great!

Sorry i'm late with the reaction, but had some days off...