Help with this code - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with this code

Author

Topic

jaimealvarez
Starting Member

31 Posts

Posted - 2012-04-24 : 11:15:00

I have to sql codes that work fine separetely. I want to see if there is a way to join them:

Code 1:

SELECT tindex,
STUFF((SELECT ' ' + tddesc FROM son_db.dbo.timedesc WHERE tindex = t.tindex ORDER BY tdline FOR XML PATH('')),1,1,'') AS [tddesc]

FROM (SELECT DISTINCT tindex FROM son_db.dbo.timedesc)t

Code 2:

SELECT
client.clnum AS 'Client'
, matter.mmatter AS 'Matter'
, timecard.ttk AS 'TTKID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'
, timekeep.tktitle AS 'Title'
, timecard.tworkdt AS 'WorkedDate'
, timecard.tworkhrs AS 'Hours'
, timecard.tworkdol AS 'WorkedAmount'
, timecard.tstatus AS 'Status'
, timecard.tinvoice AS 'Invoice'
, matter.mcurrency AS 'Currency'
, x.tddesc AS 'DiaryEntry'

FROM
son_db.dbo.client client
, son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep
, X

WHERE client.clnum = matter.mclient
AND matter.mmatter = timecard.tmatter
AND timecard.ttk = timekeep.tkinit
AND timecard.tindex = x.tindex
AND ((timecard.tprntfl Not In ('R','X'))
AND (timecard.tstatus<>'ADE, D, E, NB, NBP'))
AND (matter.mmatter Like ?)
AND (timecard.ttk Like ?)
AND (timecard.tworkdt>=?
And timecard.tworkdt<=?))

the joint field will be timecard.tindex (from 2nd code) and tindex (from 1st code).

Any way to combine these?

Any help will be appreciated.
Thanks

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-04-24 : 11:28:28

You'll have to add tindex to your second query, but here is a generic outline for you to combine them (assuming you want ot join them):

SELECT *
FROM
(
	-- QUery 1
) AS A
INNER JOIN
(
	-- Query 2
) AS B
ON A.tindex = B.tindex

jaimealvarez
Starting Member

31 Posts

Posted - 2012-04-24 : 11:47:22

Thanks for the help.
I tried this:

SELECT *
FROM
(
SELECT tindex,
STUFF((SELECT ' ' + tddesc FROM son_db.dbo.timedesc WHERE tindex = t.tindex ORDER BY tdline FOR XML PATH('')),1,1,'') AS [tddesc]

FROM (SELECT DISTINCT tindex FROM son_db.dbo.timedesc)t
)
AS A

INNER JOIN

(
SELECT
client.clnum AS 'Client'
, matter.mmatter AS 'Matter'
, timecard.ttk AS 'TTKID'
, timekeep.tklast + ', ' + timekeep.tkfirst AS 'Timekeeper'
, timekeep.tktitle AS 'Title'
, timecard.tworkdt AS 'WorkedDate'
, timecard.tworkhrs AS 'Hours'
, timecard.tworkdol AS 'WorkedAmount'
, timecard.tstatus AS 'Status'
, timecard.tinvoice AS 'Invoice'
, matter.mcurrency AS 'Currency'
, timecard.tindex AS 'Index'

FROM
son_db.dbo.client client
, son_db.dbo.matter matter
, son_db.dbo.timecard timecard
, son_db.dbo.timekeep timekeep

WHERE
client.clnum = matter.mclient
AND matter.mmatter = timecard.tmatter
AND timecard.ttk = timekeep.tkinit
AND ((timecard.tprntfl Not In ('R','X'))
AND (timecard.tstatus<>'ADE, D, E, NB, NBP'))
)
AS B
ON A.tindex = B.tindex

But I get an error: "Could not add the table '('."
Any ideas?
Thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-04-24 : 21:30:55

where are you using this query? is it inside some application? what corresponds to '?' are they parameter place holders?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources