multivalue subquery in the on of a join

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

SQL Server 2005 Forums

Transact-SQL (2005)

multivalue subquery in the on of a join

Author

Topic

bpregler
Starting Member

11 Posts

Posted - 2008-05-13 : 17:07:51

I know I can't do this but I dont know will work. any help is much appreciated. the code below in red will always return more than one value, and yes the ?? is actually data (dont ask i didnt do it).

I am basically coding this because of the ?? someone before me thought was a good idea.

inner join MHDDTest.dbo.Admin_tbl_Vendor f on
(case when len(b.vadr) > 0 then
(case when (b.vadr = '??') then
(select vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)
else
b.vend + '-' + b.vadr
end)
else
b.vend
end
= f.vendorNumber)

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 17:09:04

You need to explain in words what you are trying to do.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

bpregler
Starting Member

11 Posts

Posted - 2008-05-13 : 17:14:31

I posted the whole query

DECLARE @ASTWIN_CIFLE TABLE(
cli_id decimal(8,0),
ci_ssn decimal(9,0))

DECLARE @ASTWIN_CRSVA_V TABLE(
SOCSECNO decimal(9,0),
UNITS decimal(8,2),
FM_VENDOR varchar(8),
FM_VENADR varchar(4),
AMT decimal(8,2),
OPN_DTE int,
CLS_DTE int,
SERV_CODE varchar(8))

DECLARE @ASTWIN_ITNOD TABLE(
nod_no int,
nod_code varchar(1),
auth_unit decimal(8,2),
auth_amt1 decimal(8,2),
auth_amt2 decimal(8,2),
open_dtnum int,
clse_dtnum int,
stat varchar(1),
vend varchar(8),
vadr varchar(4),
serv_code varchar(8),
ssn decimal(9,0))

DECLARE @ASTWIN_BLSCF_V TABLE(
serviceCode varchar(8),
xdesc nvarchar(max),
curr_serv varchar(8),
unit_code varchar(2),
act_cost decimal(8,2))

INSERT INTO @ASTWIN_CIFLE
SELECT cli_id, ci_ssn FROM [SV-DEVEL].[LT].[dbo].[ASTWIN_CIFLE]

INSERT INTO @ASTWIN_CRSVA_V
SELECT SOCSECNO,UNITS,FM_VENDOR,FM_VENADR,AMT,OPN_DTE,CLS_DTE,SERV_CODE
FROM [SV-DEVEL].[LT].[LINCINQ].[ASTWIN_CRSVA_V]
where DHRM_TYPE = 'CS'

INSERT INTO @ASTWIN_ITNOD
SELECT nod_no,nod_code, auth_unit,auth_amt1,auth_amt2,open_dtnum,clse_dtnum,stat,vend,vadr,serv_code,ssn
FROM [SV-DEVEL].[LT].[dbo].[ASTWIN_ITNOD]

INSERT INTO @ASTWIN_BLSCF_V
SELECT (substring(BILL_FNCT,3,2) + BILL_OBJT) as 'serviceCode',XDESC,CURR_SERV,UNIT_CODE,ACT_COST
FROM [SV-DEVEL].[LT].[LINCINQ].[ASTWIN_BLSCF_V]
order by curr_serv, serviceCode

SELECT a.cli_id as 'ClientID', b.serv_code
,case when nod_code in ('O','T') then 1
when nod_code = 'E' then 2
else
3
end as 'fundingRequestTypeID'
,e.serviceCategoryID,e.serviceID
,f.vendorID
,b.auth_unit as 'units'
,d.unit_code as 'unitType'
,d.ACT_COST
,b.AUTH_AMT1 as 'amt1'
,b.AUTH_AMT2 as 'amt2'
,dateadd(dd,OPEN_DTNUM, '1/1/1957') as 'startDate'
,dateadd(dd,CLSE_DTNUM, '1/1/1957') as 'endDate'
,case when stat = 'A' then 'Approve'
when stat = 'D' then 'Deny'
else
'Pend'
end as 'approveDeny'
from @ASTWIN_CIFLE a
inner join @ASTWIN_ITNOD b on b.ssn = a.ci_ssn
inner join @ASTWIN_CRSVA_V c on (c.SOCSECNO = a.ci_ssn and c.serv_code = b.serv_code)
inner join @ASTWIN_BLSCF_V d on d.curr_serv = c.serv_code
inner join MHDDTest.dbo.Admin_tbl_Service e on d.serviceCode = e.serviceCode

