Include 1st query output in second query where condition.

[Niru]

Hello All,

I am pulling directly data from sql server. Here each table has millions of records. With query1 matching records i need to pull data from other tables. Is there any way i can pull only matching from other tables without using join ( if I use join it is taking more than 4 or 5 hours to pull the data from other tables).

In Alteryx query builder i have joined all the queries together i got  the unexpected results. now im unable to validate the data.

Is there any way I can include 1st query output in second query where condition. Please suggest me any other way.

kindly help me 

Thanks in Advance,

Niru

[danilang]

Hi @Niru 

Can you post the entire error message?  Right click on the line in the results and click Copy selected.

Dan

[Niru]

Hi @danilang, thank you so much for your reply

Below query I am able to execute from Sql server, I got the output and the same query when I am trying to run using Browse In-DB tool. i got the below error message.

Error: Browse In-DB (5): Error opening "WITH "Tool4_334c" AS (selecta.CLNT_DIM_ID,a.TOT_ACC_DIM_ID,a.clnt_acc_role_nm as RELATIONSHIP,a.manr_of_sign_cd as MANNER_OF_SIGNING,a.funct_lvl_cd as POWER,a.AUTH_CAT_CD as AUTHORITY_TYPE,a.AUTH_LMT_TYP_CD as AUTH_LIMIT,a.AUTH_LMT_AM as AUTH_LIMIT_AMT,a.PRIM_ROLE_IN,a.STM_DOC_E_DLV_IN,a.TAX_DOC_E_DLV_IN,a.WEB_REL_TYP_CD,a.WEB_DISP_PERMIT_CD,a.WEB_BAL_INQ_PERMIT_CD,a.WEB_STMT_PERMIT_CD,a.WEB_STOP_PAY_PERMIT_CD,a.WEB_TRNSF_IN_PERMIT_CD,a.WEB_TRNSF_OUT_PERMIT_CD,b.ACCT_23,c.CLNT_TAX_ID,c.ENTP_CLNT_SUB_TYP_NM,d.DM,d.CLNT_DIM_ID as CLNT_DIM_ID1,d.ECI,d.CAS_NAME,d.DM_NAMEFROM    [PBMIS_CA_DM_DLY].[dbo].[VW_CLNT_PRTY_TOT_ACC_BRDG]   aright join SCRATCH_DATA_OPS.[NAEAST\N369389].ACCOUNTREF_VIEW    bon           a.TOT_ACC_DIM_ID = b.TOT_ACC_DIM_IDand        a.CLNT_ACC_LST_IN = 'Y' join SCRATCH_DATA_OPS.[NAEAST\N369389].CLIENTREF_VIEW d on d.CLNT_DIM_ID = b.CLNT_DIM_ID left join [PBMIS_CA_DM_DLY].[dbo].[VW_CLNT_PRTY_DIM]     con           a.CLNT_DIM_ID = C.CLNT_DIM_IDwhere d.DM = '012713' order by 1) SELECT TOP 100 * FROM "Tool4_334c"": No Columns Returned.

Thanks & Regards

Niru

[danilang]

Hi @Niru 

What are  the Select statements in your Connect In-DB?

Dan

[Niru]

Hi @danilang,

I have only one select statement and I am combining multiple tables information. 

Thanks,

Niru

[danilang]

Did you type in the 'WITH "Tool4_334c" AS' yourself?  If so, remove it and 

use

select top 100 a.CLNT_DIM_ID,
a.TOT_ACC_DIM_ID,
a.clnt_acc_role_nm as RELATIONSHIP,
a.manr_of_sign_cd as MANNER_OF_SIGNING,
a.funct_lvl_cd as POWER,
a.AUTH_CAT_CD as AUTHORITY_TYPE,
a.AUTH_LMT_TYP_CD as AUTH_LIMIT,a.AUTH_LMT_AM as AUTH_LIMIT_AMT,
a.PRIM_ROLE_IN,
a.STM_DOC_E_DLV_IN,
a.TAX_DOC_E_DLV_IN,
a.WEB_REL_TYP_CD,
a.WEB_DISP_PERMIT_CD,
a.WEB_BAL_INQ_PERMIT_CD,
a.WEB_STMT_PERMIT_CD,
a.WEB_STOP_PAY_PERMIT_CD,
a.WEB_TRNSF_IN_PERMIT_CD,
a.WEB_TRNSF_OUT_PERMIT_CD,
b.ACCT_23,
c.CLNT_TAX_ID,
c.ENTP_CLNT_SUB_TYP_NM,
d.DM,d.CLNT_DIM_ID as CLNT_DIM_ID1,
d.ECI,
d.CAS_NAME,d.DM_NAME
FROM [PBMIS_CA_DM_DLY].[dbo].[VW_CLNT_PRTY_TOT_ACC_BRDG] a
right join SCRATCH_DATA_OPS.[NAEAST\N369389].ACCOUNTREF_VIEW b

  on a.TOT_ACC_DIM_ID = b.TOT_ACC_DIM_ID and a.CLNT_ACC_LST_IN = 'Y'
join SCRATCH_DATA_OPS.[NAEAST\N369389].CLIENTREF_VIEW d
  on d.CLNT_DIM_ID = b.CLNT_DIM_ID
left join [PBMIS_CA_DM_DLY].[dbo].[VW_CLNT_PRTY_DIM] c

  on a.CLNT_DIM_ID = C.CLNT_DIM_ID

where d.DM = '012713'

order by 1

Dan

[Niru]

Hi @danilang, sorry for one more doubt

when im using multiple select statements do i need to remove tool8 unknown words manually from the query. Please suggest, because im using multiple connect DB tools. 

Regards,

Niru 

[danilang]

Hi @Niru 

Because the With statements seem to be causing issues, you'll need to analyze your queries and reduce them to a series of Connect In-DB tools that all reference existing database tables.  Once you get these, you can use Join In-DB tool to build up the complex joins that your original queries represent.

With "TempA" as (Select A from Table1),

"TempB" as (Select B from Table2)

select "TempA" Join "TempB" on "TempA".A = "TempB".B

Becomes 

Select A from Table1 in one connect In-DB

Select B from Table2 in an second connect In-DB

Connect both of these to a Join In-DB with the key fields A=B

Of course this is a simple example and the initial query can just be replaced by a single In-DB connect with 

Select A from Table1 a1

Join Table2 b2  a1.A = b1.B

But for your more complex scenario, you'll probably want to use multiple Connect In-DB tools and multiple joins

Dan

[Niru]

Hi @danilang Thank you very much for your detailed explanation.

I am using single In-DB tools to connect each table, but still I got the same error when i am using browse tool. Any trouble shooting please. 

Regards,

Niru