Results 1 to 6 of 6

Thread: Logic help

  1. #1
    Join Date
    Jan 2010
    Posts
    29

    Unanswered: Logic help

    Hello All,

    I am new to writing oracle sql query,please help me writing the below logic

    -------------------------
    The below logic is for RISK01_STREET(output colmn name)
    1. if POLDETL.PDADR1 and POLDETL.PDADR2 is null then load only .PDADR1(if both the fields are null load only PDADR1)
    2.if PDADR1 and PDADR2 is not null then load both PDADR1,PDADR2
    3. if PDADR1 is null and PDADR2 is not null then we need to load PDADR2
    Report the property street addresses for each property covered under this policy. Use the full address for example:
    123 Becker St, Suite 100 (use a comma as a delimiter)
    ------------------------------------
    The below logic is for RISK01_ZIP_CODE

    ZIP code of RISK01 property ‘ZZZZZ-PPPP’ where P = Plus 4 if available

    POLDETL.pdzip5,POLDETL.pdzip4(Table.colmn)

    return value if pdzip4is zero or null then return pdzip5 else reterun value is ZZZZZ-PPPP(Report the property ZIP CODE and Plus4 if available for each property covered under this policy. )
    -------------------------------------

    Please help me to write the logic


    Thanks in Advance!!

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    read up on the CASE statement.
    that should satisfy all your arguments.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Please help me to write the logic

    It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
    It would be helpful if you provided DML (INSERT INTO ...) for test data.
    It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  4. #4
    Join Date
    Jan 2010
    Posts
    29
    is the below way correct for case?

    CASE when PDADR1,PDADR2 is null then RISK01_STREET=PDADR1 end
    case when PDADR1,PDADR2 is not null then RISK01_STREET=PDADR1,PDADR2

    please guide me!!

  5. #5
    Join Date
    Jul 2003
    Posts
    2,296
    my guilde is google and my tool is search.
    first hit give a great example:

    Oracle/PLSQL: Case Statement

    Code:
    CASE
      WHEN owner='SYS' THEN 'The owner is SYS'
      WHEN owner='SYSTEM' THEN 'The owner is SYSTEM'
      ELSE 'The owner is another value'
    END
    in your "case" ...
    Code:
    CASE
      WHEN (PDADR1 is null and PDADR2 is null) THEN PDADR1
      WHEN (PDADR1 is null and PDADR2 not null) THEN PDADR2
      WHEN (PDADR1 is not null and PDADR2 is null) THEN PDADR1
      ELSE PDADR1||','||PDADR2
    END
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  6. #6
    Join Date
    Jan 2010
    Posts
    29
    Thanks Duck

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •