Solved

Problem with prefix for inner join and output to file

Posted on 2006-07-05
21
449 Views
Last Modified: 2012-06-21
Hello, I'm writing a store procedure that uses inner joins but for some reason I am getting the following error:

Server: Msg 107, Level 16, State 2, Procedure mw_aragedreceivables_sp, Line 206
The column prefix 'a' does not match with a table name or alias name used in the query.

Here is my code:

--  exec dbo.mw_aragedreceivables_sp 'MWHydrocarbon', '06/14/2006', NULL, NULL, ''


-- drop procedure mw_aragedreceivables_sp

CREATE procedure dbo.mw_aragedreceivables_sp
      @company            varchar(40),
      @dateto                  varchar(32),
      @fromcustomercode      varchar(8),
      @tocustomercode            varchar(8),
--      @sort_column              varchar(3),
      @territory_code        varchar(8),
      @days_aged            int = 0,
      @availableC            int = 0,
      @balance            int = 0

as
declare
      @dateto_jul            int,
      @year                 int,
      @month                int,
      @date                 int,
      @jul_date            int,
      @dateto_date            datetime


-- temporary table creation
create table #artrxage_tmp
(
      trx_type            smallint,
      trx_ctrl_num            varchar(16),
      doc_ctrl_num            varchar(16),
      apply_to_num            varchar(16),
      apply_trx_type            smallint,
      sub_apply_num            varchar(16),
      sub_apply_type            smallint,
      salesperson_code      varchar(8),
      territory_code            varchar(8),
      price_code            varchar(8),
      date_doc            int,
      date_due            int,
      date_aging            int,
      date_applied            int,
      amount                  float,
      cust_po_num            varchar(20),
      order_ctrl_num            varchar(16),
      customer_code            varchar(8),
      payer_cust_code            varchar(8),
      trx_type_code            varchar(8),      
      ref_id                  int,
      nat_cur_code            varchar(8),
      rate_oper             float,
      divide_flag             int
)

create table #finalvalues
(
      customer_code   varchar(50),
      customer_name   varchar(50),
      doc_ctrl_num    varchar(50),
      apply_to_num    varchar(50),
      apply_trx_type  smallint,
      date_doc        int,
      date_due        int,
      date_aging      int,
      date_applied    int,
      amount          float,
      salesperson_code varchar(50),
      territory_code  varchar(50),
      price_code      varchar(50),
      cust_po_num     varchar(50),
      order_ctrl_num  varchar(50),
      trx_type        smallint,
      trx_ctrl_num    varchar(50),
      trx_type_code   varchar(50),
      ref_id          int,
        nat_cur_code    varchar(50),
        rate_oper       float,
        oper_currency   varchar(50),
        divide_flag     int,
        company_name    varchar(50),
        dateto_jul      int,
        credit_limit    float,
      additional_credit float,
      age30_60      float,
      age60_90      float,
      ageover90      float,
)



create table #non_zero_records
(
      doc_ctrl_num varchar(16),
      trx_type smallint,
      customer_code varchar(8),
      total float
)  
create clustered index iv_npif_ind1 on #non_zero_records ( doc_ctrl_num, trx_type, customer_code )


create table #temp1
(
      code varchar(20),
)

create table #temp2
(
      c_id varchar(20),      
      e_code varchar(20),
      code varchar(20)
)  


-- initialise parameters
if (@dateto = '~null~' or @dateto = ''  or @dateto is null) select @dateto_date = getdate()
else select @dateto_date = convert(datetime,@dateto)

select  @dateto_jul = datediff(dd,'1900/01/01', @dateto_date ) + 693596

if (@fromcustomercode = '~null~' or @fromcustomercode = '' or @fromcustomercode is null) select @fromcustomercode = ''
if (@tocustomercode = '~null~'  or @tocustomercode = '' or @tocustomercode is null) select @tocustomercode = 'zzzzzzzz'

if (@territory_code = '~null~' or @territory_code = '' ) select @territory_code = null


-- finds non zero records
insert
  #non_zero_records
select
  a.apply_to_num ,
      a.apply_trx_type,
      a.customer_code,
      sum(amount)
