Forum Stats

  • 3,781,278 Users
  • 2,254,498 Discussions
  • 7,879,632 Comments

Discussions

Parameter

Mohammed Fareed Hussain
Mohammed Fareed Hussain Member Posts: 20 Red Ribbon
edited May 29, 2019 7:51AM in SQL & PL/SQL

hi all,

i have some issue in parameters the below query for displaying for purchase order number and vendor name and item id

when i am adding parameter based on inventory item name(segmen1) the query is not showing any data.

(query without parameter)

select

    pha.segment1,

    asa.vendor_name,

    msib.inventory_item_id

FROM

    po_headers_all       pha,

    ap_suppliers         asa,

    mtl_system_items_b   msib,

    po_lines_all         pla

WHERE

    pha.vendor_id = asa.vendor_id

    AND msib.inventory_item_id = pla.item_id

    and pla.po_header_id=pha.po_header_id;

(query with parameter)

select

    pha.segment1,

    asa.vendor_name,

    msib.inventory_item_id

FROM

    po_headers_all       pha,

    ap_suppliers         asa,

    mtl_system_items_b   msib,

    po_lines_all         pla

WHERE

    pha.vendor_id = asa.vendor_id

    AND msib.inventory_item_id = pla.item_id

    and pla.po_header_id=pha.po_header_id;

    and msib.segment1=&p;

the above query is working fine but not giving any output.

can anyone help me out of these please.

thanks and regards hussain

Tagged:
Mohammed Fareed HussainBrunoVromanFrank Kulash

Best Answer

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited May 29, 2019 6:49AM Accepted Answer

    Hello Hussain,

    -A-

    * Is msib.segment1 a number?  (if it is a string you should write msib.segment1='&p';)

    * You can check the value that you pass as "p" with for example SELECT '<&p>' FROM dual; (is it what you expect?)

    * And you can use the query without parameter to determine some "p" values that should be OK:

      SELECT '<' || msib.segment1 || '>'

      FROM

          po_headers_all       pha,

          ap_suppliers         asa,

          mtl_system_items_b   msib,

          po_lines_all         pla

      WHERE pha.vendor_id = asa.vendor_id

        AND msib.inventory_item_id = pla.item_id

        AND pla.po_header_id = pha.po_header_id

        AND rownum <= 5

      ;

    -B- Something else:

    May I suggest you to modify your display name 3724454 to something more human-friendly? Please have a look at

    Best regards,

    Bruno Vroman.

    Mohammed Fareed Hussain

Answers

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited May 29, 2019 6:49AM Accepted Answer

    Hello Hussain,

    -A-

    * Is msib.segment1 a number?  (if it is a string you should write msib.segment1='&p';)

    * You can check the value that you pass as "p" with for example SELECT '<&p>' FROM dual; (is it what you expect?)

    * And you can use the query without parameter to determine some "p" values that should be OK:

      SELECT '<' || msib.segment1 || '>'

      FROM

          po_headers_all       pha,

          ap_suppliers         asa,

          mtl_system_items_b   msib,

          po_lines_all         pla

      WHERE pha.vendor_id = asa.vendor_id

        AND msib.inventory_item_id = pla.item_id

        AND pla.po_header_id = pha.po_header_id

        AND rownum <= 5

      ;

    -B- Something else:

    May I suggest you to modify your display name 3724454 to something more human-friendly? Please have a look at

    Best regards,

    Bruno Vroman.

    Mohammed Fareed Hussain
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited May 29, 2019 6:48AM

    Well the obvious answer is that nothing matches the parameter.

    What datatype is segement1?

    What value are you supplying?

    What tool are you running this in?

    Why do you think there's matching data?

    Frank Kulash
  • Mohammed Fareed Hussain
    Mohammed Fareed Hussain Member Posts: 20 Red Ribbon
    edited May 29, 2019 7:00AM

    thanks for your reply the issue is resolved.

    it is a number datatype column.

    thanks and regards.

    hussain

  • Mohammed Fareed Hussain
    Mohammed Fareed Hussain Member Posts: 20 Red Ribbon
    edited May 29, 2019 7:06AM

    Thank you @mr.BrunoVroman

    it was a number datatype and you are right i was missing single 'quote '

    thank you very much

    Sure i will change the number to my name .

    thanks and regards

    hussain

    BrunoVroman
  • Cookiemonster76
    Cookiemonster76 Member Posts: 3,410
    edited May 29, 2019 7:15AM

    If it's a number then you don't need the single quotes.

  • BrunoVroman
    BrunoVroman Member Posts: 1,848 Silver Crown
    edited May 29, 2019 7:51AM

    Hello Hussain,

    I am glad if you could fix your issue but a bit amazed by your comments... Indeed, what you type when prompted "value for p" is substituted to &p literally, so if you type in a number you don't need the quotes:

        WHERE mynumber = &p

      will become for example

        WHERE mynumber = 123

    but for varchars we need the quotes, like for example:

        WHERE mystring = '&p'

    that will become for example

        WHERE mystring = 'Bruno'

    But maybe there is some misunderstanding and possibly you state that what you type is a number (like 123) but the datatype of msib.segment1 is a VARCHAR? if the column is a varchar you should have the quotes (and for example this can make a big difference if we enter for p the value 0123 because segment1 = '0123' is absolutely not the same as segment1 = 0123, the latest being silently modified by Oracle to segment1 = TO_CHAR( 0123 ) that removes the leading zero...

    Remark: &p is changed into the given value before even being sent to the database server, so you might even play with things like: (suggestion: before this, issue SQL> SET VERIFY ON)

       SELECT ... FROM mytable t WHERE t.mystring = '&p';

       Enter value for p: x' or 'haha' = 'haha

    and you will have the surprise of seeing executed

         SELECT ... FROM mytable t WHERE t.mystring = 'x' or 'haha' = 'haha';

    Best regards,

    Bruno.