Issue 104961 - Database query design view generates extra unusable SQL text
Summary: Database query design view generates extra unusable SQL text
Status: CLOSED NOT_AN_OOO_ISSUE
Alias: None
Product: Base
Classification: Application
Component: ReportBuilder (show other issues)
Version: OOo 3.1.1
Hardware: PC All
: P3 Trivial (vote)
Target Milestone: ---
Assignee: dbaneedsconfirm
QA Contact: issues@dba
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2009-09-10 17:23 UTC by karlwescott
Modified: 2009-09-22 20:56 UTC (History)
1 user (show)

See Also:
Issue Type: DEFECT
Latest Confirmation in: ---
Developer Difficulty: ---


Attachments

Note You need to log in before you can comment on or make changes to this issue.
Description karlwescott 2009-09-10 17:23:49 UTC
In attempting to write a query using the design view feature text in included 
and in previewing and error message is generated "unexpected as".

Design view flawed SQL text:
SELECT "ItemNumber" AS "ItemNumber", "ItemDescription" 
AS "ItemDescription", "StdUM" AS "StdUM", "PurchaseUM" 
AS "PurchaseUM", "SalesUM" AS "SalesUM", "PrimaryVendorNumber" 
AS "PrimaryVendorNumber", "StdPrice" AS "StdPrice", "StdCost" AS "StdCost" 
FROM "IM1_InventoryMasterfile" AS "IM1_InventoryMasterfile"

Note the extra 'AS "IM1_InventoryMasterfile"' at the end.

Switching to SQL mode and removing the extra text results in a usable query 
preview:
SELECT "ItemNumber" AS "ItemNumber", "ItemDescription" 
AS "ItemDescription", "StdUM" AS "StdUM", "PurchaseUM" 
AS "PurchaseUM", "SalesUM" AS "SalesUM", "PrimaryVendorNumber" 
AS "PrimaryVendorNumber", "StdPrice" AS "StdPrice", "StdCost" AS "StdCost" 
FROM "IM1_InventoryMasterfile"

Switching back to design view recreates the flawed text.
Comment 1 Frank Schönheit 2009-09-11 08:21:24 UTC
You did not mention which database you connect to, by which means.

In general, what OOo generates here is perfectly valid SQL, it is assigning an
alias name to a table. However, some databases either do not support this at
all, or only without the "AS" keyword.

For both cases, try disabling the "Use keyword 'AS' before table alias name"
respectively "Append the table alias name on SELECT statements" options in
"Edit/Database/Advanced Settings".
Comment 2 karlwescott 2009-09-11 16:18:50 UTC
The query was to an ODBC.

Disabling the "Use keyword 'AS' before table alias name"
respectively "Append the table alias name on SELECT statements" options in
"Edit/Database/Advanced Settings" resolved the issue.

Perhaps this issue should be referred to a documentation/fac team.

THANK YOU FOR YOUR HELP AND PROMPT RESPONSE!
Comment 3 Mechtilde 2009-09-22 20:56:08 UTC
invalid -> closed