June 4, 2012 at 8:02 am
HI ,
I have the following which picks up data up to 6 times , but if there are only 4 entries I want the last two to be blank where now it will only show 4 ?
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TOP 6 TAG_NAME = 'CUST_EXTMEDS', TAG_DATA = EM.OID + '~' + ISNULL(SI.SERVICE_ITEM_DESCRIPTION,EM.FullName) + '~' + '' + '~' + EM.EXT_MED_DOSE + '; ' + EM.EXT_MED_ROUTE + '; ' + EM.EXT_MED_FREQUENCY + '~' + '' + '~' + dbo.usi_fnGetUDF_usi('MSDP-013',EM.OID) + '~' + dbo.usi_fnGetUDF_usi('MSDP-014',EM.OID)
FROM CLIENTAGENCY_TO_CLIENT_EXTERNAL_MEDICATION_COLLECTION C
INNER JOIN CLIENT_EXTERNAL_MEDICATION EM (NOLOCK) ON C.OID_LINK = EM.OID
INNER JOIN SERVICE_ITEM SI ON EM.SERVICE_ITEM_MONIKER = SI.OID
WHERE C.OID = @CA_OID
AND EM.Expdate IS NULL
ORDER BY EM.EffDate DESC;
Thanks In Advance
Joe
June 4, 2012 at 8:37 am
jbalbo (6/4/2012)
HI ,I have the following which picks up data up to 6 times , but if there are only 4 entries I want the last two to be blank where now it will only show 4 ?
INSERT INTO @TEMPTABLE(TAG_NAME,TAG_DATA)
SELECT TOP 6 TAG_NAME = 'CUST_EXTMEDS', TAG_DATA = EM.OID + '~' + ISNULL(SI.SERVICE_ITEM_DESCRIPTION,EM.FullName) + '~' + '' + '~' + EM.EXT_MED_DOSE + '; ' + EM.EXT_MED_ROUTE + '; ' + EM.EXT_MED_FREQUENCY + '~' + '' + '~' + dbo.usi_fnGetUDF_usi('MSDP-013',EM.OID) + '~' + dbo.usi_fnGetUDF_usi('MSDP-014',EM.OID)
FROM CLIENTAGENCY_TO_CLIENT_EXTERNAL_MEDICATION_COLLECTION C
INNER JOIN CLIENT_EXTERNAL_MEDICATION EM (NOLOCK) ON C.OID_LINK = EM.OID
INNER JOIN SERVICE_ITEM SI ON EM.SERVICE_ITEM_MONIKER = SI.OID
WHERE C.OID = @CA_OID
AND EM.Expdate IS NULL
ORDER BY EM.EffDate DESC;
Thanks In Advance
Joe
To solve the problem at hand you should become familiar with the tally/number table.
http://www.sqlservercentral.com/articles/T-SQL/62867/[/url]
I also noticed a couple of potential serious issues with your code. First you have a scalar function in the select ( dbo.usi_fnGetUDF_usi). Scalar function are notoriously horrible for performance.
Second is the use of the NOLOCK hint. This hint is not even consistent across your tables. Given the table names I am hazarding a guess that data accuracy is, or at least should be, incredibly important. Not only are you introducing dirty reads, there are a host of other issues with this hint.
http://sqlblog.com/blogs/andrew_kelly/archive/2009/04/10/how-dirty-are-your-reads.aspx
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
That being said, if you want some real help with your problem you need to post ddl, sample data and desired output. See the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply