Need help with a join - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Need help with a join

Author

Topic

jpockets
Starting Member

45 Posts

Posted - 2008-04-16 : 15:08:25

I have this query for a join, but i'm getting an error that says
The column prefix 'GAF' does not match with a table name or alias name used in the query.(42000,107)

This is the query: I never did a join on this many tables before so there could be something that i am missing:

Select 
GL.DSP_DIV_NO,
GL.D_APC_TXT,
GL.GL_CD,
GL.GL_TYPE_CD,
GL.GL_COMPONENT_CD,
GL.GL_COMPENT_CD_2,
D.BRANCH_CD,
D.F_TRANS_CD,
D.GL_CD, 
D.GL_COMPONENT_CD, 
D.GL_TYPE_CD, 
D.MARKET_SEGMENT_CD,
D.PREMIUM_AM,
D.MONTH,
D.YEAR,

Case WHEN GL.D_APC_TXT='Fidelity' then 'CRM' else 'excl. CRM' end LOB,
 sum((case when D.GL_Component_CD in ('ABA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'F' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) BaseGWP,
 
  sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'B' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) BudgetGWP, 
 
 sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'A' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) PriorGWP, 
 
 sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'B' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) IniFcstGWP,
 GAF.FCST_MARKET_SEG_CD

from nurdevusrt.GL_MASTER GL left join (
Select
D.DIVISION_NO,
D.APC_TX, 
D.BRANCH_CD, 
D.F_TRANS_CD,
D.GL_CD, 
D.GL_COMPONENT_CD, 
D.GL_TYPE_CD, 
D.MARKET_SEGMENT_CD, 
 
D.PREMIUM_AM, 
D.PREMIUM_RT,
D.MONTH, 
D.YEAR,
GAF.FCST_MARKET_SEG_CD FCST_MARKET_SEG_CD 

from dbo.TGL_DETL D left join

(
Select 
GAF.FCST_MARKET_SEG_CD FCST_MARKET_SEG_CD ,
GAF.CRDT_REGION_CD CRDT_REGION_CD,
GAF.DSP_DIV_NO DSP_DIV_NO,
GAF.D_APC_TX D_APC_TX,
GAF.LEDGER_MONTH LEDGER_MONTH,
GAF.LEDGER_YEAR LEDGER_YEAR,
GAF.F_TRANS_CD F_TRANS_CD,
GAF.SALES_VERIFIED_AM SALES_VERIFIED_AM,
GAF.SALES_OPEN_AM SALES_OPEN_AM,
GAF.PIPE_WRK_CT PIPE_WRK_CT,
GAF.PIPE_QTE_CT PIPE_QTE_CT,
GAF.PIPE_QTE_GWP_AM PIPE_QTE_GWP_AM,
GAF.FCST_CT FCST_CT,
GAF.FCST_AM FCST_AM,
GAF.FCST_UP_CT FCST_UP_CT,
GAF.FCST_UP_AM FCST_UP_AM,
GAF.FCST_AGG_AM FCST_AGG_AM,
GAF.UPDATE_ID UPDATE_ID,
GAF.LOAD_DT

from dbo.TGRID_AGG_FCST GAF inner join (
select
max(GAF.LOAD_DT) Load_dt,
GAF.CRDT_REGION_CD,
GAF.DSP_DIV_NO,
GAF.D_APC_TX,
GAF.LEDGER_MONTH,
GAF.LEDGER_YEAR,
GAF.F_TRANS_CD
from dbo.TGRID_AGG_FCST GAF
Group By
GAF.CRDT_REGION_CD,
GAF.DSP_DIV_NO,
GAF.D_APC_TX,
GAF.LEDGER_MONTH,
GAF.LEDGER_YEAR,
GAF.F_TRANS_CD ) GAF2 on
GAF.LOAD_DT = GAF2.LOAD_DT and
GAF.CRDT_REGION_CD = GAF2.CRDT_REGION_CD and
GAF.D_APC_TX = GAF2.D_APC_TX and
GAF.DSP_DIV_NO = GAF2.DSP_DIV_NO and
GAF.F_TRANS_CD = GAF2.F_TRANS_CD and
GAF.LEDGER_MONTH = GAF2.Ledger_MONTH and
GAF.LEDGER_YEAR = GAF2.LEDGER_YEAR ) GAF ON
GAF.D_APC_TX = D.APC_TX  and
GAF.CRDT_REGION_CD = D.BRANCH_CD and
GAF.DSP_DIV_NO = DIVISION_NO and
GAF.LEDGER_MONTH = D.MONTH and
GAF.LEDGER_YEAR = D.YEAR  and
GAF.F_TRANS_CD = D.F_TRANS_CD) D on
D.DIVISION_NO = GL.DSP_DIV_NO and 
D.APC_TX = GL.D_APC_TXT and

