Problem SUB query with join with the table that are in the main query

Database
Enthusiast

Problem SUB query with join with the table that are in the main query

Hi

I must make a porting of a query in Oracle.

 

The subquery in the select have a join with the table that are in the main query.

TERADATA does not find the table.

Error: [3807] Object 'SSL_FI_IR_SWP'  does not exist.

 

How can I replace it in TERADATA?

 

Query:

SELECT DISTINCT
SSL_FI_RELATIONSHIPS.FIID,
SSL_FI_RELATIONSHIPS.SSL_FEID,
SSL_FI_RELATIONSHIPS.PHASE,
(SELECT MAX(B.STARTDATE) FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, SSL_FI_IR_SWP B WHERE
A.SSL_SNAPSHOT_DATE = 20160627
AND A.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND A.SSL_FEID='MX03'
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_FEID='MX03'
AND A.FIID=SSL_FI_RELATIONSHIPS.FIID
AND A.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND B.LEG=SSL_FI_IR_SWP.LEG
AND A.FI_ELEMENT=B.FIID
GROUP BY A.FIID) AS STARTDATE,
(SELECT MAX(B.MATURITY) FROM SSL_FI_RELATIONSHIPS A, SSL_FI_IR_SWP B WHERE
A.SSL_SNAPSHOT_DATE = 20160627
AND A.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND A.SSL_FEID='MX03'
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_FEID='MX03'
AND A.FIID=SSL_FI_RELATIONSHIPS.FIID
AND A.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND B.LEG=SSL_FI_IR_SWP.LEG
AND A.FI_ELEMENT=B.FIID
GROUP BY A.FIID) AS MATURITY
FROM
TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS SSL_FI_RELATIONSHIPS
INNER JOIN
TSSL10D.VW_MX_DB_SSL_FI_IR_SWP SSL_FI_IR_SWP
ON (SSL_FI_RELATIONSHIPS.FI_ELEMENT=SSL_FI_IR_SWP.FIID
AND SSL_FI_IR_SWP.SSL_SNAPSHOT_DATE = 20160627
AND SSL_FI_IR_SWP.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND SSL_FI_IR_SWP.SSL_FEID='MX03'
AND SSL_FI_IR_SWP.LEG IN (0,1))
LEFT OUTER JOIN
(SELECT MIN(A.FI_ELEMENT) AS FI_ELEMENT_MIN,
A.FIID,
A.SSL_FEID,
B.LEG,
B.F_INIT_EXCHANGE,
B.F_FINAL_EXCHANGE,
A.PHASE
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_CONFIGURATION B
WHERE A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
AND A.REF_DATE=B.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND B.REF_DATE = TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
GROUP BY A.FIID, A.SSL_FEID, B.LEG,B.F_INIT_EXCHANGE, B.F_FINAL_EXCHANGE, A.PHASE) CONFIG
ON (CONFIG.LEG=SSL_FI_IR_SWP.LEG
AND CONFIG.FIID=SSL_FI_RELATIONSHIPS.FIID
AND CONFIG.PHASE=SSL_FI_RELATIONSHIPS.PHASE)
LEFT OUTER JOIN
(SELECT MAX(D.STARTDATE) AS STARTDATE_MAX,
A.FIID,
A.SSL_FEID,
B.LEG,
B.INDEX_VALUE,
B.IDX_MARGIN,
B.RATEBASIS,
B.PAYMENT_FREQ,
A.PHASE
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_CONFIGURATION B, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP D
WHERE A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
AND A.FI_ELEMENT=D.FIID
AND A.SSL_FEID=D.SSL_FEID
AND B.LEG=D.LEG
AND B.REF_DATE = TO_DATE('20160627', 'yyyymmdd')
AND D.STARTDATE<=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
AND A.REF_DATE=B.REF_DATE
AND A.REF_DATE=D.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND A.SSL_SNAPSHOT_DATE=D.SSL_SNAPSHOT_DATE
GROUP BY A.FIID, A.SSL_FEID, B.LEG, B.INDEX_VALUE, B.IDX_MARGIN, B.RATEBASIS, B.PAYMENT_FREQ, A.PHASE) CONFIG2
ON (CONFIG2.LEG=SSL_FI_IR_SWP.LEG
AND CONFIG2.FIID=SSL_FI_RELATIONSHIPS.FIID
AND CONFIG2.PHASE=SSL_FI_RELATIONSHIPS.PHASE)
LEFT OUTER JOIN
(SELECT MAX(D.STARTDATE) AS STARTDATE_MAX,
A.FIID,
A.SSL_FEID,
B.LEG,
B.SCHED_EFFDMODE,
A.PHASE
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_CONFIG_SCHED B, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP D
WHERE A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
AND A.FI_ELEMENT=D.FIID
AND A.SSL_FEID=D.SSL_FEID
AND B.LEG=D.LEG
AND B.REF_DATE = TO_DATE('20160627', 'yyyymmdd')
AND D.STARTDATE<=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.DATE_TYPE='Calculation start schedule'
AND A.REF_DATE=B.REF_DATE
AND A.REF_DATE=D.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND A.SSL_SNAPSHOT_DATE=D.SSL_SNAPSHOT_DATE
GROUP BY A.FIID, A.SSL_FEID, B.LEG, B.SCHED_EFFDMODE, A.PHASE) CONFIG_SCHED
ON (CONFIG_SCHED.LEG=SSL_FI_IR_SWP.LEG
AND CONFIG_SCHED.FIID=SSL_FI_RELATIONSHIPS.FIID
AND CONFIG_SCHED.PHASE=SSL_FI_RELATIONSHIPS.PHASE)
LEFT OUTER JOIN
(SELECT MAX(B.STARTDATE) AS STARTDATE_MAX,
A.FIID,
A.PHASE,
B.LEG
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP B
WHERE B.STARTDATE<=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND A.REF_DATE=B.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
GROUP BY A.FIID, A.PHASE, B.LEG) IRSWP_CHECK
ON (IRSWP_CHECK.FIID=SSL_FI_RELATIONSHIPS.FIID
AND IRSWP_CHECK.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND IRSWP_CHECK.LEG=SSL_FI_IR_SWP.LEG)

WHERE
SSL_FI_RELATIONSHIPS.SSL_SNAPSHOT_DATE = 20160627
AND
SSL_FI_RELATIONSHIPS.REF_DATE = TO_DATE('20160627', 'yyyymmdd')

 

 

Thanks in advance for the support.

  • join
  • oracle
  • subquery
Tags (3)

Accepted Solutions
Junior Contributor

Re: Problem SUB query with join with the table that are in the main query

Well, your query should run as-is is Teradata, but it's quite inefficient.

This is probably the same using OLAP

SELECT DISTINCT
   Sum(CASE WHEN SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM = 'CouponNotionalB' AND SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM_VAL <> '0' THEN 1 ELSE 0 end) 
   Over (PARTITION BY FIID) AS CONTA_EMBED_PARAM, 
   Max(CASE CASE WHEN SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM IN ('ImportExport','ExportImport') THEN OPT_PARAM_VAL end)
   Over (PARTITION BY FIID) AS IMPORT_EXPORT,
   SSL_FI_PARAM.FIID,
   SSL_FI_PARAM.SSL_FEID
FROM TSSL10D.VW_MX_DB_SSL_FI_EMBDDD_PARAM SSL_FI_PARAM
WHERE
   SSL_FI_PARAM.SSL_SNAPSHOT_DATE = 20160627

 

1 ACCEPTED SOLUTION
10 REPLIES
rjg
Supporter

Re: Problem SUB query with join with the table that are in the main query

This is a bit of a guess since I don't have the tables to investigate where in the query the error is actually coming from.

SyntaxEditor Code Snippet

 

this table is aliased as SSL_FI_IR_SWP

TSSL10D.VW_MX_DB_SSL_FI_IR_SWP

 SSL_FI_IR_SWP is aliased as B in both scalar subqueries.

