Show only 1 secondary table record along with primary table on cursor

Asked

Viewed 49 times

-1

I have a cursor where it brings me data of a request (mother table) and the order items in xml(daughter table) currently the cursor brings request by request with fetch next, but the daughter table comes with all the items (column "test") and I would like it to come item by item. for example:

request 1 item 1

order 1 item 2

request 1 item 3

The idea is to use a temporary table, but I have no idea how to do this. Could someone give a light? Here’s an image of the current result and code.

DECLARE crs CURSOR
select distinct a.DocEntry
,cardcode,cardname,a.CreateDate,DATEDIFF(day,a.CreateDate,GETDATE()) Dias_Em_Aberto,d.SlpName
,CONCAT(ISNULL(substring((select '/'+ ItemCode from RDR1 where RDR1.DocEntry = a.DocEntry for xml path ('')),2,9999),'')+'  '+substring((select '-'+ CAST(Onhand as varchar(200) )from OITM where OITM.ItemCode = b.ItemCode for xml path ('')),2,9999),'') teste
from ordr a........

Cursor

sql sql-server

  • Make your XML available so we can suggest something

  • "xml" would be the test column, each item code comes separated by bar, but wanted it to be a row if I got it wrong please explain me

  • 1

    I think you’d better put some more information in your question: 1) The structure of the tables involved. 2) sample data from these tables. 3) the expected result

  • Good morning! The structure would be kind of impossible because they are extremely large, are SAP B1 tables. I will add more data and also put the result.

1 answer

1


Hi viva sharing more information as you already suggested. I share here a simple example how to create a temp table with SQL Server. I also share how to remove at end of use:

Create TEMP Table

IF(OBJECT_ID('#ID_Table') IS NULL)
    BEGIN
        --IF NULL CREATE TEMP TABLE
        CREATE TABLE #ID_Table
        (
            [ID] INT
        );
    END

Removes TEMP table

--DROP TEMP TABLE IF EXISTS AFTER USAGE
IF(OBJECT_ID('#ID_Table') IS NOT NULL)
BEGIN
    DROP TABLE #ID_Table
END

  • Thank you very much Ernesto!

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.