D.F_TRANS_CD = GL.GL_COMPENT_CD_2 and
D.GL_CD = GL.GL_CD and 
D.GL_COMPONENT_CD = GL.GL_COMPONENT_CD and 
D.GL_TYPE_CD = GL.GL_TYPE_CD



Group By
GL.DSP_DIV_NO,
GL.D_APC_TXT,
GL.GL_CD,
GL.GL_TYPE_CD,
GL.GL_COMPONENT_CD,
GL.GL_COMPENT_CD_2,
D.BRANCH_CD,
D.F_TRANS_CD,
D.GL_CD, 
D.GL_COMPONENT_CD, 
D.GL_TYPE_CD, 
D.MARKET_SEGMENT_CD,
D.PREMIUM_AM,
D.MONTH,
D.YEAR,
GAF.FCST_MARKET_SEG_CD

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 15:14:20

Rubbish deleted

left join (
Select
D.DIVISION_NO,
D.APC_TX,
D.BRANCH_CD,
D.F_TRANS_CD,
D.GL_CD,
D.GL_COMPONENT_CD,
D.GL_TYPE_CD,
D.MARKET_SEGMENT_CD,

D.PREMIUM_AM,
D.PREMIUM_RT,
D.MONTH,
D.YEAR,
GAF.FCST_MARKET_SEG_CD FCST_MARKET_SEG_CD

from dbo.TGL_DETL D left join

It's difficult to tell from your layout (I might reformat it in a bit)
but I don't think GAF is exposed at that level - should be D. maybe?

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nr
SQLTeam MVY

12543 Posts

Posted - 2008-04-16 : 15:31:28

From this you can see that the only tables exposed to the outer select are GL and D so the GAF in the select and group by are invalid.
D. should work.


Select 
	GL.DSP_DIV_NO,
	GL.D_APC_TXT,
	GL.GL_CD,
	GL.GL_TYPE_CD,
	GL.GL_COMPONENT_CD,
	GL.GL_COMPENT_CD_2,
	D.BRANCH_CD,
	D.F_TRANS_CD,
	D.GL_CD, 
	D.GL_COMPONENT_CD, 
	D.GL_TYPE_CD, 
	D.MARKET_SEGMENT_CD,
	D.PREMIUM_AM,
	D.MONTH,
	D.YEAR,
	Case WHEN GL.D_APC_TXT='Fidelity' then 'CRM' else 'excl. CRM' end LOB,
	sum((case when D.GL_Component_CD in ('ABA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'F' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) BaseGWP,
	sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'B' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) BudgetGWP, 
	sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'A' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) PriorGWP, 
	sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'B' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) IniFcstGWP,
	GAF.FCST_MARKET_SEG_CD
from nurdevusrt.GL_MASTER GL
	left join
		(	Select
				D.DIVISION_NO,
				D.APC_TX, 
				D.BRANCH_CD, 
				D.F_TRANS_CD,
				D.GL_CD, 
				D.GL_COMPONENT_CD, 
				D.GL_TYPE_CD, 
				D.MARKET_SEGMENT_CD, 
				 
				D.PREMIUM_AM, 
				D.PREMIUM_RT,
				D.MONTH, 
				D.YEAR,
				GAF.FCST_MARKET_SEG_CD FCST_MARKET_SEG_CD 
			from dbo.TGL_DETL D
				left join
					(
						Select 
							GAF.FCST_MARKET_SEG_CD FCST_MARKET_SEG_CD ,
							GAF.CRDT_REGION_CD CRDT_REGION_CD,
							GAF.DSP_DIV_NO DSP_DIV_NO,
							GAF.D_APC_TX D_APC_TX,
							GAF.LEDGER_MONTH LEDGER_MONTH,
							GAF.LEDGER_YEAR LEDGER_YEAR,
							GAF.F_TRANS_CD F_TRANS_CD,
							GAF.SALES_VERIFIED_AM SALES_VERIFIED_AM,
							GAF.SALES_OPEN_AM SALES_OPEN_AM,
							GAF.PIPE_WRK_CT PIPE_WRK_CT,
							GAF.PIPE_QTE_CT PIPE_QTE_CT,
							GAF.PIPE_QTE_GWP_AM PIPE_QTE_GWP_AM,
							GAF.FCST_CT FCST_CT,
							GAF.FCST_AM FCST_AM,
							GAF.FCST_UP_CT FCST_UP_CT,
							GAF.FCST_UP_AM FCST_UP_AM,
							GAF.FCST_AGG_AM FCST_AGG_AM,
							GAF.UPDATE_ID UPDATE_ID,
							GAF.LOAD_DT
						from dbo.TGRID_AGG_FCST GAF
							inner join
								(
									select
										max(GAF.LOAD_DT) Load_dt,
										GAF.CRDT_REGION_CD,
										GAF.DSP_DIV_NO,
										GAF.D_APC_TX,
										GAF.LEDGER_MONTH,
										GAF.LEDGER_YEAR,
										GAF.F_TRANS_CD
										from dbo.TGRID_AGG_FCST GAF
									Group By
										GAF.CRDT_REGION_CD,
										GAF.DSP_DIV_NO,
										GAF.D_APC_TX,
										GAF.LEDGER_MONTH,
										GAF.LEDGER_YEAR,
										GAF.F_TRANS_CD
								) GAF2 
									on
										GAF.LOAD_DT = GAF2.LOAD_DT and
										GAF.CRDT_REGION_CD = GAF2.CRDT_REGION_CD and
										GAF.D_APC_TX = GAF2.D_APC_TX and
										GAF.DSP_DIV_NO = GAF2.DSP_DIV_NO and
										GAF.F_TRANS_CD = GAF2.F_TRANS_CD and
										GAF.LEDGER_MONTH = GAF2.Ledger_MONTH and
										GAF.LEDGER_YEAR = GAF2.LEDGER_YEAR
					) GAF
						ON
							GAF.D_APC_TX = D.APC_TX  and
							GAF.CRDT_REGION_CD = D.BRANCH_CD and
							GAF.DSP_DIV_NO = DIVISION_NO and
							GAF.LEDGER_MONTH = D.MONTH and
							GAF.LEDGER_YEAR = D.YEAR  and
							GAF.F_TRANS_CD = D.F_TRANS_CD
		) D 
			on
				D.DIVISION_NO = GL.DSP_DIV_NO and 
				D.APC_TX = GL.D_APC_TXT and
				D.F_TRANS_CD = GL.GL_COMPENT_CD_2 and
				D.GL_CD = GL.GL_CD and 
				D.GL_COMPONENT_CD = GL.GL_COMPONENT_CD and 
				D.GL_TYPE_CD = GL.GL_TYPE_CD
Group By
	GL.DSP_DIV_NO,
	GL.D_APC_TXT,
	GL.GL_CD,
	GL.GL_TYPE_CD,
	GL.GL_COMPONENT_CD,
	GL.GL_COMPENT_CD_2,
	D.BRANCH_CD,
	D.F_TRANS_CD,
	D.GL_CD, 
	D.GL_COMPONENT_CD, 
	D.GL_TYPE_CD, 
	D.MARKET_SEGMENT_CD,
	D.PREMIUM_AM,
	D.MONTH,
	D.YEAR,
	GAF.FCST_MARKET_SEG_CD

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

jpockets
Starting Member

45 Posts

Posted - 2008-04-16 : 15:58:39

Thank-you very much. I see what you are saying, you have to pass the value to outer select and than rename the alias. Thank-you, it's a learning process!

jpockets
Starting Member

45 Posts

Posted - 2008-04-22 : 11:17:20

I just found a bug in the query and i'm banging my head against the wall trying to work through it. I need to include this query again

 Select 
                GAF.FCST_MARKET_SEG_CD,
                GAF.CRDT_REGION_CD CRDT_REGION_CD,
                GAF.DSP_DIV_NO DSP_DIV_NO,
                GAF.D_APC_TX D_APC_TX,
                GAF.LEDGER_MONTH LEDGER_MONTH,
                GAF.LEDGER_YEAR LEDGER_YEAR,
                GAF.F_TRANS_CD F_TRANS_CD,
                GAF.SALES_VERIFIED_AM SALES_VERIFIED_AM,
                GAF.SALES_OPEN_AM SALES_OPEN_AM,
                GAF.PIPE_WRK_CT PIPE_WRK_CT,
                GAF.PIPE_QTE_CT PIPE_QTE_CT,
                GAF.PIPE_QTE_GWP_AM PIPE_QTE_GWP_AM,
                GAF.FCST_CT FCST_CT,
                GAF.FCST_AM FCST_AM,
                GAF.FCST_UP_CT FCST_UP_CT,
                GAF.FCST_UP_AM FCST_UP_AM,
                GAF.FCST_AGG_AM FCST_AGG_AM,
                GAF.UPDATE_ID UPDATE_ID,
                GAF.LOAD_DT
                from dbo.TGRID_AGG_FCST GAF inner join 
                    (
                        select
                            max(GAF.LOAD_DT) Load_dt,
                            GAF.CRDT_REGION_CD,
                            GAF.DSP_DIV_NO,
                            GAF.D_APC_TX,
                            GAF.LEDGER_MONTH,
                            GAF.LEDGER_YEAR,
                            GAF.F_TRANS_CD
                            from dbo.TGRID_AGG_FCST GAF
                            Group By
                            GAF.CRDT_REGION_CD,
                            GAF.DSP_DIV_NO,
                            GAF.D_APC_TX,
                            GAF.LEDGER_MONTH,
                            GAF.LEDGER_YEAR,
                            GAF.F_TRANS_CD

I need to join it on the first table nurdevusrt.GL_MASTER GL using the following columns in the join: CRDT_REGION_CD, DSP_DIV_NO,D_APC_TX,LEDGER_MONTH, LEDGER_YEAR, F_Trans_Cd. I'm just not sure where to put the placement of this query to join on the first table again nurdevusrt.GL_MASTER. Any help is greatly appriciated This is the full query:

Select 
GL.DSP_DIV_NO,
GL.D_APC_TXT,
GL.GL_CD,
GL.GL_TYPE_CD,
GL.GL_COMPONENT_CD,
GL.GL_COMPENT_CD_2,
D.BRANCH_CD,
D.BRANCH_NAME ,
D.F_TRANS_CD,
D.GL_CD, 
D.GL_COMPONENT_CD, 
D.GL_TYPE_CD, 
D.MARKET_SEGMENT_CD,
ISNULL(D.PREMIUM_AM,0) Premium_AM,
D.MONTH,
D.YEAR,

Case WHEN GL.D_APC_TXT='Fidelity' then 'CRM' else 'excl. CRM' end LOB,
 sum((case when D.GL_Component_CD in ('ABA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'F' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) BaseGWP,
 
  sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'B' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) BudgetGWP, 
 
 sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'A' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) PriorGWP, 
 
 sum((case when D.GL_Component_CD in ('RBA', 'GWP') and rtrim(ltrim(isnull(D.GL_TYPE_CD,'x'))) = 'B' then 1.00 else 0.00 End)*isnull(D.PREMIUM_AM, 0.00)) IniFcstGWP,

D.FCST_MARKET_SEG_CD,
ISNULL(D.SALES_VERIFIED_AM,0) SALES_VERIFIED_AM,
ISNULL(D.SALES_OPEN_AM,0) SALES_OPEN_AM,
ISNULL(D.PIPE_WRK_CT,0) PIPE_WRK_CT,
ISNULL(D.PIPE_QTE_CT,0) PIPE_QTE,
ISNULL(D.PIPE_QTE_GWP_AM,0) PIPE_QTE_AM,
ISNULL(D.FCST_CT,0) FCST_CT,
ISNULL(D.FCST_AM,0) FCST_AM,
ISNULL(D.FCST_UP_CT,0) FCST_UP_CT,
ISNULL(D.FCST_UP_AM,0) FCST_UP_AM,
ISNULL(D.FCST_AGG_AM,0) FCST_AGG_AM


from nurdevusrt.GL_MASTER GL  left join (

    Select
    D.DIVISION_NO,
    D.APC_TX, 
    D.BRANCH_CD, 
    D.F_TRANS_CD,
    D.GL_CD, 
    D.GL_COMPONENT_CD, 
    D.GL_TYPE_CD, 
    D.MARKET_SEGMENT_CD, 
    D.PREMIUM_AM, 
    D.PREMIUM_RT,
    D.MONTH, 
    D.YEAR,
    GAF.FCST_MARKET_SEG_CD,
    GAF.SALES_VERIFIED_AM,
    GAF.SALES_OPEN_AM,
    GAF.PIPE_WRK_CT,
    GAF.PIPE_QTE_CT,
    GAF.PIPE_QTE_GWP_AM,
    GAF.FCST_CT,
    GAF.FCST_AM,
    GAF.FCST_UP_CT,
    GAF.FCST_UP_AM,
    GAF.FCST_AGG_AM,
    B.BRANCH_NAME 

    from dbo.TGL_DETL D  left join 
  
        (
            select 
            B.BRANCH_CODE,
            B.BRANCH_NAME 
            from dbo.TBRANCH B) B on 
            B.BRANCH_CODE = D.BRANCH_CD left join

            (
                Select 
                GAF.FCST_MARKET_SEG_CD,
                GAF.CRDT_REGION_CD CRDT_REGION_CD,
                GAF.DSP_DIV_NO DSP_DIV_NO,
                GAF.D_APC_TX D_APC_TX,
                GAF.LEDGER_MONTH LEDGER_MONTH,
                GAF.LEDGER_YEAR LEDGER_YEAR,
                GAF.F_TRANS_CD F_TRANS_CD,
                GAF.SALES_VERIFIED_AM SALES_VERIFIED_AM,
                GAF.SALES_OPEN_AM SALES_OPEN_AM,
                GAF.PIPE_WRK_CT PIPE_WRK_CT,
                GAF.PIPE_QTE_CT PIPE_QTE_CT,
                GAF.PIPE_QTE_GWP_AM PIPE_QTE_GWP_AM,
                GAF.FCST_CT FCST_CT,
                GAF.FCST_AM FCST_AM,
                GAF.FCST_UP_CT FCST_UP_CT,
                GAF.FCST_UP_AM FCST_UP_AM,
                GAF.FCST_AGG_AM FCST_AGG_AM,
                GAF.UPDATE_ID UPDATE_ID,
                GAF.LOAD_DT
                from dbo.TGRID_AGG_FCST GAF inner join 
                    (
                        select
                            max(GAF.LOAD_DT) Load_dt,
                            GAF.CRDT_REGION_CD,
                            GAF.DSP_DIV_NO,
                            GAF.D_APC_TX,
                            GAF.LEDGER_MONTH,
                            GAF.LEDGER_YEAR,
                            GAF.F_TRANS_CD
                            from dbo.TGRID_AGG_FCST GAF
                            Group By
                            GAF.CRDT_REGION_CD,
                            GAF.DSP_DIV_NO,
                            GAF.D_APC_TX,
                            GAF.LEDGER_MONTH,
                            GAF.LEDGER_YEAR,
                            GAF.F_TRANS_CD ) GAF2 on
                            GAF.LOAD_DT = GAF2.LOAD_DT and
                            GAF.CRDT_REGION_CD =                            GAF2.CRDT_REGION_CD and
                            GAF.D_APC_TX = GAF2.D_APC_TX and
                            GAF.DSP_DIV_NO = GAF2.DSP_DIV_NO                            and
                            GAF.F_TRANS_CD = GAF2.F_TRANS_CD                            and
                            GAF.LEDGER_MONTH =                            GAF2.Ledger_MONTH and
                            GAF.LEDGER_YEAR =          GAF2.LEDGER_YEAR                            ) GAF ON
                        GAF.D_APC_TX = D.APC_TX  and
                        GAF.CRDT_REGION_CD = D.BRANCH_CD and
                        GAF.DSP_DIV_NO = D.DIVISION_NO and
                        GAF.LEDGER_MONTH = D.MONTH and
                        GAF.LEDGER_YEAR = D.YEAR  and
                        GAF.F_TRANS_CD = D.F_TRANS_CD) D on
                    D.DIVISION_NO = GL.DSP_DIV_NO and 
                    D.APC_TX = GL.D_APC_TXT and
                    D.F_TRANS_CD = GL.GL_COMPENT_CD_2 and
                    D.GL_CD = GL.GL_CD and 
                    D.GL_COMPONENT_CD = GL.GL_COMPONENT_CD and 
                    D.GL_TYPE_CD = GL.GL_TYPE_CD 
                Group By
                GL.DSP_DIV_NO,
                GL.D_APC_TXT,
                GL.GL_CD,
                GL.GL_TYPE_CD,
                GL.GL_COMPONENT_CD,
                GL.GL_COMPENT_CD_2,
                D.BRANCH_CD,
                D.F_TRANS_CD,
                D.GL_CD, 
                D.GL_COMPONENT_CD, 
                D.GL_TYPE_CD, 
                D.MARKET_SEGMENT_CD,
                D.PREMIUM_AM,
                D.MONTH,
                D.YEAR,
                D.FCST_MARKET_SEG_CD,
                D.SALES_VERIFIED_AM,
                D.SALES_OPEN_AM,
                D.PIPE_WRK_CT,
                D.PIPE_QTE_CT,
                D.PIPE_QTE_GWP_AM,
                D.FCST_CT,
                D.FCST_AM,
                D.FCST_UP_CT,
                D.FCST_UP_AM,
                D.FCST_AGG_AM,
                D.BRANCH_NAME

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-22 : 14:16:56

Your posted query snippet is not complete. Where is part after join?

jpockets
Starting Member

45 Posts

Posted - 2008-04-22 : 16:04:21

This is the query i would like to join on the main query just not sure on how to go about it

Select
GAF.FCST_MARKET_SEG_CD,
GAF.CRDT_REGION_CD CRDT_REGION_CD,
GAF.DSP_DIV_NO DSP_DIV_NO,
GAF.D_APC_TX D_APC_TX,
GAF.LEDGER_MONTH LEDGER_MONTH,
GAF.LEDGER_YEAR LEDGER_YEAR,
GAF.F_TRANS_CD F_TRANS_CD,
GAF.SALES_VERIFIED_AM SALES_VERIFIED_AM,
GAF.SALES_OPEN_AM SALES_OPEN_AM,
GAF.PIPE_WRK_CT PIPE_WRK_CT,
GAF.PIPE_QTE_CT PIPE_QTE_CT,
GAF.PIPE_QTE_GWP_AM PIPE_QTE_GWP_AM,
GAF.FCST_CT FCST_CT,
GAF.FCST_AM FCST_AM,
GAF.FCST_UP_CT FCST_UP_CT,
GAF.FCST_UP_AM FCST_UP_AM,
GAF.FCST_AGG_AM FCST_AGG_AM,
GAF.UPDATE_ID UPDATE_ID,
GAF.LOAD_DT
from dbo.TGRID_AGG_FCST GAF inner join
(
select
max(GAF.LOAD_DT) Load_dt,
GAF.CRDT_REGION_CD,
GAF.DSP_DIV_NO,
GAF.D_APC_TX,
GAF.LEDGER_MONTH,
GAF.LEDGER_YEAR,
GAF.F_TRANS_CD
from dbo.TGRID_AGG_FCST GAF
Group By
GAF.CRDT_REGION_CD,
GAF.DSP_DIV_NO,
GAF.D_APC_TX,
GAF.LEDGER_MONTH,
GAF.LEDGER_YEAR,
GAF.F_TRANS_CD ) GAF2 on
GAF.LOAD_DT = GAF2.LOAD_DT and
GAF.CRDT_REGION_CD = GL.CRDT_REGION_CD and
GAF.D_APC_TX = GL.D_APC_TX and
GAF.DSP_DIV_NO = GL.DSP_DIV_NO and
GAF.F_TRANS_CD =GL.GL_COMPENT_CD_2

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources