cancel
Showing results for 
Search instead for 
Did you mean: 

Get sales quote number to sales order query + order lines on a single line

lsauser
Participant
0 Kudos

Hi all,

This is a 2 part question. The first half is probably something really simple but i cant see why it's not working (it works going from quote to sales order but not the other way). I need this to also show the T5.DocDate of the linked sales quotation but i cant get it to display.

SELECT DISTINCT T0.DocNum,T0.[CardCode], T0.[CardName],T1.FirstName,T1.LastName,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T5.DocDate
 FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT JOIN QUT1 T4 ON T4.BaseEntry = T3.DocEntry AND T4.BaseLine = T3.LineNum
LEFT JOIN OQUT T5 ON T5.DocEntry = T4.DocEntry
WHERE T0.series = '73' AND T0.DocStatus = 'O'

The second half is a bit more complicated and i dont know if it can be done. I need to display all the line items (itemcode and quantity) from a sales order on each details line in Crystal reports. Example below

As always, any help is appreciated.

Accepted Solutions (0)

Answers (1)

Answers (1)

Johan_H
Active Contributor
0 Kudos

Hi Nick,

Part 1: I think maybe the join needs to go the other way around:

LEFT OUTER JOIN QUT1 T4 ON T3.BaseEntry = T4.DocEntry AND T3.BaseLine = T4.LineNum

Part 2: can be done, but is somewhat complicated, and also volatile. What do you expect the report to look like when there are say 25 item lines? Anyway, generally speaking you could use a cursor to dynamically build a query string:

DECLARE @ItemCode NVARCHAR(30)
DECLARE @Quantity NUMERIC(19, 6)
DECLARE @QUERY AS NVARCHAR(MAX) = 'SELECT [all], [the], [other], [columns], '
DECLARE crsr CURSOR
FOR SELECT r.ItemCode, r.Quantity
    FROM [dbo].RDR1 r
    etc.
OPEN crsr
FETCH NEXT FROM crsr
INTO @ItemCode, @Quantity
WHILE @@FETCH_STATUS = 0
 BEGIN
  /*** Build the combination column one row at a time, something like this: ***/
  SET @QUERY = @QUERY + CAST(CAST(@Quantity AS INT) AS NVARCHAR) + ' x ' + @ItemCode + ', '
  FETCH NEXT FROM crsr
  INTO @ItemCode, @Quantity
 END
CLOSE crsr
DEALLOCATE crsr
SET @QUERY = @QUERY + 
' FROM ORDER h
       INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry 
  etc. '
EXEC(@QUERY)

which you would then execute with the EXEC('the query you built with the cursor goes here') function.

Regards,

Johan

lsauser
Participant
0 Kudos

Hey Johan,

I had a feeling it was going to be simple like this, i changed it around a few times but obviously not like this! thanks for that 🙂

Now i just need to see if there is a solution for the second part.

Johan_H
Active Contributor
0 Kudos

Hi Nick,

You managed to answer while I edited my answer to include a solution for part 2. Please have a look, and see if you can make it work.

Regards,

Johan

Johan_H
Active Contributor
0 Kudos

P.S. you will need experiment with the single quotes, until you get a single coherent column, and functioning query.

I notice that I wrote it a little slipshod.

lsauser
Participant
0 Kudos

Hey Johan,

Thanks for adding in a possible solution to the second part, you're going to have to hold my hand a bit here as this is a bit out of my current knowledge, where would this query fit in with my original query?

Regards,

Nick

Johan_H
Active Contributor

Hi Nick,

Basically what you do is, build the query as a piece of text first, and then use the EXEC function to execute that query text. A really simple example:

DECLARE @QUERY AS NVARCHAR(MAX) = 'SELECT * FROM ORDR'
EXEC(@QUERY)

To make this work for you, you need to break your original query into 3 parts of text, the bit before the tricky bit, the tricky bit, and the bit after the tricky bit:

  1. SELECT T0.DocNum, T0.CardCode etc.
  2. The column that is going to show 'Quantity x ItemCode, Quantity x ItemCode, Quantity x ItemCode, etc.' ("1 x CW10, 2 x CD10")
  3. FROM ORDR T0 INNER JOIN etc. WHERE T0.series = '73' etc.

All of these parts of text are combined by the "query builder" query, and then executed.