inner join MHDDTest.dbo.Admin_tbl_Vendor f on
(case when len(b.vadr) > 0 then
(case when (b.vadr = '??') then
(select vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)
else
b.vend + '-' + b.vadr
end)
else
b.vend
end
= f.vendorNumber)
order by a.cli_id, startDate desc

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-13 : 17:25:52

That does not help us help you. See my last post.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-13 : 17:55:01

Sure you can do that, kinda. If the sub query returns more than one row, which one do you want? Does it matter?

Maybe this will work for you?

quote:
Originally posted by bpregler

inner join MHDDTest.dbo.Admin_tbl_Vendor f on
(case when len(b.vadr) > 0 then
(case when (b.vadr = '??') then
(select TOP 1 vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)
else
b.vend + '-' + b.vadr
end)
else
b.vend
end
= f.vendorNumber)

bpregler
Starting Member

11 Posts

Posted - 2008-05-14 : 08:52:59

When vadr does equal "??" there are from 2 to 15 rows that will be returned by the sub query. I need the vendorNumber for all rows returned to be part of the result set.
so instead of 45678-?? and all the other data, i need to see:

45678-01 all other data
45678-04 all other data
45678-07 all other data

where all other data will be the same for each venderNumber. Hope this helps.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-05-14 : 12:49:53

It is probably good idea to supply some sampel data and expected results. Without any more information, here is a shot in the dark:

INNER JOIN 
	MHDDTest.dbo.Admin_tbl_Vendor AS f 
	ON f.vendorNumber = b.vend
	OR f.vendorNumber = b.vend + '-' + b.vadr
	OR f.vendorNumber IN 
	(
		SELECT 
			vendorNumber 
		FROM 
			MHDDTest.dbo.Admin_tbl_Vendor
		WHERE 
			SUBSTRING(afunction, 3, 2) + [object] = '42305' 
			AND SUBSTRING(vendorNumber, 1, 5) = b.vend
	)

Again this is a wild shot in the dark and probably not very effiecent nor accurate.

bpregler
Starting Member

11 Posts

Posted - 2008-05-14 : 14:30:56

partial sample data before addressing the ?? thing
serviceID vendorID units unitType act_cost
67 NULL 0.00 H 110.87
121 4298 12.00 M 150.25
78 NULL 6.00 H 240.57
2 8145 3.00 M 67.16

the results i need
serviceID vendorID units unitType act_cost
67 3260 0.00 H 110.87
67 3261 0.00 H 110.87
67 3264 0.00 H 110.87
67 3266 0.00 H 110.87
67 3269 0.00 H 110.87
67 3274 0.00 H 110.87
121 4298 12.00 M 150.25
78 124 6.00 H 240.57
78 127 6.00 H 240.57
78 129 6.00 H 240.57
2 8145 3.00 M 67.16

does this help???

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-14 : 14:35:35

quote:
Originally posted by bpregler

partial sample data before addressing the ?? thing
serviceID vendorID units unitType act_cost
67 NULL 0.00 H 110.87
121 4298 12.00 M 150.25
78 NULL 6.00 H 240.57
2 8145 3.00 M 67.16

the results i need
serviceID vendorID units unitType act_cost
67 3260 0.00 H 110.87
67 3261 0.00 H 110.87
67 3264 0.00 H 110.87
67 3266 0.00 H 110.87
67 3269 0.00 H 110.87
67 3274 0.00 H 110.87
121 4298 12.00 M 150.25
78 124 6.00 H 240.57
78 127 6.00 H 240.57
78 129 6.00 H 240.57
2 8145 3.00 M 67.16

does this help???

And what's the rule for generating the numbers in blue?

bpregler
Starting Member

11 Posts

Posted - 2008-05-14 : 15:50:34

[/quote]
And what's the rule for generating the numbers in blue?
[/quote]

there is no rule, its data from a table

select vendorNumber from MHDDTest.dbo.Admin_tbl_Vendor
where (substring(afunction,3,2) + object) = '42305' and substring(vendorNumber,1,5) = b.vend)

vendorID vendorNumber afunction object
1 23456 43526 236a
2 23456-01 52420 305
3 23456-02 52420 635c

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources