Line Loop - select active row - in order form - SAP HANA

Answered

Patrice Vigier

• January 29, 2021 10:06
• Edited

Hello,

I want in Line Loop, for an order, only select active row

do you know how to do this ?

My hana sql would be

SELECT T1."LineStatus" FROM "ORDR" T0 INNER JOIN "RDR1" T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."DocNum" = 11023360 AND T1."LineStatus" = 'O' LIMIT 1

but I cannot make it work in the loop
I have a lake of hana syntax.....

I wrote

SELECT CASE 
WHEN (SELECT T1."LineStatus" FROM "ORDR" T0 INNER JOIN "RDR1" T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."DocNum" = 11023360 AND T1."LineStatus" = 'O') ='O'
THEN 
(
SELECT 'RES' FROM DUMMY 
) 
END FROM Dummy

which make an error "General error;305 single-row query returns more than one row"

Thank you in advance

Patrice

Vote

0

• Official comment

  

  Patrice Vigier

  • January 30, 2021 00:34

  Nadav Caridi thank you again.

  I agree I have the RDR1 I need already in the grid, I found out because of your advise.

  Regarding one statement instead of two, yes

  SELECT CASE 
  WHEN $[$38.40.0] = 'O' and $[$38.1.0] <>''
  THEN
  (
  SELECT 'RES' FROM DUMMY
  ) 
  END FROM Dummy
  
  

   
• 

  Nadav Caridi

  • January 29, 2021 12:24

  Hi, 

  Not sure what you're trying to achieve, but with line loop I usually process one row at a time 

  your dynamic syntax should probably check the row status e.g. field : $[$38.40.0]

  p.s the line loop boyum videos were really useful to me when I first started.

   

  Good luck . 
• 

  Patrice Vigier

  • January 29, 2021 12:56

  Thank you Nadav for your answer.

  You help me a lot with $[$38.40.0, grrrrrrr I did not see this one, it is what I need to work only on active line. I was trying to make a Hana sql command to read ORDR and RDR1n ti check if the line is active.

  However I need to find example with Hana sql script to use with line loop. Most of all example we see are with SQL not HANA. There is a tool to convert but it is limited.

  Take care

   
• 

  Nadav Caridi

  • January 29, 2021 13:08

  I use SBO so I'm not sure about the differences . 

  wouldn't this work when you loop through ?

  SELECT CASE 
  WHEN ($[$38.40.0]='O')
  THEN 
  (
  SELECT 'RES' FROM DUMMY 
  ) 
  END FROM Dummy

   

   

   

   
• 

  Patrice Vigier

  • January 29, 2021 14:07

  Yes this work.

  In my case I solved my problem, here is my script

  

  but I still would like to know how to put a more complex SQL HANA like

  SELECT T1."LineStatus" FROM "ORDR" T0 INNER JOIN "RDR1" T1 ON T0."DocEntry" = T1."DocEntry" WHERE T0."DocNum" = 11023360 AND T1."LineStatus" = 'O' LIMIT 1
  
  

  If this make sense in line loop....
• 

  Nadav Caridi

  • January 29, 2021 14:42

  The way I see it With line loop you query the contents of each line. You can call sql statements of all sorts but you already hold most of the rdr1 data in the table so why do it?
  Other than that with the image you attached you can combine both conditions with an And operator

  Glad it worked out for you.
  Good luck

Please sign in to leave a comment.