Report with Query and data related to query response.

[WireGuyJCB]

First time question. Here is the situation. I have a query which pulls related data from three separate tables. There are six fields in the query results which may contain pointers to a fourth table. I only want to retrieve the data from the fourth table, if there the query returns data in the pointer field. If there are all six pointers then I will need to retrive six different records from the table. Can this be done with a subquery? Multiple subquerys? I would be happy for any recommendations.

Thanks

 

[scriverb]

Yes this can be done by using your existing query as input to another query and linking the fields to your secondary table. Post your current query SQL here along with the fields from the four table and I will see what we can do. Identify the link fields in the first query and what they should match up with in the fourth table.

Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[WireGuyJCB]

I really appreciate the help. Here is the current SQL.

SELECT DB2OPER_FT_ACCOUNT.BANK_ID, DB2OPER_FT_ACCOUNT.ID_TYPE, DB2OPER_FT_ACCOUNT.ACCOUNT, DB2OPER_FT_ACCOUNT.ACC_NAME, DB2OPER_FT_ACCOUNT.REL_STATUS_FLG, DB2OPER_FT_REPETITIVE.BANK, DB2OPER_FT_REPETITIVE.TYPE, DB2OPER_FT_REPETITIVE.KEY_ACC, DB2OPER_FT_REPETITIVE.REPETITIVE_ID, DB2OPER_FT_REPETITIVE.OWN_BANK, DB2OPER_FT_REPETITIVE.RPT_STATUS_FLG, DB2OPER_FT_REPETITIVE.OWN_LOC, DB2OPER_FT_REPETITIVE.SOURCE_CD, DB2OPER_FT_REPETITIVE.TYPE_CD, DB2OPER_FT_REPETITIVE.TRAN_TYPE, DB2OPER_FT_REPETITIVE.INSTR_ADV_TYPE, DB2OPER_FT_REPETITIVE.CURRENCY_CODE, DB2OPER_FT_REPETITIVE.DBT_CHRG, DB2OPER_FT_REPETITIVE.CDT_CHRG, DB2OPER_FT_REPETITIVE.COMMISSION, DB2OPER_FT_REPETITIVE.CBL_CHARGE, DB2OPER_FT_REPETITIVE_CR.CDT_ADV_TYPE, DB2OPER_FT_REPETITIVE_DR.DBT_NAME1, DB2OPER_FT_REPETITIVE_DR.DBT_NAME2, DB2OPER_FT_REPETITIVE_DR.DBT_NAME3, DB2OPER_FT_REPETITIVE_DR.DBT_NAME4, DB2OPER_FT_REPETITIVE_DR.DBT_RECON_REF, DB2OPER_FT_REPETITIVE_DR.SPC_INST1, DB2OPER_FT_REPETITIVE_DR.SPC_INST2, DB2OPER_FT_REPETITIVE_DR.SPC_INST3, DB2OPER_FT_REPETITIVE_DR.SBK_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_DR.SBK_REL_ID, DB2OPER_FT_REPETITIVE_DR.SBK_IDTYPE, DB2OPER_FT_REPETITIVE_DR.SBK_ID, DB2OPER_FT_REPETITIVE_DR.SBK_NAME1, DB2OPER_FT_REPETITIVE_DR.SBK_NAME2, DB2OPER_FT_REPETITIVE_DR.SBK_NAME3, DB2OPER_FT_REPETITIVE_DR.SBK_NAME4, DB2OPER_FT_REPETITIVE_DR.SBK_REF_NUM, DB2OPER_FT_REPETITIVE_DR.OBK_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_DR.OBK_REL_ID, DB2OPER_FT_REPETITIVE_DR.OBK_IDTYPE, DB2OPER_FT_REPETITIVE_DR.OBK_ID, DB2OPER_FT_REPETITIVE_DR.OBK_NAME1, DB2OPER_FT_REPETITIVE_DR.OBK_NAME2, DB2OPER_FT_REPETITIVE_DR.OBK_NAME3, DB2OPER_FT_REPETITIVE_DR.OBK_NAME4, DB2OPER_FT_REPETITIVE_DR.OBK_REF_NUM, DB2OPER_FT_REPETITIVE_DR.ORP_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_DR.ORP_REL_ID, DB2OPER_FT_REPETITIVE_DR.ORP_IDTYPE, DB2OPER_FT_REPETITIVE_DR.ORP_ID, DB2OPER_FT_REPETITIVE_DR.ORP_NAME1, DB2OPER_FT_REPETITIVE_DR.ORP_NAME2, DB2OPER_FT_REPETITIVE_DR.ORP_NAME3, DB2OPER_FT_REPETITIVE_DR.ORP_NAME4, DB2OPER_FT_REPETITIVE_DR.ORP_REF_NUM, DB2OPER_FT_REPETITIVE_DR.DBT_BNK_INF1, DB2OPER_FT_REPETITIVE_DR.DBT_BNK_INF2, DB2OPER_FT_REPETITIVE_DR.DBT_BNK_INF3, DB2OPER_FT_REPETITIVE_DR.DBT_BNK_INF4, DB2OPER_FT_REPETITIVE_DR.DBT_BNK_INF5, DB2OPER_FT_REPETITIVE_DR.DBT_BNK_INF6, DB2OPER_FT_REPETITIVE_CR.CDT_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_CR.CDT_REL_ID, DB2OPER_FT_REPETITIVE_CR.BANK_ID, DB2OPER_FT_REPETITIVE_CR.ID_TYPE, DB2OPER_FT_REPETITIVE_CR.ACCOUNT, DB2OPER_FT_REPETITIVE_CR.ADV_TYPE, DB2OPER_FT_REPETITIVE_CR.CDT_NAME1, DB2OPER_FT_REPETITIVE_CR.CDT_NAME2, DB2OPER_FT_REPETITIVE_CR.CDT_NAME3, DB2OPER_FT_REPETITIVE_CR.CDT_ADV_INST1, DB2OPER_FT_REPETITIVE_CR.CDT_ADV_INST2, DB2OPER_FT_REPETITIVE_CR.CDT_ADV_INST3, DB2OPER_FT_REPETITIVE_CR.CDT_RECON_REF, DB2OPER_FT_REPETITIVE_CR.CDT_SPC_INST1, DB2OPER_FT_REPETITIVE_CR.CDT_SPC_INST2, DB2OPER_FT_REPETITIVE_CR.CDT_SPC_INST3, DB2OPER_FT_REPETITIVE_CR.IB1_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_CR.IB1_REL_ID, DB2OPER_FT_REPETITIVE_CR.IB1_IDTYPE, DB2OPER_FT_REPETITIVE_CR.IB1_ID, DB2OPER_FT_REPETITIVE_CR.IB1_NAME1, DB2OPER_FT_REPETITIVE_CR.IB1_NAME2, DB2OPER_FT_REPETITIVE_CR.IB1_NAME3, DB2OPER_FT_REPETITIVE_CR.IB1_NAME4, DB2OPER_FT_REPETITIVE_CR.IB1_ADV_INST1, DB2OPER_FT_REPETITIVE_CR.IB1_ADV_INST2, DB2OPER_FT_REPETITIVE_CR.IB1_ADV_INST3, DB2OPER_FT_REPETITIVE_CR.IBK_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_CR.IBK_REL_ID, DB2OPER_FT_REPETITIVE_CR.IBK_IDTYPE, DB2OPER_FT_REPETITIVE_CR.IBK_ID, DB2OPER_FT_REPETITIVE_CR.IBK_NAME1, DB2OPER_FT_REPETITIVE_CR.IBK_NAME2, DB2OPER_FT_REPETITIVE_CR.IBK_NAME3, DB2OPER_FT_REPETITIVE_CR.IBK_NAME4, DB2OPER_FT_REPETITIVE_CR.IBK_ADV_INST1, DB2OPER_FT_REPETITIVE_CR.IBK_ADV_INST2, DB2OPER_FT_REPETITIVE_CR.IBK_ADV_INST3, DB2OPER_FT_REPETITIVE_CR.BBK_ADR_BNK_ID, DB2OPER_FT_REPETITIVE_CR.BBK_REL_ID, DB2OPER_FT_REPETITIVE_CR.BBK_SECWIR, DB2OPER_FT_REPETITIVE_CR.BBK_IDTYPE, DB2OPER_FT_REPETITIVE_CR.BBK_ID, DB2OPER_FT_REPETITIVE_CR.BBK_ID_OVERFLOW, DB2OPER_FT_REPETITIVE_CR.BBK_NAME1, DB2OPER_FT_REPETITIVE_CR.BBK_NAME2, DB2OPER_FT_REPETITIVE_CR.BBK_NAME3, DB2OPER_FT_REPETITIVE_CR.BBK_NAME4, DB2OPER_FT_REPETITIVE_CR.BBK_ADV_INST1, DB2OPER_FT_REPETITIVE_CR.BBK_ADV_INST2, DB2OPER_FT_REPETITIVE_CR.BBK_ADV_INST3, DB2OPER_FT_REPETITIVE_CR.BNP_CHARGE_FLG, DB2OPER_FT_REPETITIVE_CR.BNP_IDTYPE, DB2OPER_FT_REPETITIVE_CR.BNP_ID, DB2OPER_FT_REPETITIVE_CR.BNP_ID_OVERFLOW, DB2OPER_FT_REPETITIVE_CR.BNP_BNK_FLG, DB2OPER_FT_REPETITIVE_CR.BNP_NAME1, DB2OPER_FT_REPETITIVE_CR.BNP_NAME2, DB2OPER_FT_REPETITIVE_CR.BNP_NAME3, DB2OPER_FT_REPETITIVE_CR.BNP_NAME4, DB2OPER_FT_REPETITIVE_CR.BNP_ADV_INST1, DB2OPER_FT_REPETITIVE_CR.BNP_ADV_INST2, DB2OPER_FT_REPETITIVE_CR.BNP_ADV_INST3, DB2OPER_FT_REPETITIVE_CR.ORP_BEN_INF1, DB2OPER_FT_REPETITIVE_CR.ORP_BEN_INF2, DB2OPER_FT_REPETITIVE_CR.ORP_BEN_INF3, DB2OPER_FT_REPETITIVE_CR.ORP_BEN_INF4
FROM DB2OPER_FT_ACCOUNT INNER JOIN ((DB2OPER_FT_REPETITIVE INNER JOIN DB2OPER_FT_REPETITIVE_DR ON (DB2OPER_FT_REPETITIVE.BANK = DB2OPER_FT_REPETITIVE_DR.BANK) AND (DB2OPER_FT_REPETITIVE.TYPE = DB2OPER_FT_REPETITIVE_DR.TYPE) AND (DB2OPER_FT_REPETITIVE.KEY_ACC = DB2OPER_FT_REPETITIVE_DR.KEY_ACC) AND (DB2OPER_FT_REPETITIVE.REPETITIVE_ID = DB2OPER_FT_REPETITIVE_DR.REPETITIVE_ID) AND (DB2OPER_FT_REPETITIVE.RPT_STATUS_FLG = DB2OPER_FT_REPETITIVE_DR.RPT_STATUS_FLG)) INNER JOIN DB2OPER_FT_REPETITIVE_CR ON (DB2OPER_FT_REPETITIVE.BANK = DB2OPER_FT_REPETITIVE_CR.BANK) AND (DB2OPER_FT_REPETITIVE.TYPE = DB2OPER_FT_REPETITIVE_CR.TYPE) AND (DB2OPER_FT_REPETITIVE.KEY_ACC = DB2OPER_FT_REPETITIVE_CR.KEY_ACC) AND (DB2OPER_FT_REPETITIVE.REPETITIVE_ID = DB2OPER_FT_REPETITIVE_CR.REPETITIVE_ID) AND (DB2OPER_FT_REPETITIVE.RPT_STATUS_FLG = DB2OPER_FT_REPETITIVE_CR.RPT_STATUS_FLG)) ON (DB2OPER_FT_ACCOUNT.BANK_ID = DB2OPER_FT_REPETITIVE.BANK) AND (DB2OPER_FT_ACCOUNT.ID_TYPE = DB2OPER_FT_REPETITIVE.TYPE) AND (DB2OPER_FT_ACCOUNT.ACCOUNT = DB2OPER_FT_REPETITIVE.KEY_ACC) AND (DB2OPER_FT_ACCOUNT.ACCOUNT = DB2OPER_FT_REPETITIVE_CR.KEY_ACC) AND (DB2OPER_FT_ACCOUNT.BANK_ID = DB2OPER_FT_REPETITIVE_CR.BANK) AND (DB2OPER_FT_ACCOUNT.ID_TYPE = DB2OPER_FT_REPETITIVE_CR.TYPE) AND (DB2OPER_FT_ACCOUNT.BANK_ID = DB2OPER_FT_REPETITIVE_DR.BANK) AND (DB2OPER_FT_ACCOUNT.ID_TYPE = DB2OPER_FT_REPETITIVE_DR.TYPE)
WHERE (((DB2OPER_FT_ACCOUNT.BANK_ID) Like [Enter the Bank ID/Org] & "*&quot AND ((DB2OPER_FT_ACCOUNT.ID_TYPE) Like [Enter the Account Type D/V/G(case sensitive)] & "*&quot AND ((DB2OPER_FT_ACCOUNT.ACCOUNT) Like [Enter the Account Number] & "*&quot AND ((DB2OPER_FT_ACCOUNT.REL_STATUS_FLG)="C&quot AND ((DB2OPER_FT_REPETITIVE.RPT_STATUS_FLG)="C&quot);

Pointer fields:
DB2OPER_FT_REPETITIVE_DR.SBK_REL_ID
DB2OPER_FT_REPETITIVE_DR.OBK_REL_ID
DB2OPER_FT_REPETITIVE_DR.ORP_REL_ID
DB2OPER_FT_REPETITIVE_CR.CDT_REL_ID
DB2OPER_FT_REPETITIVE_CR.IBK_REL_ID
DB2OPER_FT_REPETITIVE_CR.BBK_REL_ID

These pointers are the key to a table called DB2OPER_FT_ADDRESS
for each pointer, when they are not equal to either all spaces or all zeros, I need to retrieve about six fields from this table.

Thanks again.

 

[scriverb]

Wow, I would first suggest that the use of Alias's be imposed here so that this query doesn't exceed the maximum characters. Use a text editor to identify each table reference and replace it with the alias name:

DB2OPER_FT_ACCOUNT as A
DB2OPER_FT_REPETITIVE as R
DB2OPER_FT_REPETITIVE_DR as RDR
DB2OPER_FT_REPETITIVE_DR as RCR
etc.

After these Alias's are created then perform a Search and Replace with the name plus a period replace with the Alias.
Search for: DB2OPER_FT_ACCOUNT.
Replace with: A.

Then repost the SQL and we can help you out.


Bob Scriver
Want the best answers? See FAQ181-2886
Nobody believes the official spokesman... but everybody trusts an unidentified source.
Author, Bagdad Bob???

 

[WireGuyJCB]

I can understand the confusion. I guess I'm just used to it. Here is the SQL in a cleaned up fashion with alises. I did run it so I know it works.

SELECT AC.BANK_ID,
AC.ID_TYPE,
AC.ACCOUNT,
AC.ACC_NAME,
AC.REL_STATUS_FLG,
R.BANK,
R.TYPE,
R.KEY_ACC,
R.REPETITIVE_ID,
R.OWN_BANK,
R.RPT_STATUS_FLG,
R.OWN_LOC,
R.SOURCE_CD,
R.TYPE_CD,
R.TRAN_TYPE,
R.INSTR_ADV_TYPE,
R.CURRENCY_CODE,
R.DBT_CHRG,
R.CDT_CHRG,
R.COMMISSION,
R.CBL_CHARGE,
RCR.CDT_ADV_TYPE,
RDR.DBT_NAME1,
RDR.DBT_NAME2,
RDR.DBT_NAME3,
RDR.DBT_NAME4,
RDR.DBT_RECON_REF,
RDR.SPC_INST1,
RDR.SPC_INST2,
RDR.SPC_INST3,
RDR.SBK_ADR_BNK_ID,
RDR.SBK_REL_ID,
RDR.SBK_IDTYPE,
RDR.SBK_ID,
RDR.SBK_NAME1,
RDR.SBK_NAME2,
RDR.SBK_NAME3,
RDR.SBK_NAME4,
RDR.SBK_REF_NUM,
RDR.OBK_ADR_BNK_ID,
RDR.OBK_REL_ID,
RDR.OBK_IDTYPE,
RDR.OBK_ID,
RDR.OBK_NAME1,
RDR.OBK_NAME2,
RDR.OBK_NAME3,
RDR.OBK_NAME4,
RDR.OBK_REF_NUM,
RDR.ORP_ADR_BNK_ID,
RDR.ORP_REL_ID,
RDR.ORP_IDTYPE,
RDR.ORP_ID,
RDR.ORP_NAME1,
RDR.ORP_NAME2,
RDR.ORP_NAME3,
RDR.ORP_NAME4,
RDR.ORP_REF_NUM,
RDR.DBT_BNK_INF1,
RDR.DBT_BNK_INF2,
RDR.DBT_BNK_INF3,
RDR.DBT_BNK_INF4,
RDR.DBT_BNK_INF5,
RDR.DBT_BNK_INF6,
RCR.CDT_ADR_BNK_ID,
RCR.CDT_REL_ID,
RCR.BANK_ID,
RCR.ID_TYPE,
RCR.ACCOUNT,
RCR.ADV_TYPE,
RCR.CDT_NAME1,
RCR.CDT_NAME2,
RCR.CDT_NAME3,
RCR.CDT_ADV_INST1,
RCR.CDT_ADV_INST2,
RCR.CDT_ADV_INST3,
RCR.CDT_RECON_REF,
RCR.CDT_SPC_INST1,
RCR.CDT_SPC_INST2,
RCR.CDT_SPC_INST3,
RCR.IB1_ADR_BNK_ID,
RCR.IB1_REL_ID,
RCR.IB1_IDTYPE,
RCR.IB1_ID,
RCR.IB1_NAME1,
RCR.IB1_NAME2,
RCR.IB1_NAME3,
RCR.IB1_NAME4,
RCR.IB1_ADV_INST1,
RCR.IB1_ADV_INST2,
RCR.IB1_ADV_INST3,
RCR.IBK_ADR_BNK_ID,
RCR.IBK_REL_ID,
RCR.IBK_IDTYPE,
RCR.IBK_ID,
RCR.IBK_NAME1,
RCR.IBK_NAME2,
RCR.IBK_NAME3,
RCR.IBK_NAME4,
RCR.IBK_ADV_INST1,
RCR.IBK_ADV_INST2,
RCR.IBK_ADV_INST3,
RCR.BBK_ADR_BNK_ID,
RCR.BBK_REL_ID,
RCR.BBK_SECWIR,
RCR.BBK_IDTYPE,
RCR.BBK_ID,
RCR.BBK_ID_OVERFLOW,
RCR.BBK_NAME1,
RCR.BBK_NAME2,
RCR.BBK_NAME3,
RCR.BBK_NAME4,
RCR.BBK_ADV_INST1,
RCR.BBK_ADV_INST2,
RCR.BBK_ADV_INST3,
RCR.BNP_CHARGE_FLG,
RCR.BNP_IDTYPE,
RCR.BNP_ID,
RCR.BNP_ID_OVERFLOW,
RCR.BNP_BNK_FLG,
RCR.BNP_NAME1,
RCR.BNP_NAME2,
RCR.BNP_NAME3,
RCR.BNP_NAME4,
RCR.BNP_ADV_INST1,
RCR.BNP_ADV_INST2,
RCR.BNP_ADV_INST3,
RCR.ORP_BEN_INF1,
RCR.ORP_BEN_INF2,
RCR.ORP_BEN_INF3,
RCR.ORP_BEN_INF4
FROM DB2OPER_FT_ACCOUNT AS AC
INNER JOIN ((DB2OPER_FT_REPETITIVE AS R INNER JOIN DB2OPER_FT_REPETITIVE_DR AS RDR ON (R.BANK = RDR.BANK) AND (R.TYPE = RDR.TYPE) AND (R.KEY_ACC = RDR.KEY_ACC) AND (R.REPETITIVE_ID = RDR.REPETITIVE_ID) AND (R.RPT_STATUS_FLG = RDR.RPT_STATUS_FLG)) INNER JOIN DB2OPER_FT_REPETITIVE_CR AS RCR ON (R.BANK = RCR.BANK) AND (R.TYPE = RCR.TYPE) AND (R.KEY_ACC = RCR.KEY_ACC) AND (R.REPETITIVE_ID = RCR.REPETITIVE_ID) AND (R.RPT_STATUS_FLG = RCR.RPT_STATUS_FLG)) ON (AC.BANK_ID = R.BANK) AND (AC.ID_TYPE = R.TYPE) AND (AC.ACCOUNT = R.KEY_ACC) AND (AC.ACCOUNT = RCR.KEY_ACC) AND (AC.BANK_ID = RCR.BANK) AND (AC.ID_TYPE = RCR.TYPE) AND (AC.BANK_ID = RDR.BANK) AND (AC.ID_TYPE = RDR.TYPE)
WHERE (((AC.BANK_ID)
Like [Enter the Bank ID/Org] & "*&quot AND
((AC.ID_TYPE)
Like [Enter the Account Type D/V/G(case sensitive)] & "*&quot AND
((AC.ACCOUNT) Like [Enter the Account Number] & "*&quot AND
((AC.REL_STATUS_FLG)="C&quot AND
((R.RPT_STATUS_FLG)="C&quot);


The fields which act as keys to the address table are:
RDR.SBK_REL_ID
RDR.OBK_REL_ID
RDR.ORP_REL_ID
RCR.CDT_REL_ID
RCR.IBK_REL_ID
RCR.BBK_REL_ID

This is basically the SQL that I want to do for each of these six keys :

SELECT AD.REL_ID,
AD.ADR_NAME,
AD.ADR1,
AD.ADR2,
AD.ADR3,
AD.ZIP
FROM DB2OPER_FT_ADDRESS as AD
WHERE (((AD.REL_ID)=[ENTER THE REL ID]) AND ((AD.REL_STATUS_FLG)="C&quot);

In this the [ENTER THE REL ID] becomes the keys above.

But it's only done if the key has a value.

Thanks for the help.