The tricky bit is the CURSOR, which we use to combine the Quantity and ItemCode from all rows into a single column. This is the basic cursor syntax:

DECLARE <variable1> AS <sql type>
DECLARE <variable2> AS <sql type>
/* etc, etc */
DECLARE crsr CURSOR
FOR <query here>
OPEN crsr
FETCH NEXT FROM crsr
INTO <variable1>, <variable2> /*, etc */
WHILE @@FETCH_STATUS =0 
  BEGIN
    /* Do something with the variables here */
    FETCH NEXT FROM crsr
    INTO <variable1>, <variable2> /*, etc */
  END
CLOSE crsr
DEALLOCATE crsr

Regards,

Johan

lsauser
Participant
0 Kudos

Hey Johan,

I've tried looking up other examples of how this is used to wrap my head around it but everything i've found relates to HANA, will this work with the (other) SQL version of SAP B1?

I had a go at putting it all together but got an error saying incorrect syntaxt near the keyword FROM. Unfortunately i cant tell which FROM it means!

DECLARE @ItemCode NVARCHAR(30)
DECLARE @Quantity NUMERIC(19, 6)
DECLARE @QUERY AS NVARCHAR(MAX) = 'SELECT DISTINCT T0.DocNum,T0.[CardCode], T0.[CardName],T1.FirstName,T1.LastName,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T9.DocDate '
DECLARE crsr CURSOR
FOR SELECT r.ItemCode, r.Quantity
    FROM [dbo].RDR1 r
OPEN crsr
FETCH NEXT FROM crsr
INTO @ItemCode, @Quantity
WHILE @@FETCH_STATUS = 0
 BEGIN
  /*** Build the combination column one row at a time, something like this: ***/
  SET @QUERY = @QUERY + CAST(CAST(@Quantity AS INT) AS NVARCHAR) + ' x ' + @ItemCode + ', '
  FETCH NEXT FROM crsr
  INTO @ItemCode, @Quantity
 END
CLOSE crsr
DEALLOCATE crsr
SET @QUERY = @QUERY + 
' FROM ORDER h
       INNER JOIN RDR1 r ON h.DocEntry = r.DocEntry 
   '
EXEC(@QUERY)
 FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT JOIN INV1 T6 ON T6.BaseEntry = T0.DocEntry AND T6.BaseType = '17'
LEFT JOIN OINV T7 ON T7.DocEntry = T6.DocEntry
LEFT OUTER JOIN QUT1 T8 ON T3.BaseEntry = T8.DocEntry AND T3.BaseLine = T8.LineNum
LEFT JOIN OQUT T9 ON T9.DocEntry = T8.DocEntry
LEFT join dln1 T10 on T10.baseentry = t0.docentry and t3.linenum = T10.baseline
LEFT join odln T11 on T11.docentry = T10.docentry
WHERE T0.series = '73' AND T11.DocNum IS NULL AND T0.DocStatus = 'O'

If i cant get this to work i might have to come up with a different solution.

Regards,

Nick

Johan_H
Active Contributor
0 Kudos

Hi Nick,

This syntax is specifically for MS SQL, and it wouldn't work on HANA, so if you are running on MS SQL, then good thing.

Don't despair, we will get this to work. (then I wrote a long boring technical discourse, which in the end I deleted, because this query is too complicated for a beginner, and it wouldn't have helped you much).

So without further ado, please try this:

DECLARE @ItemCode NVARCHAR(30)
DECLARE @Quantity NUMERIC(19, 6)
DECLARE @QUERY AS NVARCHAR(MAX) = 'SELECT T0.DocNum,T0.[CardCode], T0.[CardName],T1.FirstName,T1.LastName,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T9.DocDate, '''
DECLARE @FINAL_ITEMCODE AS NVARCHAR(30)
DECLARE crsr CURSOR
FOR SELECT T3.ItemCode, T3.Quantity
    FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT JOIN INV1 T6 ON T6.BaseEntry = T0.DocEntry AND T6.BaseType = '17'
LEFT JOIN OINV T7 ON T7.DocEntry = T6.DocEntry
LEFT OUTER JOIN QUT1 T8 ON T3.BaseEntry = T8.DocEntry AND T3.BaseLine = T8.LineNum
LEFT JOIN OQUT T9 ON T9.DocEntry = T8.DocEntry
LEFT join dln1 T10 on T10.baseentry = t0.docentry and t3.linenum = T10.baseline
LEFT join odln T11 on T11.docentry = T10.docentry
WHERE T0.series = '73' AND T11.DocNum IS NULL AND T0.DocStatus = 'O'

