Edit Excel Query can't be represented graphically

[AGambhir]

Reference thread 68-1248326

A Query is created, and data is returned to Excel. However, when you edit the query, you are presented with the SQL statement. Click OK, and you can execute the query, but you can’t view or add tables, show or add criteria, etc. You can only edit the SQL statement, and if you do, you get the message that the SQL Query can’t be represented graphically.

Thanks for your help.

SQL Code below
SELECT CASE
WHEN
alt_map_acct_flat.level_4 <> ''
THEN
alt_map_acct_flat.level_4
ELSE
alt_map_acct_flat.level_3
END AS Classification,
gl_acct_summary.fiscal_year AS 'Year', gl_acct_summary.accounting_period AS ' Period', gl_acct_summary.co_code AS 'Company', gl_acct_summary.co_code+' '+co_info.co_name AS 'Company Name', co_info.co_base_currency_code AS 'Company Currency', alt_map_org_flat.level_4 AS 'Combined Org', gl_acct_summary.org_code AS 'Org', gl_acct_summary.org_code+' '+Organization.Name AS 'Org Name', alt_map_org_flat.level_1 AS 'Org Map Level 1', alt_map_org_flat.level_2 AS 'Org Map Level 2', alt_map_org_flat.level_3 AS 'Org Map Level 3', alt_map_org_flat.level_4 AS 'Org Map Level 4', alt_map_org_flat.level_5 AS 'Org Map Level 5', alt_map_acct_flat.acct_code AS 'Account Code', Account.Name AS 'Account Name', gl_acct_summary.db_amt_cc-gl_acct_summary.cr_amt_cc AS 'Actuals', 0 AS 'Plan', 0 AS 'Fcst', 'Actuals' AS 'DataType'
FROM Snap.dbo.Account Account, Snap.dbo.alt_map_acct_flat alt_map_acct_flat, Snap.dbo.alt_map_org_flat alt_map_org_flat, Snap.dbo.co_info co_info, Skip.dbo.gl_acct_summary gl_acct_summary, Snap.dbrganization Organization
WHERE gl_acct_summary.acct_code = alt_map_acct_flat.acct_code AND gl_acct_summary.co_code = alt_map_acct_flat.co_code AND Account.Code = gl_acct_summary.acct_code AND Account.CompanyCode = gl_acct_summary.co_code AND co_info.co_code = gl_acct_summary.co_code AND gl_acct_summary.co_code = Organization.CompanyCode AND gl_acct_summary.co_code = alt_map_org_flat.co_code AND gl_acct_summary.org_code = alt_map_org_flat.org_code AND gl_acct_summary.org_code = Organization.Code AND ((alt_map_acct_flat.map_code='200') AND (gl_acct_summary.fiscal_year>'2018') AND (gl_acct_summary.db_amt_cc-gl_acct_summary.cr_amt_cc<>0) AND (alt_map_org_flat.map_code='GBL')) and alt_map_acct_flat.level_3 = 'Indirect Costs' AND alt_map_org_flat.level_1 <> 'Corporate'

 

[SkipVought]

Hi,

“A Query is created, and data is returned to Excel.”

Where was the query created and run?

thread68-1248326

It seems like you’re using MS Query in Excel, querying an Oracle or DB2 database. I so, that’s what your stuck with. You must do ALL your SQL coding directly, without the aid of a graphic interface. I had to do this for decades, writing my own SQL for either Oracle, DB2, SQL Server...

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

You may be already doing it, but what helps me while building complex SQL statements is formatting the text so I can SEE what I am doing:

[pre]
SELECT
CASE WHEN alt_map_acct_flat.level_4 <> '' THEN alt_map_acct_flat.level_4
ELSE alt_map_acct_flat.level_3 END AS Classification,
gl_acct_summary.fiscal_year AS 'Year',
gl_acct_summary.accounting_period AS ' Period',
gl_acct_summary.co_code AS 'Company',
gl_acct_summary.co_code + ' ' + co_info.co_name AS 'Company Name',
co_info.co_base_currency_code AS 'Company Currency',
alt_map_org_flat.level_4 AS 'Combined Org',
gl_acct_summary.org_code AS 'Org',
gl_acct_summary.org_code + ' ' + Organization.Name AS 'Org Name',
alt_map_org_flat.level_1 AS 'Org Map Level 1',
alt_map_org_flat.level_2 AS 'Org Map Level 2',
alt_map_org_flat.level_3 AS 'Org Map Level 3',
alt_map_org_flat.level_4 AS 'Org Map Level 4',
alt_map_org_flat.level_5 AS 'Org Map Level 5',
alt_map_acct_flat.acct_code AS 'Account Code',
Account.Name AS 'Account Name',
gl_acct_summary.db_amt_cc - gl_acct_summary.cr_amt_cc AS 'Actuals',
0 AS 'Plan',
0 AS 'Fcst',
'Actuals' AS 'DataType'
FROM Snap.dbo.Account Account,
Snap.dbo.alt_map_acct_flat alt_map_acct_flat,
Snap.dbo.alt_map_org_flat alt_map_org_flat,
Snap.dbo.co_info co_info,
Skip.dbo.gl_acct_summary gl_acct_summary,
Snap.dbrganization Organization
WHERE gl_acct_summary.acct_code = alt_map_acct_flat.acct_code
AND gl_acct_summary.co_code = alt_map_acct_flat.co_code
AND Account.Code = gl_acct_summary.acct_code
AND Account.CompanyCode = gl_acct_summary.co_code
AND co_info.co_code = gl_acct_summary.co_code
AND gl_acct_summary.co_code = Organization.CompanyCode
AND gl_acct_summary.co_code = alt_map_org_flat.co_code
AND gl_acct_summary.org_code = alt_map_org_flat.org_code
AND gl_acct_summary.org_code = Organization.Code
AND ((alt_map_acct_flat.map_code = '200')
AND (gl_acct_summary.fiscal_year > '2018')
AND (gl_acct_summary.db_amt_cc-gl_acct_summary.cr_amt_cc <> 0)
AND (alt_map_org_flat.map_code = 'GBL'))
AND alt_map_acct_flat.level_3 = 'Indirect Costs'
AND alt_map_org_flat.level_1 <> 'Corporate'
[/pre]

I would use a lot shorter aliases for my tables, but that's just me....


---- Andy

There is a great need for a sarcasm font.

 

[dhookom]

You can format you SQL using on-line tools like SQLInForm. There are many tools that are way awesome!!!

Duane
Minnesota
Hook'D on Access
MS Access MVP 2001-2016

 

[AGambhir]

Thank you everyone.

 

[SkipVought]

@AGambhir, do you mind explaining what exactly helped you?

We have many members who browse threads looking for solutions. Your reply leaves that question open.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]