LAE

Welcome to the LAE community!  Please feel free to start a discussion in the discussion tab or join in a conversation.

Discussions

Members

Resources

Events

 View Only

  • 1.  sql select statement

    Employee
    Posted 05-09-2017 09:05

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: mw9286

    OCI Error 1008: ORA-01008: not all variables bound

    sqlSelect(1,
    "{{^query^}}",
    EXTERNAL_ID.str(),SOC.str().trim())

    How do I write the sql select statement when I have multiple join sql statement?

    select sub.subscriber_no ctn,sa.soc FROM(select
    subscriber_no,
    row_number() over(partition by subscriber_no
    order by sub_status_date desc) seq
    from arkmasterown.subscriber_ns
    where subscriber_no = :1) sub
    left join
    (select
    subscriber_no,
    row_number() over(partition by subscriber_no
    order by sys_creation_date desc) seq
    from service_agreement
    where
    expiration_date is not null and
    subscriber_no = :1 and
    soc = :2    )sa
    on sub.subscriber_no = sa.subscriber_no and
    sa.seq = 1 and sub.seq = 1


  • 2.  RE: sql select statement

    Employee
    Posted 05-09-2017 09:36

    Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

    Originally posted by: stonysmith

    In my experience, you can't use a numbered parameter twice. I could be wrong. This should work:

    sqlSelect(1,
		"{{^query^}}",
		EXTERNAL_ID.str(),EXTERNAL_ID.str(),SOC.str().trim())
    select sub.subscriber_no ctn,sa.soc FROM(select 
subscriber_no,
row_number() over(partition by  subscriber_no order by sub_status_date desc) seq
from arkmasterown.subscriber_ns 
where  subscriber_no = :1) sub
left join
(select 
subscriber_no,
row_number() over(partition by subscriber_no
                        order by sys_creation_date desc) seq
from service_agreement 
  where
  expiration_date is not null and
subscriber_no = :2 and
soc = :3
) sa
on sub.subscriber_no = sa.subscriber_no and
sa.seq = 1 and sub.seq = 1