OPEN crsr
FETCH NEXT FROM crsr
INTO @ItemCode, @Quantity
WHILE @@FETCH_STATUS = 0
 BEGIN
  /*keep track of the last itemcode, we are going to need to know after the cursor ends*/
  SET @FINAL_ITEMCODE = @ItemCode;
  
  /*** Build the combination column one order row at a time ***/
  IF @QUERY LIKE '%, '''
   BEGIN
    SET @QUERY = @QUERY + '' + CAST(CAST(@Quantity AS INT) AS NVARCHAR) + ' x ' + @ItemCode  
   END
  ELSE
   BEGIN
    SET @QUERY = @QUERY + ', ' + CAST(CAST(@Quantity AS INT) AS NVARCHAR) + ' x ' + @ItemCode 
   END
  FETCH NEXT FROM crsr
  INTO @ItemCode, @Quantity
 END
CLOSE crsr
DEALLOCATE crsr
IF @QUERY LIKE '%, '
   BEGIN
    SET @QUERY = @QUERY + '''' + ''' AS [Items in order]'  
   END
ELSE
 BEGIN
  IF @QUERY LIKE '%'  + @FINAL_ITEMCODE
   BEGIN
    SET @QUERY = @QUERY + ''' AS [Items in order]'  
   END
  ELSE
   BEGIN
    SET @QUERY = @QUERY + ' AS [Items in order]'  
  END
 END
SET @QUERY = @QUERY + 
' FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT JOIN INV1 T6 ON T6.BaseEntry = T0.DocEntry AND T6.BaseType = ''17''
LEFT JOIN OINV T7 ON T7.DocEntry = T6.DocEntry
LEFT OUTER JOIN QUT1 T8 ON T3.BaseEntry = T8.DocEntry AND T3.BaseLine = T8.LineNum
LEFT JOIN OQUT T9 ON T9.DocEntry = T8.DocEntry
LEFT join dln1 T10 on T10.baseentry = t0.docentry and t3.linenum = T10.baseline
LEFT join odln T11 on T11.docentry = T10.docentry
--WHERE T0.series = ''73'' AND T11.DocNum IS NULL AND T0.DocStatus = ''O''
   '
EXEC(@QUERY)

Regards,

Johan

lsauser
Participant
0 Kudos

Hey Johan,

Amazing work on this code!

It doesn't give me any errors, but it seems like the WHERE condition isn't working as it should. It gives me about 10 records for every single order number. Also it is showing the same items for every single record.

Regards,

Nick

Johan_H
Active Contributor

Hi Nick,

Looking back, I was overthinking this completely, and indeed it was not even returning the correct result. So back to the drawing board, and after simplifying the whole thing, please try this:

SELECT T0.DocNum,T0.[CardCode], T0.[CardName]
,T1.FirstName
,T1.LastName
,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T9.DocDate
,stuff((select ', ' + cast(cast(r.Quantity as int) as nvarchar) + ' x ' + r.ItemCode
  from RDR1 r 
  where r.DocEntry = T0.DocEntry
    AND r.LineStatus = 'O' FOR XML PATH('')), 1, 2, '') AS [Items in order]
FROM ORDR T0
INNER JOIN OCPR T1 ON T0.[CntctCode] = T1.[CntctCode]
INNER JOIN OSLP T2 ON T0.SlpCode = T2.SlpCode
INNER JOIN RDR1 T3 ON T3.DocEntry = T0.DocEntry
LEFT OUTER JOIN QUT1 T8 ON T3.BaseEntry = T8.DocEntry AND T3.BaseLine = T8.LineNum
LEFT JOIN OQUT T9 ON T9.DocEntry = T8.DocEntry
WHERE T0.series = '73' AND T0.DocStatus = 'O'
GROUP BY T0.DocEntry, T0.DocNum,T0.[CardCode], T0.[CardName]
,T1.FirstName
,T1.LastName
,T0.Address2,T1.Tel1,T1.Cellolar,T1.E_MailL,T2.SlpName,T9.DocDate

Regards,

Johan