Joining two datasets?

Hi All,

I have got two datasets. Some 'normal' rows with state values and an addtional dataset with the translation from state value to plain text (value / text pair).

A table object is attached to the first dataset, but I would like to show the plain text from the second dataset in a group header. So I'd like to do some kind of 'look up in another dataset'.

I wasn't able to find a function that could help me solve this problem. Does anyone has any ideas on this?

Kind regards.

Create 2 new parameters: Parameter1 will have all the values from DataSet2 (dataset with text/value paur) and Parameter2 will have all the text from DataSet2.

Write a function (VB.Net ot C#) in the report code (Report -> Report Properties -> Code tab) which will accept these parameters as object arrays and then store them in a shared variable.

Create a textbox in the beginning of the report which will call the above function by passing both the parameters. If you just specify the parameter (say Parameter!Parameter1.Value), it is considered as an array as it have more than one value and it will be received in the function as an object array.

Write another function (function2) in the code which will get the state id (or value) and then loop through the shared arrays (already stored) to find the corresponding text.

Call function2 from your table header to get the text for a given value.

Hope this helps. If more examples and explanation is needed, let me know.

Shyam

|||

Hello Shyam,

thanks a lot for your post. I think this a way to solve my problem.

But also think MS should think about this for future versions. I think it is very complicated (Crystal Reports makes it much easier for developers). It would also be very helpful if datasets could be accessed directly within custom funcation.

Kind regards, CLive76

|||

An alternative approach is to combine the two datasets already in the dataset query. Some options to consider:
? Linked Server functionality (see http://msdn2.microsoft.com/en-us/library/aa213283(SQL.80).aspx)
? OpenRowSet functionality to join data from another database server into the current query (http://msdn2.microsoft.com/en-us/library/ms190312.aspx, http://msdn2.microsoft.com/en-us/library/aa276850(SQL.80).aspx)

-- Robert

Join Translation in SQL Server

Hi
Can any one please translate these oracle inner join and outer join in SQL
Server. Any help will be highly appreciate.

SELECT V1.DB_VENDOR V1.NAME1,1,21)||DECODE(B1.JOINT_DESC,NULL,'',
'(JV)')||
DECODE(B1.NON_RESP,'Y','(NR)',
'N','') V1_NAME1,
B2.DB_CONTRACT B2_DB_CONTRACT, B2.BID_VENDOR B2_BID_VENDOR, B2.RANK_NUMB B2_RANK_NUMB,
V2.DB_VENDOR V2_DB_VENDOR,
SUBSTR(V2.NAME1,1,21)||DECODE(B2.JOINT_DESC,NULL,' ',
'(JV)')||
DECODE(B2.NON_RESP,'Y','(NR)',
'N','') V2_NAME1
FROM BID_TOTAL B1, BID_TOTAL B2, VENDOR V1, VENDOR V2
WHERE V1.DB_VENDOR = B1.BID_VENDOR
AND V2.DB_VENDOR (+) = B2.BID_VENDOR
AND B1.DB_CONTRACT = B2.DB_CONTRACT (+)
AND B1.RANK_NUMB > 1
AND MOD(B1.RANK_NUMB,2) = 0
AND B2.RANK_NUMB (+) = B1.RANK_NUMB + 1
AND B2.DB_CONTRACT IS NOT NULL
and b2.db_Contract=39624
ORDER BY B1.RANK_NUMBThey are all INNER JOINed. The (+) means (I think) the same as (INDEX=) hint. I'm having bigger problems with DECODE, don't have anything on Horacle handy at this point ;)|||any more help from some on on join?|||SELECT SUBSTR(V1.DB_VENDOR V1.NAME1,1,21)
+ CASE WHEN B1.JOINT_DESC IS NULL THEN '' ELSE '(JV)' END
+ CASE WHEN B1.NON_RESP = 'Y' THEN '(NR)'
WHEN B1.NON_RESP = 'N' THEN ''
END AS V1_NAME1
, B2.DB_CONTRACT AS B2_DB_CONTRACT
, B2.BID_VENDOR AS B2_BID_VENDOR
, B2.RANK_NUMB AS B2_RANK_NUMB
, V2.DB_VENDOR AS V2_DB_VENDOR
, SUBSTR(V2.NAME1,1,21)
+ CASE WHEN B2.JOINT_DESC IS NULL THEN '' ELSE '(JV)' END
+ CASE WHEN B2.NON_RESP = 'Y' THEN '(NR)'
WHEN B2.NON_RESP = 'N' THEN ''
END AS V2_NAME1
FROM BID_TOTAL B1
LEFT JOIN BID_TOTAL B2 ON B1.DB_CONTRACT = B2.DB_CONTRACT (+)
INNER JOIN VENDOR V1 ON V1.DB_VENDOR = B1.BID_VENDOR
RIGHT JOIN VENDOR V2 ON V2.DB_VENDOR (+) = B2.BID_VENDOR
WHERE B1.RANK_NUMB > 1
AND MOD(B1.RANK_NUMB,2) = 0
AND B2.DB_CONTRACT IS NOT NULL
AND b2.db_Contract=39624
ORDER BY B1.RANK_NUMB|||Hi Brett,

the mod function line is giving me error. I chanaged to this:
AND B1.RANK_NUMB % '2' = '0'
but it is still giving me error i.e.

Operand type clash: varchar is incompatible with void type
Server: Msg 8117, Level 16, State 1, Line 1
Operand data type numeric is invalid for modulo operator.

Thank you very much for immediate help.

waiting for response|||I forget what MOD does...do you know?|||Hi Brett.

Mod return the remainder of a division|||Hi Brett.

Mod return the remainder of a division

Well then that's a bizarre predicate

AND B1.RANK_NUMB/2.00-FLOOR(B1.RANK_NUMB/2.00) = 0|||AND cast(B1.RANK_NUMB as int) % 2 = 0|||What does this do again?

SELECT cast(100.00/20.00 as int) % 2|||Hi Brett,

Thank you very much...it worked