cancel
Showing results for 
Search instead for 
Did you mean: 

Simple Query on Sales Quotes

abdul_ranginwala
Participant
0 Kudos

Hello Experts,

I am trying to create a simple query on sales quotes. Each of our sales quotes have multiple detail lines (QUT1 table). I need to extract some data from the first raw that appear on the detail line. I can not say show me "Where T1.linenum < 1" because sometimes rows get deleted so the number do not start at zeor. Any idea how I can solve this issue. Here is the example of my query:

SELECT T0.[DocNum], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T1.[U_WordCount] FROM OQUT T0  Left JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDueDate] between [%0] and [%1]

Thank You.

Abdul Ranginwala

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Abdul,

Try:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T0.[NumAtCard]

FROM OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry

WHERE T0.[DocDate] between [%0] and [%1] and T1.linenum = (select min(linenum) from qut1 where docentry=t0.docentry)

abdul_ranginwala
Participant
0 Kudos

Hi Gordon,

Thank you for your reply. It worked.

Answers (2)

Answers (2)

kothandaraman_nagarajan
Active Contributor
0 Kudos

Hi,

Try this query:

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T0.[NumAtCard] FROM OQUT T0  INNER JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry WHERE T1.[LineNum] = '0' and  T0.[DocDate] between [%0] and [%1] GROUP BY T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate], T1.[ItemCode], T1.[Dscription], T0.[NumAtCard]

Thanks & Regards,

Nagarajan

abdul_ranginwala
Participant
0 Kudos

Dear Nagarajan,

Thanks for the help but I can not use "[LineNum] = 0" because sometimes line 0 is being deleted.

Regard,

Abdul...

Former Member
0 Kudos

Abdul,

Please check:  (TOP 1)

SELECT TOP 1 T0.[DocNum], T0.[DocDueDate], T0.[CardName], T0.[NumAtCard], T1.[U_WordCount] FROM OQUT T0  Left JOIN QUT1 T1 ON T0.DocEntry = T1.DocEntry WHERE T0.[DocDueDate] between [%0] and [%1]

Regards,

Marcelo Silva Santos

abdul_ranginwala
Participant
0 Kudos

Dear Marcelo,

Thank you for the replay. The "Top 1" only shows me one record. I need list of all sales quotes within the time frame but for each sales quote, I need the top line on the detail section, i.e. top 1 of QUT1. Any idea how can I active it.

Thanks

Abdul...