Add Parameter to hardcoded string in decode function

10-11  Source: Network gathering  Views:1 

Advertisement
I have a query something like this :
Select ............
,decode((select bill_sequence_id
from bom_structures_b a
where a.assembly_item_id =POL.ITEM_ID
and a.organization_id=FSP.INVENTORY_ORGANIZATION_ID
and a.alternate_bom_designator is null
and msi.bom_enabled_flag='Y'
),null,null,'test_url'||a.bill_sequence_id) "Destination"
From........
where..........
i want to add the column value that I searched at the end of 'test_url'
If I did decode(...,null,null,'test_url'||222222222) it worked, but when i give bill_sequence_id or a.bill_sequence_id , it cries for invalid identifier.
please assist.
Thanks
Its good to flush out your tear glands sometimes.Sure is. I've tried to dechiffer your "query" and to me it looks like something like this:
select <some_columns_we_dont_know_of>
     , nvl2(a.bill_sequence_id, 'test_url'|| to_char(a.bill_sequence_id), null) "Destination"
  from (select assembly_item_id
             , organization_id
          from bom_structures_b
         where alternate_bom_designator is null) a
     , (select pol.item_id
             , fsp.inventory_organization_id
          from pol
             , fsp
             , msi
             , <other_tables_we_dont_know_of>
         where <your_join_conditions>
           and msi.bom_enabled_flag='Y'
           and <other_predicates>) b
where a.assembly_item_id(+) = b.item_id
   and a.organization_id(+) = b.inventory_organization_id;Or perhaps
select <some_columns_we_dont_know_of>
      , case
          when a.bill_sequence_id is not null and b.bom_enabled_flag = 'Y'
          then 'test_url'|| to_char(a.bill_sequence_id)
        end "Destination"
   from (select assembly_item_id
              , organization_id
           from bom_structures_b
          where alternate_bom_designator is null) a
      , (select pol.item_id
              , fsp.inventory_organization_id
              , msi.bom_enabled_flag
           from pol
              , fsp
              , msi
              , <other_tables_we_dont_know_of>
          where <your_join_conditions>
            and msi.bom_enabled_flag='Y'
            and <predicates>) b
  where a.assembly_item_id(+) = b.item_id
   and a.organization_id(+) = b.inventory_organization_id;Regards
