Extra spaces in SQL statement after visualizing

Toad Data Point & Toad Intelligence Central
1

In TDP 5.0 when I modify a query and select 'Visualize the Statement in Query Tab", the SQL statement refreshes and there are spaces in between the table alias and column names near the end of the SQL statement (see below). For example, the column "AL1.PROJ_ID" is displayed as "AL1 . PROJ_ID" in the GROUP BY section (extra spaces before and after the "." delimiter).

SELECT AL1.INVT_ABBRV_CD,
SUBSTRING (AL1.INVT_ABBRV_CD, 4, 3)
AS INV_TYPE,
AL1.PROJ_ID,
AL1.PART_KEY,
AL1.PART_ID,
AL4.ITEM_DESC,
AL1.INVT_DT,
AL5.S_MAKE_BUY_CD,
AL1.PART_RVSN_ID,
MAX (AL2.RECPT_DT)
AS RECEPT_DT,
ISNULL (MAX (AL2.RECPT_DT), AL1.LAST_RECPT_DT)
AS RECPT_DT_CALC,
AL1.LAST_ISS_DT,
AL1.LAST_RECPT_DT,
AL1.LAST_ADJ_DT,
AL1.YTD_USAGE_QTY,
AL1.LAST_YR_USAGE_QTY,
AL1.INCEPT_TO_DT_QTY,
AL5.S_STATUS_TYPE,
AL1.ON_HAND_QTY,
AL1.ON_HAND_QTY * (AL1.MATL_CST_AMT + AL1.SUBCT_CST_AMT)
AS ON_HAND_EXT_AMT,
AL1.ON_ORD_QTY,
AL1.RES_QTY,
AL1.ON_RQ_QTY,
AL1.IN_INSP_QTY,
AL1.IN_MRB_QTY,
AL1.ON_HOLD_QTY,
AL1.ON_HOLD_QTY * (AL1.MATL_CST_AMT + AL1.SUBCT_CST_AMT)
AS ON_HOLD_EXT_AMT,
AL1.IN_SHIP_QTY,
AL1.MODIFIED_BY,
AL1.TIME_STAMP,
AL1.COMPANY_ID,
AL1.OWED_QTY,
AL1.MATL_CST_AMT,
AL1.LAB_CST_AMT,
AL1.SUBCT_CST_AMT,
AL1.MISC1_CST_AMT,
AL1.MISC2_CST_AMT,
AL1.MATL_BURD_CST_AMT,
AL1.LAB_BURD_CST_AMT,
AL1.SUBCT_BURD_CST_AMT,
AL1.MISC1_BURD_CST_AMT,
AL1.MISC2_BURD_CST_AMT,
AL1.PLND_RES_QTY,
AL1.PLND_OWED_QTY,
AL1.EXPDT_TX,
AL1.AVG_LABOR_HRS,
AL1.AVG_SUBCT_HRS,
AL1.ROWVERSION
FROM DELTEKCP.DELTEK.PART AL5
CROSS JOIN
(
( (DELTEKCP.DELTEK.RECPT_LN AL3
CROSS JOIN DELTEKCP.DELTEK.RECPT_HDR AL2 ON ( AL3 . RECPT_KEY = AL2 . RECPT_KEY))
RIGHT OUTER JOIN DELTEKCP.dbo.INVT_HIST AL1
ON (AL1.PART_KEY = AL3.ITEM_KEY))
LEFT OUTER JOIN DELTEKCP.DELTEK.ITEM AL4
ON (AL1.PART_KEY = AL4.ITEM_KEY)
AND (AL1.PART_ID = AL4.ITEM_ID)
AND (AL1.PART_RVSN_ID = AL4.ITEM_RVSN_ID) )
LEFT OUTER JOIN
DELTEKCP . dbo . INVT_WHSE_LOC_HIST AL6
ON ( AL1 . INVT_ABBRV_CD = AL6 . INVT_ABBRV_CD ) AND ( AL1 . PART_KEY = AL6 . PART_KEY )
WHERE ( ( ( ( AL1 . PART_KEY = AL5 . PART_KEY AND AL1 . INVT_DT = AL6 . INVT_DT ) AND ( AL1 . ON_HAND_QTY <> 0 OR AL1 . ON_HOLD_QTY <> 0 ) ) AND AL1 . INVT_DT = :INVT_DT ) AND SUBSTRING ( AL1 . INVT_ABBRV_CD , 4 , 3 ) <> 'GFE'
)
GROUP BY AL1 . INVT_ABBRV_CD
, SUBSTRING ( AL1 . INVT_ABBRV_CD , 4 , 3 )
, AL1 . PROJ_ID
, AL1 . PART_KEY
, AL1 . PART_ID
, AL4 . ITEM_DESC
, AL1 . INVT_DT
, AL5 . S_MAKE_BUY_CD
, AL1 . PART_RVSN_ID
, AL1 . LAST_ISS_DT
, AL1 . LAST_RECPT_DT
, AL1 . LAST_ADJ_DT
, AL1 . YTD_USAGE_QTY
, AL1 . LAST_YR_USAGE_QTY
, AL1 . INCEPT_TO_DT_QTY
, AL5 . S_STATUS_TYPE
, AL1 . ON_HAND_QTY
, AL1 . ON_ORD_QTY
, AL1 . RES_QTY
, AL1 . ON_RQ_QTY
, AL1 . IN_INSP_QTY
, AL1 . IN_MRB_QTY
, AL1 . ON_HOLD_QTY
, AL1 . IN_SHIP_QTY
, AL1 . MODIFIED_BY
, AL1 . TIME_STAMP
, AL1 . COMPANY_ID
, AL1 . OWED_QTY
, AL1 . MATL_CST_AMT
, AL1 . LAB_CST_AMT
, AL1 . SUBCT_CST_AMT
, AL1 . MISC1_CST_AMT
, AL1 . MISC2_CST_AMT
, AL1 . MATL_BURD_CST_AMT
, AL1 . LAB_BURD_CST_AMT
, AL1 . SUBCT_BURD_CST_AMT
, AL1 . MISC1_BURD_CST_AMT
, AL1 . MISC2_BURD_CST_AMT
, AL1 . PLND_RES_QTY
, AL1 . PLND_OWED_QTY
, AL1 . EXPDT_TX
, AL1 . AVG_LABOR_HRS
, AL1 . AVG_SUBCT_HRS
, AL1 . ROWVERSION

2

Can you load this SQL in the query Builder and while keeping the Query Builder focused, generate a support bundled from the Help menu? Then email the support bundle to debbie.peabody@quest.com. The support bundle should attach to an email but if not it is the zip file located in your app data dir. Similar to the path below. Please add url to this forum post when sending.
C:\Users\dpeabody\AppData\Roaming\Quest Software\Toad Data Point 5.0

3

I just sent the support bundle you requested. I ran this prior to modifying the query. After making a slight change to the query and hitting visualize then the query gets updated with some of the extra spaces between the table alias
and column names as shown in my first post to this topic. I tried to run the support bundle after modifying the query but am getting this error:

image001.png890×284 11.3 KB

4

Any updates on this issue?

5

It looks like the SQL Parser is having trouble with the SQL and did not generate the SQLAnalyze.log file I need. This file is to take the sql and generate DDl for the tables so I can create the tables and run the SQL.

Since this didn’t work can you send me the SQL as well as the table DDL? You can get the DDL from the Script tab of the View details. Or if that doesn’t work can you send me screenshots of the column tab of each table in your SQL.

6

This issue has been entered as QAT-14816