Getting syntax error in @PROMPT Oracle SQL Developer?

Question

SAP Bo has generated a SQL query for a report which is not working in Oracle SQL Developer. I am getting 'missing expression' error. Below is the code, please help me finding out the error here. and also help me to understand the role of @PROMPT here.

SELECT 
  CASE
    WHEN MATERIAL IN @PROMPT('Enter materials which may not be shipped to SIMS:','C',,Multi,Free,Persistent)  OR material IN @PROMPT'Enter materials which may not be shipped to SIMS(2):','C',,Multi,Free,Persistent) 
    THEN 'Not SIMS'
    WHEN
material IN @PROMPT('Enter materials which can be shipped to SIMS:','C',,Multi,Free,Persistent) 
    THEN 'SIMS'
END
FROM
Materials;

Expecting query should work as the same is working in SAP BO.

@PROMPT is SAP syntax, not SQL. You might be looking for bind variables or substitution variables? — Alex Poole
– Alex Poole, Commented Nov 6, 2022 at 9:43

d r · Accepted Answer · 2022-11-06 10:11:03Z

1

Try it like this:

SELECT 
  CASE
    WHEN MATERIAL IN(&Enter_NON_ShippableMaterials_SIMS) OR MATERIAL IN(&Enter_NON_ShippableMaterials_SIMS_2) THEN 'Not SIMS'
    WHEN MATERIAL IN(&Enter_SIMS_ShippableMaterials) THEN 'SIMS'
  END "SIMS_OR_NOT"
FROM

You will be prompted to input data 3 times. If data are of type VarChar2 and you need to enter a list of data as input then do it within single quotes separated by comma - 'MAT_1', 'MAT_2', ....
Regards...

answered Nov 6, 2022 at 10:11

d r

8,6722 gold badges9 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Getting syntax error in @PROMPT Oracle SQL Developer?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related