Peter
Related articles
  • Add Parameter to hardcoded string in decode function 10-11

    I have a query something like this : Select ............ ,decode((select bill_sequence_id from bom_structures_b a where a.assembly_item_id =POL.ITEM_ID and a.organization_id=FSP.INVENTORY_ORGANIZATION_ID and a.alternate_bom_designator is null and msi

  • Problem with decode function. 10-11

    Hi, Can anyone of you help me out in solving this? It is like i wish to give different select statements according to the value of a parameter entered by user USING DECODE FUNCTION.The select statement contains some other select statements inside it.

  • Suggestion on decode function 10-11

    DECODE(pc.prtyp_refno,4053,0,201979,0,201980,0,205495,0,201982,0,201981,0,1) = 0 could some body explain me what this exactly it means ,does pc.prtyp_refno is in the numbers provided or it should not be in the numbers given Many thanks in advanceHi,

  • Error -3016 when trying to compare a column value via "decode" function 10-11

    The following statement:   select * from turbine_user   where login_name  = decode(1, 2,  'name1', 'name2') yields a "-3016 Invalid numeric constants" error. OTOH, the following variations work as expected:   (a)   select * from turbine_user   w

  • Regarding decode function 10-11

    Hi all, i want to know abt decode function in oracle-sql i..e passing the parameters in decode using decode using ":" bind parameter select JOB, decode(:j,'CLERK','MANAGER','ANALYST','EXEC',JOB) FROM EMP; i declared the j variable in sql environ

  • Error while replacing IF statements with DECODE function in procedure 10-11

    Hi All, I have created a procedure which has nested IF statements. Now I want to replace the IF statements with DECODE functions to improve performance. Procedure: IF (var_int_sev = '0') THEN var_sev := '2'; ELSE SELECT sev INTO var_int_sev FROM erro

  • DECODE function to validate date value and sort the records 10-11

    Hi Friends, I am looking for some query which can give me the required output, I need to do this using SQL query only and I have tried using the MIN() and MAX() functions it was working fine with limited data, now after inserting the last record(in t

  • Decode function in Update statement 10-11

    Hello everyone, I'm trying to write a query where I can update a pastdue_fees column in a book_trans table based on a difference between return_dte and due_dte columns. I am using Oracle SQL. This is what I have so far for my decode function: SQL> SE

  • How to pass multiple query string values using the same parameter in Query String (URL) Filter Web Part 10-11

    Hi, I want to pass multiple query string values using the same parameter in Query String (URL) Filter Web Part like mentioned below: http://server/pages/Default.aspx?Title=Arup&Title=Ratan But it always return those items whose "Title" value

  • How to use a decode function 10-11

    I want to use the following with decode, recently started on BODS. Can you please provide the steps to use decode function in source tabke fieldname is ordid and in target fieldname is Orderid decode(Order_ID = 1709,lpad(Order_ID,7,'700'),Order_ID) t

  • Decode Function I asked this in the wrong section so I hope Im right now! 10-11

    select a11.SEQ_MEMB_ID, a11.Auth_Number, a11.Admit_Primary_Date, a11.Reviewer, Decode (a11.Reviewer, CLC, '2', HBR, '3', RAB, '4') a11.admit_primary_date from windsoradm.auth_master a11; I am trying to get if the Reviewer is CLC then they are code 2

  • Using mysql decode function in Java 10-11

    Hi everybody, mysql documentation says: DECODE(crypt_str,pass_str) --> Decrypts the encrypted string crypt_str using pass_str as the password. crypt_str should be a string returned from ENCODE(). I used the above function in a Python script and had n

  • Order by clause using decode function 10-11

    Hi everybody, i need below order in my report. Connecticut greenwich stamford bridgeport New York NYC wrestcher byram Georgia atlanta athens oconny first i need above order in my view out put. so in order by clause i used first decode function for St

  • Problem passing a table parameter in rfc call using the function control 10-11

    I try to pass a Table as parameter to a Function Module using the SAP.Functions library (part of SAP frontend controls) from a Visual Basic (VBA) program. The function module has been verified to work when invoked from SAP FrontEnd Function Builder.

  • HOW TO PASS A PARAMETER TO UIX PAGE FROM APPS' FUNCTIONS FORM? 10-11

    Hello, We created a form function (in Sys Admin resp) for a UIX page. The Type field is 'SSWA plsql function that opens a new window (Kiosk Mode)'. The HTML Call field is 'POBarCode.uix'. We have to pass the username (who logs in) to the UIX page. If

  • Logical Operations in SQL decode function ? 10-11

    Hi, Is it possible to do Logical Operations in SQL decode function like '>' '<' '>=' '<=' '<>' not in in not null is null eg... select col1 ,order_by,decode ( col1 , > 10 , 0 , 1) from tab; select col1 ,order_by,decode ( col1 , <&g

  • SQL Expression in decode function or case statement? 10-11

    Can I put SQL expressions in decode function or case statement? For example, select le.profile, decode( select count(1) from profile_data where NAME= 'XXXX_AFTER' and object_id = le.profile, 0, 'N', 'Y') from element le; or select le.profile, case WH

  • SQL DECODE function 10-11

    Oracle version: 9.2.0.1.0 Procob version: 9.2.0.1.0 Cobol version: 3.1.11 O.S. version: Windows XP We have a pro*cobol file with the next conversion rule into a select defined with a cursor. NVL(TO_CHAR(A.F_ALTA,'YYYYMMDD'),' '),      NVL(TO_CHAR(A.F

  • Problem using DECODE() function with a Query of Queries 10-11

    I posted on my blog about an issue I was having trying to use the PL/SQL DECODE() function with a Coldfusion Query of Queries. This function works fine when you query a database for information. However, when you query another query, it seems that CF

  • Using DECODE Function to change data 10-11

    I am trying to use the Decode function in a SQL statement to find a field that has a specific type, and when it finds that type, I want to blank out the results in a different field. For example: DECODE(ADDR_TYPE,'HOME',PHONE='') HOME_PHONEsomething