So  SSL10D.VW_MX_DB_SSL_FI_IR_SWP is the table not found. 

Can you check this?

 

Rglass

Enthusiast

Re: Problem SUB query with join with the table that are in the main query

Hi

 

Sorry.

In  the subquery

'SSL_FI_IR_SWP B' is  'TSSL10D.VW_MX_DB_SSL_FI_IR_SWP B'

 

The problem is in the where of subquery where there are

'AND A.FIID=SSL_FI_RELATIONSHIPS.FIID
AND A.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND B.LEG=SSL_FI_IR_SWP.LEG'

 

SSL_FI_RELATIONSHIPS and SSL_FI_IR_SWP are alias of table there are in the main query and not in the subquery.

 

Query correct:

SELECT DISTINCT
SSL_FI_RELATIONSHIPS.FIID,
SSL_FI_RELATIONSHIPS.SSL_FEID,
SSL_FI_RELATIONSHIPS.PHASE,
(SELECT MAX(B.STARTDATE) FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP B WHERE
A.SSL_SNAPSHOT_DATE = 20160627
AND A.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND A.SSL_FEID='MX03'
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_FEID='MX03'
AND A.FIID=SSL_FI_RELATIONSHIPS.FIID
AND A.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND B.LEG=SSL_FI_IR_SWP.LEG
AND A.FI_ELEMENT=B.FIID
GROUP BY A.FIID) AS STARTDATE,
(SELECT MAX(B.MATURITY) FROM SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP B WHERE
A.SSL_SNAPSHOT_DATE = 20160627
AND A.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND A.SSL_FEID='MX03'
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_FEID='MX03'
AND A.FIID=SSL_FI_RELATIONSHIPS.FIID
AND A.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND B.LEG=SSL_FI_IR_SWP.LEG
AND A.FI_ELEMENT=B.FIID
GROUP BY A.FIID) AS MATURITY
FROM
TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS SSL_FI_RELATIONSHIPS
INNER JOIN
TSSL10D.VW_MX_DB_SSL_FI_IR_SWP SSL_FI_IR_SWP
ON (SSL_FI_RELATIONSHIPS.FI_ELEMENT=SSL_FI_IR_SWP.FIID
AND SSL_FI_IR_SWP.SSL_SNAPSHOT_DATE = 20160627
AND SSL_FI_IR_SWP.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND SSL_FI_IR_SWP.SSL_FEID='MX03'
AND SSL_FI_IR_SWP.LEG IN (0,1))
LEFT OUTER JOIN
(SELECT MIN(A.FI_ELEMENT) AS FI_ELEMENT_MIN,
A.FIID,
A.SSL_FEID,
B.LEG,
B.F_INIT_EXCHANGE,
B.F_FINAL_EXCHANGE,
A.PHASE
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_CONFIGURATION B
WHERE A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
AND A.REF_DATE=B.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND B.REF_DATE = TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
GROUP BY A.FIID, A.SSL_FEID, B.LEG,B.F_INIT_EXCHANGE, B.F_FINAL_EXCHANGE, A.PHASE) CONFIG
ON (CONFIG.LEG=SSL_FI_IR_SWP.LEG
AND CONFIG.FIID=SSL_FI_RELATIONSHIPS.FIID
AND CONFIG.PHASE=SSL_FI_RELATIONSHIPS.PHASE)
LEFT OUTER JOIN
(SELECT MAX(D.STARTDATE) AS STARTDATE_MAX,
A.FIID,
A.SSL_FEID,
B.LEG,
B.INDEX_VALUE,
B.IDX_MARGIN,
B.RATEBASIS,
B.PAYMENT_FREQ,
A.PHASE
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_CONFIGURATION B, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP D
WHERE A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
AND A.FI_ELEMENT=D.FIID
AND A.SSL_FEID=D.SSL_FEID
AND B.LEG=D.LEG
AND B.REF_DATE = TO_DATE('20160627', 'yyyymmdd')
AND D.STARTDATE<=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
AND A.REF_DATE=B.REF_DATE
AND A.REF_DATE=D.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND A.SSL_SNAPSHOT_DATE=D.SSL_SNAPSHOT_DATE
GROUP BY A.FIID, A.SSL_FEID, B.LEG, B.INDEX_VALUE, B.IDX_MARGIN, B.RATEBASIS, B.PAYMENT_FREQ, A.PHASE) CONFIG2
ON (CONFIG2.LEG=SSL_FI_IR_SWP.LEG
AND CONFIG2.FIID=SSL_FI_RELATIONSHIPS.FIID
AND CONFIG2.PHASE=SSL_FI_RELATIONSHIPS.PHASE)
LEFT OUTER JOIN
(SELECT MAX(D.STARTDATE) AS STARTDATE_MAX,
A.FIID,
A.SSL_FEID,
B.LEG,
B.SCHED_EFFDMODE,
A.PHASE
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_CONFIG_SCHED B, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP D
WHERE A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
AND A.FI_ELEMENT=D.FIID
AND A.SSL_FEID=D.SSL_FEID
AND B.LEG=D.LEG
AND B.REF_DATE = TO_DATE('20160627', 'yyyymmdd')
AND D.STARTDATE<=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.DATE_TYPE='Calculation start schedule'
AND A.REF_DATE=B.REF_DATE
AND A.REF_DATE=D.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND A.SSL_SNAPSHOT_DATE=D.SSL_SNAPSHOT_DATE
GROUP BY A.FIID, A.SSL_FEID, B.LEG, B.SCHED_EFFDMODE, A.PHASE) CONFIG_SCHED
ON (CONFIG_SCHED.LEG=SSL_FI_IR_SWP.LEG
AND CONFIG_SCHED.FIID=SSL_FI_RELATIONSHIPS.FIID
AND CONFIG_SCHED.PHASE=SSL_FI_RELATIONSHIPS.PHASE)
LEFT OUTER JOIN
(SELECT MAX(B.STARTDATE) AS STARTDATE_MAX,
A.FIID,
A.PHASE,
B.LEG
FROM TSSL10D.VW_MX_DB_SSL_FI_RELATIONSHIPS A, TSSL10D.VW_MX_DB_SSL_FI_IR_SWP B
WHERE B.STARTDATE<=TO_DATE('20160627', 'yyyymmdd')
AND B.SSL_SNAPSHOT_DATE = 20160627
AND B.REF_DATE=TO_DATE('20160627', 'yyyymmdd')
AND A.REF_DATE=B.REF_DATE
AND A.SSL_SNAPSHOT_DATE=B.SSL_SNAPSHOT_DATE
AND A.FI_ELEMENT=B.FIID
AND A.SSL_FEID=B.SSL_FEID
GROUP BY A.FIID, A.PHASE, B.LEG) IRSWP_CHECK
ON (IRSWP_CHECK.FIID=SSL_FI_RELATIONSHIPS.FIID
AND IRSWP_CHECK.PHASE=SSL_FI_RELATIONSHIPS.PHASE
AND IRSWP_CHECK.LEG=SSL_FI_IR_SWP.LEG)

WHERE
SSL_FI_RELATIONSHIPS.SSL_SNAPSHOT_DATE = 20160627
AND
SSL_FI_RELATIONSHIPS.REF_DATE = TO_DATE('20160627', 'yyyymmdd')

Enthusiast

Re: Problem SUB query with join with the table that are in the main query

Hi

 

I found this possible solution

Query oracle:

SELECT
(SELECT COUNT(*) FROM VW_MX_DB_SSL_FI_EMBDDD_FLOW , VW_MX_DB_SSL_FI_EMBDDD_FLOW SSL_FI_FLOW ON SSL_FI_EMBEDDEDOPT_FLOW.FIID=SSL_FI_FLOW.FIID WHERE SSL_FI_EMBEDDEDOPT_FLOW.SSL_SNAPSHOT_DATE = #TODAY# AND SSL_FI_FLOW.SSL_SNAPSHOT_DATE = #TODAY#) AS CONTA_EMBED_FLOW,
SSL_FI_FLOW.WEIGHT,
SSL_FI_FLOW.EXERCISE_DATE,
SSL_FI_FLOW.OPTID,
SSL_FI_FLOW.FIID
FROM
VW_MX_DB_SSL_FI_EMBDDD_FLOW SSL_FI_FLOW
WHERE
SSL_FI_FLOW.SSL_SNAPSHOT_DATE = #TODAY#

 

Query Teradata:

with temp as (
SELECT COUNT(*) n , SSL_FI_EMBEDDEDOPT_FLOW.FIID
FROM VW_MX_DB_SSL_FI_EMBDDD_FLOW SSL_FI_EMBEDDEDOPT_FLOW
WHERE SSL_FI_EMBEDDEDOPT_FLOW.SSL_SNAPSHOT_DATE = #TODAY#
group by SSL_FI_EMBEDDEDOPT_FLOW.FIID
)

SELECT
temp.n AS CONTA_EMBED_FLOW,
SSL_FI_FLOW.WEIGHT,
SSL_FI_FLOW.EXERCISE_DATE,
SSL_FI_FLOW.OPTID,
SSL_FI_FLOW.FIID
FROM
VW_MX_DB_SSL_FI_EMBDDD_FLOW SSL_FI_FLOW , temp
where temp.FIID=SSL_FI_FLOW.FIID
and
SSL_FI_FLOW.SSL_SNAPSHOT_DATE = #TODAY#
ORDER BY SSL_FI_FLOW.FiID,
SSL_FI_FLOW.EXERCISE_DATE

 


but isn't there a simpler solution?

 

Thanks in advance for the support.

Junior Contributor

Re: Problem SUB query with join with the table that are in the main query

The Query Correct in your 2nd post is valid syntax.

Query Oracle in the last post is not valid syntax and if you add an alias and JOIN it's valid in Teradata, too, but if you actually join two tables it's not the same as Query Teradata. (Btw, Teradata query will probably be more efficient in Oracle, too).

 

I don't get what you really trying to do, I guess you simply need a Windowed Aggregate, i.e. MAX/COUNT() OVER (PARTITON BY ...)

Enthusiast

Re: Problem SUB query with join with the table that are in the main query

Hi

 

I have this query in Oracle: 

 

SELECT DISTINCT
(SELECT COUNT(*) FROM TSSL10D.VW_MX_DB_SSL_FI_EMBDDD_PARAM
WHERE SSL_FI_EMBEDDEDOPT_PARAM.SSL_SNAPSHOT_DATE = 20160627
AND SSL_FI_EMBEDDEDOPT_PARAM.FIID=SSL_FI_PARAM.FIID
AND SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM = 'CouponNotionalB'
AND SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM_VAL <> '0') AS CONTA_EMBED_PARAM,
(SELECT DISTINCT MAX(OPT_PARAM_VAL) FROM TSSL10D.VW_MX_DB_SSL_FI_EMBDDD_PARAM
WHERE SSL_FI_EMBEDDEDOPT_PARAM.SSL_SNAPSHOT_DATE = 20160627
AND SSL_FI_EMBEDDEDOPT_PARAM.FIID=SSL_FI_PARAM.FIID
AND SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM in ('ImportExport','ExportImport')) AS IMPORT_EXPORT,
SSL_FI_PARAM.FIID,
SSL_FI_PARAM.SSL_FEID
FROM
TSSL10D.VW_MX_DB_SSL_FI_EMBDDD_PARAM SSL_FI_PARAM
WHERE
SSL_FI_PARAM.SSL_SNAPSHOT_DATE = 20160627

 

 

In this query, how can I use over partiton for subquery with max in the select ?

I can only use the with temp as () ?

 

thank you

 

Junior Contributor

Re: Problem SUB query with join with the table that are in the main query

Well, your query should run as-is is Teradata, but it's quite inefficient.

This is probably the same using OLAP

SELECT DISTINCT
   Sum(CASE WHEN SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM = 'CouponNotionalB' AND SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM_VAL <> '0' THEN 1 ELSE 0 end) 
   Over (PARTITION BY FIID) AS CONTA_EMBED_PARAM, 
   Max(CASE CASE WHEN SSL_FI_EMBEDDEDOPT_PARAM.OPT_PARAM IN ('ImportExport','ExportImport') THEN OPT_PARAM_VAL end)
   Over (PARTITION BY FIID) AS IMPORT_EXPORT,
   SSL_FI_PARAM.FIID,
   SSL_FI_PARAM.SSL_FEID
FROM TSSL10D.VW_MX_DB_SSL_FI_EMBDDD_PARAM SSL_FI_PARAM
WHERE
   SSL_FI_PARAM.SSL_SNAPSHOT_DATE = 20160627

 

Enthusiast

Re: Problem SUB query with join with the table that are in the main query

Hi

 

I have a similar problem, but with ROWNUM in a subquery.

 

This is the oracle's query:

select FROM
(SELECT DISTINCT
SSL_DEAL.DEALID,
SSL_DEAL.SSL_FEID,
row_number() over (partition by SSL_DEAL.DEALID order by SSL_DEAL_CASHFLOW.EFFECTIVE_DATE asc) NUM_IN_GROUP,
(SELECT SSL_DEAL_CASHFLOW.CASHFLOW_WDAYS AS CF_WDAYS_START FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW WHERE (SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20160103 AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20160103', 'YYYYMMDD') AND SSL_DEAL_CASHFLOW.DEALID = SSL_DEAL.DEALID AND SSL_DEAL_CASHFLOW.SSL_FEID = 'MX03' AND SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE=TAB1.ACCRUAL_START_DATE_MIN) AND ROWNUM=1) AS CASHFLOW_WDAYS_START,
FROM
VW_MX_DB_SSL_DEAL SSL_DEAL
INNER JOIN
VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW
ON (SSL_DEAL_CASHFLOW.SSL_FEID = SSL_DEAL.SSL_FEID
AND SSL_DEAL_CASHFLOW.DEALID = SSL_DEAL.DEALID
AND SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE=SSL_DEAL.SSL_SNAPSHOT_DATE
AND SSL_DEAL_CASHFLOW.REF_DATE=SSL_DEAL.REF_DATE)
LEFT OUTER JOIN
(SELECT SSL_DEAL_CASHFLOW.DEALID,
MIN(SSL_DEAL_CASHFLOW.EFFECTIVE_DATE) AS EFFECTIVE_DATE_MIN
FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW
WHERE (SSL_DEAL_CASHFLOW.EFFECTIVE_DATE >= TO_DATE('20161003', 'YYYYMMDD')
AND SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20161003
AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20161003', 'YYYYMMDD')
AND SSL_DEAL_CASHFLOW.SSL_FEID='MX03'
AND SSL_DEAL_CASHFLOW.TYPE0 = 'CAP'
AND SSL_DEAL_CASHFLOW.TYPE1 = 'IPAY'
AND SSL_DEAL_CASHFLOW.TYPE2 = ' '
AND SSL_DEAL_CASHFLOW.TYPE3 = ' '
AND SSL_DEAL_CASHFLOW.TYPE4 = 'INT')
GROUP BY SSL_DEAL_CASHFLOW.DEALID) CASHFLOW_MIN
ON (SSL_DEAL_CASHFLOW.DEALID = CASHFLOW_MIN.DEALID)
LEFT OUTER JOIN
(SELECT SSL_DEAL_CASHFLOW.DEALID,
MAX(SSL_DEAL_CASHFLOW.EFFECTIVE_DATE) AS EFFECTIVE_DATE_MAX_PAST
FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW
WHERE (SSL_DEAL_CASHFLOW.EFFECTIVE_DATE < TO_DATE('20161003', 'YYYYMMDD')
AND SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20161003
AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20161003', 'YYYYMMDD')
AND SSL_DEAL_CASHFLOW.SSL_FEID='MX03'
AND SSL_DEAL_CASHFLOW.TYPE0 = 'CAP'
AND SSL_DEAL_CASHFLOW.TYPE1 = 'IPAY'
AND SSL_DEAL_CASHFLOW.TYPE2 = ' '
AND SSL_DEAL_CASHFLOW.TYPE3 = ' '
AND SSL_DEAL_CASHFLOW.TYPE4 = 'INT')
GROUP BY SSL_DEAL_CASHFLOW.DEALID) CASHFLOW_MAX_PAST
ON (SSL_DEAL_CASHFLOW.DEALID = CASHFLOW_MAX_PAST.DEALID)
INNER JOIN
VW_MX_DB_SSL_FI SSL_FI
ON (SSL_DEAL.SSL_FEID = SSL_FI.SSL_FEID
AND SSL_DEAL.FIID = SSL_FI.FIID
AND SSL_DEAL.SSL_SNAPSHOT_DATE=SSL_FI.SSL_SNAPSHOT_DATE
AND SSL_DEAL.REF_DATE=SSL_FI.REF_DATE)
LEFT OUTER JOIN
(SELECT MIN(SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE) AS ACCRUAL_START_DATE_MIN,
SSL_DEAL_CASHFLOW.DEALID
FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW
WHERE (SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE > TO_DATE('20161003', 'YYYYMMDD') )
GROUP BY SSL_DEAL_CASHFLOW.DEALID) TAB1
ON (SSL_DEAL.DEALID = TAB1.DEALID)
WHERE
SSL_DEAL.SSL_SNAPSHOT_DATE = 20161003
AND
SSL_DEAL.REF_DATE = TO_DATE('20161003', 'YYYYMMDD')
AND
SSL_DEAL_CASHFLOW.LEG=0
AND
SSL_DEAL.SSL_FEID='MX03'
AND
(((SSL_FI.MFAMILY='IRD' AND SSL_FI.MGROUP='OPT' AND SSL_FI.MTYPE='OTC') OR
(SSL_FI.MFAMILY='IRD' AND SSL_FI.MGROUP='WARNT') OR
(SSL_FI.MFAMILY='EQD' AND SSL_FI.MGROUP='EQUIT' AND SSL_FI.MTYPE='FWD') OR
(SSL_FI.MFAMILY='EQD' AND SSL_FI.MGROUP='OPT' AND (SSL_FI.MTYPE='OTC' OR SSL_FI.MTYPE='BAR' OR SSL_FI.MTYPE='ASI' OR SSL_FI.MTYPE='RAT'))) OR
(SSL_FI.MFAMILY='COM' AND SSL_FI.MGROUP='ASIAN') OR
(SSL_FI.MFAMILY='CURR' AND SSL_FI.MGROUP='OPT' AND (SSL_FI.MTYPE IS NULL OR SSL_FI.MTYPE<>'LST')) OR
(SSL_FI.MFAMILY='EQD' AND SSL_FI.MGROUP='WARNT') OR
(SSL_FI.MFAMILY='COM' AND SSL_FI.MGROUP='OPT' AND SSL_FI.MTYPE='SMP') OR
(SSL_FI.MFAMILY='COM' AND SSL_FI.MGROUP='OPT' AND SSL_FI.MTYPE='SWAP'))
AND SSL_DEAL.STATUS<>'DEAD'
AND SSL_FI.INSTRUMENT_FE<>'SPALMA FEES'
AND SSL_DEAL_CASHFLOW.TYPE0 = 'CAP'
AND SSL_DEAL_CASHFLOW.TYPE1 = 'IPAY'
AND SSL_DEAL_CASHFLOW.TYPE2 = ' '
AND SSL_DEAL_CASHFLOW.TYPE3 = ' '
AND SSL_DEAL_CASHFLOW.TYPE4 = 'INT' ) TABELLABASE
WHERE TABELLABASE.NUM_IN_GROUP=1

 

 

I have replaced the part in red with:

( select * from (SELECT SSL_DEAL_CASHFLOW.CASHFLOW_WDAYS AS CF_WDAYS_START
FROM TSSL10D.VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW WHERE (SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20160103
AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20160103', 'YYYYMMDD')
AND SSL_DEAL_CASHFLOW.DEALID = SSL_DEAL.DEALID AND SSL_DEAL_CASHFLOW.SSL_FEID = 'MX03'
AND SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE=TAB1.ACCRUAL_START_DATE_MIN) sample 1 ) temp )
AS CASHFLOW_WDAYS_START

 

but i have the following error: [3807] Object 'SSL_DEAL' does not exist. 

It does not seem to find the table in the main query.

 

How can I fix the problem?
Is there another method to replace the rownum?

 

Thanks in advance for the support.

Teradata Employee

Re: Problem SUB query with join with the table that are in the main query

How about a different approach that may work:

select * FROM
(SELECT DISTINCT
SSL_DEAL.DEALID,
SSL_DEAL.SSL_FEID,
row_number() over (partition by SSL_DEAL.DEALID order by SSL_DEAL_CASHFLOW.EFFECTIVE_DATE asc) NUM_IN_GROUP,

WDAYS.CASHFLOW_WDAYS_START

FROM
VW_MX_DB_SSL_DEAL SSL_DEAL

CROSS JOIN
(SELECT SSL_DEAL_CASHFLOW.CASHFLOW_WDAYS AS CF_WDAYS_START FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW WHERE (SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20160103 AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20160103', 'YYYYMMDD') AND SSL_DEAL_CASHFLOW.DEALID = SSL_DEAL.DEALID AND SSL_DEAL_CASHFLOW.SSL_FEID = 'MX03' AND SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE=TAB1.ACCRUAL_START_DATE_MIN) AND ROWNUM=1) WDAYS(CASHFLOW_WDAYS_START)

INNER JOIN
...
... ) TABELLABASE
WHERE TABELLABASE.NUM_IN_GROUP=1

Enthusiast

Re: Problem SUB query with join with the table that are in the main query

Hi

 

CROSS JOIN
(SELECT SSL_DEAL_CASHFLOW.CASHFLOW_WDAYS AS CF_WDAYS_START FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW WHERE (SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20160103 AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20160103', 'YYYYMMDD') AND SSL_DEAL_CASHFLOW.DEALID = SSL_DEAL.DEALID AND SSL_DEAL_CASHFLOW.SSL_FEID = 'MX03' AND SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE=TAB1.ACCRUAL_START_DATE_MIN) AND ROWNUM=1) WDAYS(CASHFLOW_WDAYS_START)

is not Teradata's syntax.

 

Teradata generates an error because don't found SSL_DEAL in SSL_DEAL_CASHFLOW.DEALID = SSL_DEAL.DEALID or TAB1 in SSL_DEAL_CASHFLOW.ACCRUAL_START_DATE=TAB1.ACCRUAL_START_DATE_MIN

 

However, if i use:

inner join (
SELECT SSL_DEAL_CASHFLOW.CASHFLOW_WDAYS AS CF_WDAYS_START , SSL_DEAL_CASHFLOW , SSL_DEAL_CASHFLOW.
FROM VW_MX_DB_SSL_DEAL_CASHFLOW SSL_DEAL_CASHFLOW WHERE (SSL_DEAL_CASHFLOW.SSL_SNAPSHOT_DATE = 20160103 AND SSL_DEAL_CASHFLOW.REF_DATE = TO_DATE('20160103', 'YYYYMMDD') AND SSL_DEAL_CASHFLOW.SSL_FEID = 'MX03' )) WDAYS on WDAYS.DEALID = SSL_DEAL.DEALID AND WDAYS.ACCRUAL_START_DATE=TAB1.ACCRUAL_START_DATE_MIN AND ROWNUM=1

Teradata found the table, but the problem is ROWNUM ( SAMPLE in Teradata )

 

 

 Thanks in advance for the support.