We help IT Professionals succeed at work.

Query syntax problem

212 Views
Last Modified: 2008-02-01
Hello, i'm trying to write this sql command with text and I'm getting an error:

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'a'.

Here is my code:

select @sql = 'select "' + @@servername + '" as "ServerName", convert(char(32), "' + @db_name + '") as "DatabaseName", journal_type, journal_ctrl_num, journal_description, date_entered, date_applied, recurring_flag, repeating_flag, reversing_flag, hold_flag, posted_flag, date_posted, source_batch_code, batch_code, type_flag, intercompany_flag, company_code, app_id, home_cur_code, document_1, trx_type, user_id, source_company_code, process_group_num, oper_cur_code '
      select @sql = @sql + 'from [' + @db_name + ']..gltrx a'
      select @sql = @sql + 'where a.date_entered between "' + convert(varchar, @from_date) + '" and "' + convert(varchar, @to_date) + '" '
      select @sql = @sql + 'and gltrx not in ("__NDES__", "afe", "BNA_Appalachia", "BNA_Basin", "BNA_Hydrocarbon", "BNA_JavelinaCompany", "BNA_JavelinaPipeline", "BNA_Michigan", "BNA_MichiganPipeline", "BNA_MichiganPipeline-FERC", "BNA_MLP", "BNA_Westshore", "CitrixIMA", "coffice", "core", "Epicor_Analytics", "ExpressOptions_MWE", "ExpressOptions_MWP", "master", "MAXIMO", "MAXTEST", "model", "msdb", "MWIntranet", "pipelines", "PLTMASTER", "pricing", "producer", "production", "RAM", "RiskAssetManager", "sample", "SQLAudit", "stellent", "standard", "temp_PLTMASTER", "TempChartConvert", "tempdb", "transfer", "workflow"'
      select @sql = @sql + 'order by date_entered DESC'
      Print '********************************************************************************************************************************'
      Print @db_name
      exec (@sql)

Comment
Watch Question

CERTIFIED EXPERT
Most Valuable Expert 2015
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Ok, I'm gettig two more errors:

Invalid object name '__NDES__..gltrx'.
Invalid column name 'gltrx'.

The first errors is because the table that I am calling is not in this database so I wrote a clause where it would:

select @sql = @sql + 'and gltrx not in ("__NDES__", "afe", "BNA_Appalachia", "BNA_Basin", "BNA_Hydrocarbon", "BNA_JavelinaCompany", "BNA_JavelinaPipeline", "BNA_Michigan", "BNA_MichiganPipeline", "BNA_MichiganPipeline-FERC", "BNA_MLP", "BNA_Westshore", "CitrixIMA", "coffice", "core", "Epicor_Analytics", "ExpressOptions_MWE", "ExpressOptions_MWP", "master", "MAXIMO", "MAXTEST", "model", "msdb", "MWIntranet", "pipelines", "PLTMASTER", "pricing", "producer", "production", "RAM", "RiskAssetManager", "sample", "SQLAudit", "stellent", "standard", "temp_PLTMASTER", "TempChartConvert", "tempdb", "transfer", "workflow") '
but it seems that this is not working or do I need to do this a different way.

The second error seems to be trying to view gltrx as a column rather then a table.

Need some help with this.
CERTIFIED EXPERT
Most Valuable Expert 2015

Commented:
> Invalid object name '__NDES__..gltrx'.

The databaseName.owner.tableName shorthand should work.  I dont' know about db names that start with an underscore though.  You might also try using square brackets around the db name like this

        [__NDES__]..gltrx

> Invalid column name 'gltrx'.

You need to use the column name

AND ColumnName NOT IN (..... .list of values )

Author

Commented:
Well, I'm trying to exclude all databases that don't have the table gltrx from the select statement. So and columnName not in (....list of values) will not work.

Author

Commented:
Ok, figure out what I was doing wrong but now I have this problem:

Line 1: Incorrect syntax near 'a'.

on this piece of code:

select @sql = 'select "' + @@servername + '" as "ServerName", convert(char(32), "' + @db_name + '") as "DatabaseName", a.journal_type, a.journal_ctrl_num, a.journal_description, a.date_entered, a.date_applied, a.recurring_flag, a.repeating_flag, a.reversing_flag, a.hold_flag, a.posted_flag, a.date_posted, a.source_batch_code, a.batch_code, a.type_flag, a.intercompany_flag, a.company_code, a.app_id, a.home_cur_code, a.document_1, a.trx_type, a.user_id, a.source_company_code, a.process_group_num, a.oper_cur_code, b.balance, b.nat_balance, b.balance_oper, c.user_name '
        select @sql = @sql + 'from [' + @db_name + ']..gltrx a '
      select @sql = @sql + 'inner join gltrxdet b on b.journal_ctrl_num = a.journal_ctrl_num'
      select @sql = @sql + 'inner join pltmaster c on c.user_id = a.user_id'
        select @sql = @sql + 'where a.date_entered between "' + convert(varchar, @from_date) + '" and "' + convert(varchar, @to_date) + '" '
        select @sql = @sql + 'order by date_entered DESC'
      Print '********************************************************************************************************************************'
      Print @db_name
      exec (@sql)

Author

Commented:
Nevermind, figured it out. Last order of business is to convert julian dates to real dates.
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.