from
  artrxage a
  inner join arcust c on a.customer_code = c.customer_code
where
  a.date_applied <= @dateto_jul and
  a.customer_code between @fromcustomercode and @tocustomercode and
  ( @territory_code is null or c.territory_code = @territory_code )
group by
  a.apply_to_num,
      a.apply_trx_type,
      a.customer_code  having abs(sum(amount)) > 0.0000001

insert into #temp1
select replace(replace(left(customer_code , 15 ), 'AR', ''), 'CUS', '')
from artrxage

insert into #temp2
select company_id, epicor_code, code from core..company_epicor_xref b
inner join #temp1 u on u.code = b.epicor_code
order by epicor_code


-- inserts data for non zero records
insert
  #artrxage_tmp
select
  a.trx_type,
      a.trx_ctrl_num,      
      a.doc_ctrl_num,      
      a.apply_to_num,      
      a.apply_trx_type,      
      a.sub_apply_num,      
      a.sub_apply_type,      
      a.salesperson_code,      
      a.territory_code,      
      a.price_code,      
      a.date_doc,      
      (1+ sign(sign(a.ref_id) - 1))*a.date_due + abs(sign(sign(a.ref_id)-1))*a.date_doc,
      (1+ sign(sign(a.ref_id) - 1))*a.date_aging + abs(sign(sign(a.ref_id)-1))*a.date_doc,
      a.date_applied,      
  a.amount,
      a.cust_po_num,      
      a.order_ctrl_num,      
      a.customer_code,      
      a.payer_cust_code,      
      '',
      a.ref_id,
  a.nat_cur_code,
  a.rate_oper,
  1
from
  artrxage a,
  #non_zero_records i
where
  a.apply_to_num = i.doc_ctrl_num and
  a.apply_trx_type = i.trx_type and
  a.customer_code = i.customer_code and
  a.date_applied <= @dateto_jul


-- updates transaction type description
update      #artrxage_tmp
set       trx_type_code = b.trx_type_code
from       artrxtyp b
where       #artrxage_tmp.trx_type = b.trx_type


--updates divide_flag for multi-currency conversion
update      #artrxage_tmp
set  divide_flag = m.divide_flag
from #artrxage_tmp a
inner join pltmaster..mccurate m on a.nat_cur_code = m.from_currency
inner join glco g on g.oper_currency = m.to_currency


--report selection
insert into #finalvalues
select distinct
      t.company_id,
      t.max(expiration_date) as expiredcredit_limit,
      t.e_code,
        a.customer_code,
      c.customer_name,
      a.doc_ctrl_num,
      a.apply_to_num,
      a.apply_trx_type,
      a.date_doc,
      a.date_due,
      a.date_aging,
      a.date_applied,
      a.amount,
      a.salesperson_code,
      a.territory_code,
      a.price_code,
      a.cust_po_num,
      a.order_ctrl_num,
      a.trx_type,
      a.trx_ctrl_num,
      a.trx_type_code,
      a.ref_id,
        a.nat_cur_code,
        a.rate_oper,
       g.oper_currency,
        a.divide_flag,
       g.company_name,
        @dateto_jul as dateto_jul,
        c.credit_limit,
      isNull((select sum(amount) from core..v_company_additional_credit where epicor_db_name = 'MWHydrocarbon' and status = 'A' and @dateto between effective_date and expiration_date and epicor_code + 'AR' = a.customer_code), 0) as additional_credit,
      case when (@dateto_jul - a.date_aging) between 30 and 60 then amount else 0 end as Age30,
      case when (@dateto_jul - a.date_aging) between 60 and 90 then amount else 0 end as Age60,
      case when (@dateto_jul - a.date_aging) > 90 then amount else 0 end as Age90
      
from
  glco g,
  #artrxage_tmp a,
  core..company_additional_credit t
  inner join arcust c on a.customer_code = c.customer_code
  inner join #temp2 x on x.c_id = t.company_id
  Group by company_id, e_Code
 
   
select * from #finalvalues

/* select customer_code, credit_limit, additional_credit, customer_name,
          (credit_limit + additional_credit) - sum(amount) as balance ,    
          sum(Age30_60)  as   Age30_60 ,    
          sum(Age60_90)  as   Age60_90,        
          sum(ageover90) as   Ageover90
from #finalvalues
group by customer_code, customer_name, credit_limit, additional_credit */

drop table #artrxage_tmp
drop table #finalvalues
drop table #non_zero_records
drop table #temp1
drop table #temp2

GO

I believe that although the query says that the error is coming from the following line I believe the error is coming from huge select at the end of the file in the from section. Also, wants I get this to work I want output the data to a file in a delimeter file. Can someone help me with these two things. I need this info ASAP for a work project.

0
Comment
Question by:DancingFighterG
  • 11
  • 10
21 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 125 total points
ID: 17043757
-- updates transaction type description
update     #artrxage_tmp
set      trx_type_code = b.trx_type_code
from      artrxtyp b
where      #artrxage_tmp.trx_type = b.trx_type

should be

-- updates transaction type description
update     #artrxage_tmp
set      trx_type_code = b.trx_type_code
from      #artrxage_tmp a
join artrxtyp b
where      a.trx_type = b.trx_type



0
 

Author Comment

by:DancingFighterG
ID: 17043823
I'm getting an error on the where clause and if I switch the where and the join I get an error on the join
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17043851
sorry, should be:
-- updates transaction type description
update     #artrxage_tmp
set      trx_type_code = b.trx_type_code
from      #artrxage_tmp a
join artrxtyp b
on a.trx_type = b.trx_type
0
 

Author Comment

by:DancingFighterG
ID: 17043997
I'm still getting the same error:

Server: Msg 107, Level 16, State 2, Procedure mw_aragedreceivables_sp, Line 206
The column prefix 'a' does not match with a table name or alias name used in the query.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17044064
ok. line 206 seems to be the the below query.
can you try to remove the lines I commented in the query to see if those are the "culprit".

also, which version of sql server?

--report selection
insert into #finalvalues
select distinct
     t.company_id,
     t.max(expiration_date) as expiredcredit_limit,
     t.e_code,
     a.customer_code,
     c.customer_name,
     a.doc_ctrl_num,
     a.apply_to_num,
     a.apply_trx_type,
     a.date_doc,
     a.date_due,
     a.date_aging,
     a.date_applied,
     a.amount,
     a.salesperson_code,
     a.territory_code,
     a.price_code,
     a.cust_po_num,
     a.order_ctrl_num,
     a.trx_type,
     a.trx_ctrl_num,
     a.trx_type_code,
     a.ref_id,
       a.nat_cur_code,
       a.rate_oper,
      g.oper_currency,
       a.divide_flag,
      g.company_name,
       @dateto_jul as dateto_jul,
       c.credit_limit,
--     isNull((select sum(amount) from core..v_company_additional_credit where epicor_db_name = 'MWHydrocarbon' and status = 'A' and @dateto between effective_date and expiration_date and epicor_code + 'AR' = a.customer_code), 0) as additional_credit,
--     case when (@dateto_jul - a.date_aging) between 30 and 60 then amount else 0 end as Age30,
--     case when (@dateto_jul - a.date_aging) between 60 and 90 then amount else 0 end as Age60,
--     case when (@dateto_jul - a.date_aging) > 90 then amount else 0 end as Age90
from
  glco g,
  #artrxage_tmp a,
  core..company_additional_credit t
  inner join arcust c on a.customer_code = c.customer_code
  inner join #temp2 x on x.c_id = t.company_id
  Group by company_id, e_Code
0
 

Author Comment

by:DancingFighterG
ID: 17044243
No, that is not the problem. I still get the same error. I had this working for some information. What I added was the following:

1.
core..company_additional_credit t
inner join #temp2 x on x.c_id = t.company_id
Group by company_id, e_Code

2.
create table #temp1
(
     code varchar(20),
)

create table #temp2
(
     c_id varchar(20),    
     e_code varchar(20),
     code varchar(20)
)

