UNION subquery in FROM Clause

[dkillilea]

I have two tables (historical and live). I need to combine them and and use max(period) to keep only the most recent period the tag existed.

SQl Server keeps telling me I have incorrect syntax by GROUP.

Am I doing something wrong? Can I even do a union subquery in the FROM clause? Please help.


SELECT
MAX([PERIOD]),[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],
[REF_ITEM],[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],
[LENGTH],[POUNDS]

FROM
(SELECT * FROM INTTTR UNION SELECT * FROM HISTORICAL_INTTTR)

GROUP BY
[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],[REF_ITEM],
[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],[LENGTH],[POUNDS]


Thanks,
Doug

 

[AlexCuse]

You need to give the union query an alias:

SELECT 
MAX([PERIOD]),[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],
[REF_ITEM],[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],
[LENGTH],[POUNDS]

FROM 
(SELECT * FROM INTTTR UNION SELECT * FROM HISTORICAL_INTTTR) [b]A[/b]

GROUP BY
[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],[REF_ITEM],
[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],[LENGTH],[POUNDS]

Hope tis helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[sunila7]

Try this query instead. Derived table is not aliased...

SELECT
MAX([PERIOD]),[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],
[REF_ITEM],[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],
[LENGTH],[POUNDS]

FROM
(SELECT * FROM INTTTR UNION SELECT * FROM HISTORICAL_INTTTR) TBL

GROUP BY
[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],[REF_ITEM],
[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],[LENGTH],[POUNDS]

Sunil

 

[dkillilea]

Thank You!!!

My max([PERIOD]) is not removing duplicates.
HISTORICAL_INTTTR = 244,326 rows
INTTTR = 105,346
I should get a new table with 249,479 rows after duplicates are removed by grabbing the most recent, or max period, for each inventory tag.

HISTORICAL_INTTTR was imported and each column CAST exactly like table INTTTR which came from a text file.

I noticed that when I choose "open table" from Management Studio i see padding in the form of a square on the end data in some cells. Again I know I cast them exactly the same. Is there somehting you would suggest investigating further.

Thank you so much for your help!!!!!
-Doug

 

[gmmastros]

That square bracket that you see is probably a non-printable character like Carriage Return, Line Feed, or the tab character. You can still do this, but it's gonna be ugly. Essentially, you won't be able to select * anymore. You'll need to do something like this...

SELECT
MAX([PERIOD]),[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],
[REF_ITEM],[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],
[LENGTH],[POUNDS]

FROM
(
SELECT Replace([PERIOD], Char(9), '') As Period,
       Replace([TAG_CONTROL], Char(9), '') As TAG_CONTROL,
       Replace([BRANCH], Char(9), '') As BRANCH,
       Replace([REF_PFX], Char(9), '') As REF_PFX,
       Replace([REF_NUM], Char(9), '') As REF_NUM,
       Replace([REF_ITEM], Char(9), '') As REF_ITEM,
       Replace([TAG_NUM], Char(9), '') As TAG_NUM,
       Replace([GRP], Char(9), '') As GRP,
       Replace([GAUGE], Char(9), '') As GUAGE,
       Replace([GRADE], Char(9), '') As GRADE,
       Replace([WIDTH], Char(9), '') As WIDTH,
       Replace([LENGTH], Char(9), '') As LENGTH,
       Replace([POUNDS],  Char(9), '') As POUNDS 
FROM   INTTTR 

UNION 

SELECT Replace([PERIOD], Char(9), '') As Period,
       Replace([TAG_CONTROL], Char(9), '') As TAG_CONTROL,
       Replace([BRANCH], Char(9), '') As BRANCH,
       Replace([REF_PFX], Char(9), '') As REF_PFX,
       Replace([REF_NUM], Char(9), '') As REF_NUM,
       Replace([REF_ITEM], Char(9), '') As REF_ITEM,
       Replace([TAG_NUM], Char(9), '') As TAG_NUM,
       Replace([GRP], Char(9), '') As GRP,
       Replace([GAUGE], Char(9), '') As GUAGE,
       Replace([GRADE], Char(9), '') As GRADE,
       Replace([WIDTH], Char(9), '') As WIDTH,
       Replace([LENGTH], Char(9), '') As LENGTH,
       Replace([POUNDS],  Char(9), '') As POUNDS 
FROM   HISTORICAL_INTTTR) TBL

GROUP BY
[TAG_CONTROL],[BRANCH],[REF_PFX],[REF_NUM],[REF_ITEM],
[TAG_NUM],[GRP],[GAUGE],[GRADE],[WIDTH],[LENGTH],[POUNDS]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dkillilea]

Thanks George. Most of my fields are varchar, so I'll try to see if I can use replace or some other function to trim.

I only see this padding on Management Studio when I open table, not when I run a select and view in query analyzer.

The little square I see is coming from INTTTR. I am only seeing it on varchar fields.

Also, my INTTTR is imported and created from two tables (so there's a join involved). It seems that I can manipulate what columns get the square padding by switching the join. Have you seen this before? Any suggestions for el;imiating this problem from the root cause here?

Thank you all for you're help!!! This helps me more than you can impagine. It's such a pain when your backend is basically a text file that can't be shema bound for indexing.

 

[gmmastros]

From the sounds of it, you should be able to eliminate the problem before it becomes a problem. Without seeing the query, it's hard to say.

Query Analyzer won't show the special characters at all.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dkillilea]

IF OBJECT_ID('INTTTR')
IS NOT NULL
DROP TABLE INTTTR

CREATE TABLE INTTTR
(
[PERIOD] VARCHAR(6),
[TAG_CONTROL] NUMERIC(10,0),
[BRANCH] VARCHAR(3),
[REF_PFX] VARCHAR(2),
[REF_NUM] INTEGER,
[REF_ITEM] SMALLINT,
[TAG_NUM] VARCHAR(7),
[GRP] VARCHAR(6),
[GAUGE] VARCHAR(12),
[GRADE] VARCHAR(6),
[WIDTH] NUMERIC (9,4),
[LENGTH] NUMERIC (9,4),
[POUNDS] BIGINT
)

INSERT INTO INTTTR
SELECT
CAST(B.CAP_CUR_PER_AP AS VARCHAR(6)) AS PERIOD,
CAST(A.TTR_TAG_CT2_NO AS NUMERIC(10,0)) AS TAG_CONTROL,
CAST(A.TTR_SSN_BRH AS VARCHAR(3)) AS BRANCH,
CAST(A.TTR_REF_PFX AS VARCHAR(2)) AS REF_PFX,
CAST(A.TTR_REF_NO AS INTEGER) AS REF_NUM,
CAST(A.TTR_REF_ITM AS SMALLINT) AS REF_ITEM,
CAST(A.TTR_TAG_NO1 AS VARCHAR(7)) AS TAG_NUM,
CAST(A.TTR_PRG AS VARCHAR(6)) AS [GRP],
CAST(A.TTR_SIZE AS VARCHAR(12)) AS GAUGE,
CAST(A.TTR_GRD AS VARCHAR(6)) AS GRADE,
CAST(A.TTR_WDTH AS NUMERIC (9,4)) AS WIDTH,
CAST(A.TTR_LGTH AS NUMERIC (9,4)) AS LENGTH,
CAST(A.TTR_WGT AS BIGINT) AS POUNDS
FROM MAJESTI..root.INTTTR A
JOIN MAJESTI..root.SCRCAP B
ON CAST(A.TTR_REF_PFX AS VARCHAR(2))='RC'


Please note that:
MAJESTI..root.SCRCAP only has one row. I'm using it to grab the current period

BRANCH, TAG_NUMBER, GRP, GAUGE are all coming back with the padding.

 

[gmmastros]

I dunno. This is weird. Instead of doing a join to get that 1 value, you could always create a variable, and then use the variable later. Like this...

Declare @CAP_CUR_PER_AP VarChar(6)

Select  @CAP_CUR_PER_AP = CAP_CUR_PER_AP 
From    MAJESTI..root.SCRCAP 
Where   CAST(A.TTR_REF_PFX AS VARCHAR(2))='RC'

INSERT INTO INTTTR
SELECT 
    [!]@CAP_CUR_PER_AP[/!] AS PERIOD,
    CAST(A.TTR_TAG_CT2_NO AS NUMERIC(10,0)) AS TAG_CONTROL,
    CAST(A.TTR_SSN_BRH AS VARCHAR(3)) AS BRANCH,
    CAST(A.TTR_REF_PFX AS VARCHAR(2)) AS REF_PFX,
    CAST(A.TTR_REF_NO AS INTEGER) AS REF_NUM,
    CAST(A.TTR_REF_ITM AS SMALLINT) AS REF_ITEM,
    CAST(A.TTR_TAG_NO1 AS VARCHAR(7)) AS TAG_NUM,
    CAST(A.TTR_PRG AS VARCHAR(6)) AS [GRP],
    CAST(A.TTR_SIZE AS VARCHAR(12)) AS GAUGE,
    CAST(A.TTR_GRD AS VARCHAR(6)) AS GRADE,
    CAST(A.TTR_WDTH AS NUMERIC (9,4)) AS WIDTH,
    CAST(A.TTR_LGTH AS NUMERIC (9,4)) AS LENGTH,
    CAST(A.TTR_WGT AS BIGINT) AS POUNDS
FROM MAJESTI..root.INTTTR A

I doubt this will makes things any better, but I suppose it's worth a try.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[dkillilea]

I'll give that a shot, but I've had this same exact problem accross the board with every query (with a join) I've built. It may seem that the one value, period, is perhaps creating the problem. But I've done joins on a couple different table and noticed this. Thanks again for all your help. Until I solve this problem I pretty much can't do joins in a useful way for our front end users in Access.