Problem with Insert in sub query

[Palmyra]

Is there something simple that I'm missing in the below insert statement (with a subquery)? Or am I way off?

I'm getting an error:
Incorrect syntax near ')'

which is on the last line

INSERT INTO insaudit (
insafindex,
insaiindex,
insadate,
insatime,
insacrop,
insafunction,
insaitem,
insatodata
)
SELECT dm_HRB_Legacy_Box_Conversion_hits.recindex,
f.recid,
convert(varchar(10), getdate(), 101),
convert(varchar(10), getdate(), 108),
'HRBConv' as insacrop,
'M' as insafunction,
3 as insaitem,
dm_HRB_Legacy_Box_Conversion_hits.new_curr_location
FROM dm_HRB_Legacy_Box_Conversion_hits
INNER JOIN dm_HRB_Legacy_Box_Conversion
ON dm_HRB_Legacy_Box_Conversion_hits.HRB_recid = dm_HRB_Legacy_Box_Conversion.recid
INNER JOIN

(
Select
f.recid
from dm_HRB_Legacy_Box_Conversion_hits f
INNER JOIN dm_HRB_Legacy_Box_Conversion
ON f.HRB_recid = dm_HRB_Legacy_Box_Conversion.recid
WHERE (f.type = 'I')
AND (dm_HRB_Legacy_Box_Conversion.process_number = '1'))

 

[bborissov]

INNER JOIN What?
Where if the table alias of derived table and how you JOIN it?

INNER JOIN (Select f.recid
                   from dm_HRB_Legacy_Box_Conversion_hits f
            INNER JOIN dm_HRB_Legacy_Box_Conversion
                  ON f.HRB_recid = dm_HRB_Legacy_Box_Conversion.recid
            WHERE (f.type = 'I') AND
       (dm_HRB_Legacy_Box_Conversion.process_number = '1')) [COLOR=red][b]TableAlias[/b][/color]
[COLOR=red][b]ON dm_HRB_Legacy_Box_Conversion_hits.???? =
                 TableAlias.?????[/b][/color]

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Palmyra]

Well, I guess the answer is that I don't know enough to know how to do this.

 

[bborissov]

Palmyra,
Thread derived tables like any other ones, only they need to specify their alias and that is mandatory for them, if you use them in JOINS.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Ach005ki]

People, please don't flame me if I'm wrong but isn't it this part which is missing the Inner Join info?

...
FROM        dm_HRB_Legacy_Box_Conversion_hits
INNER JOIN  dm_HRB_Legacy_Box_Conversion
ON            dm_HRB_Legacy_Box_Conversion_hits.HRB_recid = dm_HRB_Legacy_Box_Conversion.recid
[COLOR=yellow blue]INNER JOIN[/color]

[COLOR=red yellow]  next part is your derived table  [/color]
    (
    Select  
        f.recid
        from dm_HRB_Legacy_Box_Conversion_hits f
INNER JOIN dm_HRB_Legacy_Box_Conversion
ON    f.HRB_recid = dm_HRB_Legacy_Box_Conversion.recid
WHERE    (f.type = 'I')
        AND (dm_HRB_Legacy_Box_Conversion.process_number = '1'))[COLOR=red yellow][i] give your derived table an alias [/i][/color]
[COLOR=yellow blue]ON [/color][COLOR=red yellow]give your derived-table join at least one condition such as...[/color][COLOR=yellow blue][i]join-table.field[/i] = [i]derived-table-alias.field[/i][/color]

Mark, somewhere near Blackburn Lancs!

 

[SQLSister]

yep that would be it, ach005ki


"NOTHING is more important in a database than integrity." ESquared