3.
t.company_id,
t.max(expiration_date) as expiredcredit_limit,
t.e_code

4.
insert into #temp1
select replace(replace(left(customer_code , 15 ), 'AR', ''), 'CUS', '')
from artrxage

5.
insert into #temp2
select company_id, epicor_code, code from core..company_epicor_xref b
inner join #temp1 u on u.code = b.epicor_code
order by epicor_code

Now, everything still worked ok until I put in:

core..company_additional_credit t
inner join #temp2 x on x.c_id = t.company_id
Group by company_id, e_Code

this is when I get the error about the prefix error

0
 

Author Comment

by:DancingFighterG
ID: 17044423
Also, I'm using Microsoft SQL 2000. Sorry about that!!
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17044445
any better with this:

<...>
from glco g
  cross join #artrxage_tmp a
  cross join core..company_additional_credit t
  inner join arcust c on a.customer_code = c.customer_code
  inner join #temp2 x on x.c_id = t.company_id
  Group by company_id, e_Code
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17044461
Also:

insert into #finalvalues
select  < many columns >
<...>
Group by company_id, e_Code

this is not allowed. you have to put each column that you want in the select list, that is without aggregate function, also into the group by clause...
0
 

Author Comment

by:DancingFighterG
ID: 17044613
ok, it solved the prefix error but now I get the following:

Server: Msg 207, Level 16, State 3, Procedure mw_aragedreceivables_sp, Line 209
Invalid column name 'e_code'.
Server: Msg 209, Level 16, State 1, Procedure mw_aragedreceivables_sp, Line 209
Ambiguous column name 'amount'.
Server: Msg 209, Level 16, State 1, Procedure mw_aragedreceivables_sp, Line 209
Ambiguous column name 'amount'.
Server: Msg 209, Level 16, State 1, Procedure mw_aragedreceivables_sp, Line 209
Ambiguous column name 'amount'.
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17044688
>Ambiguous column name 'amount'.
these indicate that you have that column name in several tables. you have to prefix the column name by one of the table names/aliases to get rid of that error

>Invalid column name 'e_code'.
well, the error speaks for itself I would say...
0
 

Author Comment

by:DancingFighterG
ID: 17045371
ok, well it compiles and runs but it's getting hung up. The query is thinking for about 4 min without any result coming out.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17045388
>ok, well it compiles and runs
so, primary issue solved

>but it's getting hung up.
>The query is thinking for about 4 min without any result coming out.
well, that get's a tuning issue, for sure you miss some indexes.

now, how many rows do you get in the table you are doing a CROSS JOIN on?
say, 100 in all 3 and you have 100x100x100 makes 1 Million rows to be returned...
0
 

Author Comment

by:DancingFighterG
ID: 17045523
Well, I have about over 500 rows. Lot's of information. Question, what is the benefit of a cross join. i don't use cross joins much.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17045546
if you don't have a condition linking rows together between 2 tables, it's a CROSS JOIN:
select * from tableA, tableB
is the same as
select * from tableA CROSS JOIN tableB

0
 

Author Comment

by:DancingFighterG
ID: 17045755
So when we said

  cross join #artrxage_tmp a
  cross join core..company_additional_credit t

what is the other table that we cross joining too? Does this process slow down computation time?
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17045783
from glco g
  cross join #artrxage_tmp a
  cross join core..company_additional_credit t

you are actually cross joining 3 tables here: glco, @artrxage_tmp and core..company_additional_credit
0
 

Author Comment

by:DancingFighterG
ID: 17045910
The execution time is already at 30 minutes. any suggesions on another way to get this work and still avoid the prefix problem that I was getting. This takes to long to execute.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17045987
>Well, I have about over 500 rows
As I explained above, 500x500x500 will be some 62 millon rows produced.
you probabyl don't want to get that many rows as output, so your query is wrong, at least missing some conditions to join the tables. Adding the join condition (by changing the cross join into inner join) should help
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17045992
PS: this is no longer the initial issue (which was a pure syntax issue), here you are in query logic issue.
0
 

Author Comment

by:DancingFighterG
ID: 17046357
Cool, thanks man!
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now