September 30, 2005 at 10:36 am
I will try to explain my problem:
In a table for companydata. Each company has several rows (lots of data for the same company). Each row has until now been unique by a mnemonicId. I now would like to add a history possibility by adding a new column =version. So each mnemonicId can be stored in several rows for each company. But I then only want to select one value for each mnemonic per company and that should be the maximum version number per each mnemonic. I want all mnemonics that is placed in a order table so if the value is not in the table where I store the values NULL is returned. So I allways want a specific number of rows dependent on the order table. But only one value from the storing table based on the version number
now before changing the select statement I get this when running the SP when added more than 1 value per mnemonic per company
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Here is the SQL code witha CompanyId=294 as a test
SELECT
294 AS CompanyId,
MED.Med_Id as MetadataId,
MED.MED_mnemonic as Mnemonic,
MED.MED_short as Short,
MED.MED_format as format,
MED.MED_type as MetaDataType,
(SELECT CDX.CDX_intValue FROM
CDX_CompanyDataXX CDX
WHERE
CDX.CDX_COM_id=294
AND
CDX.CDX_MED_id=MED.Med_Id
(select MAX(CDX_version)from CDX_CompanyDataXX
where CDX_COM_id=294)
)
AS lngValue
FROM
MED_MetaData MED
JOIN
CDO_CompanyDataXXorder CDO ON MED.MED_id=CDO.CDO_MED_id
WHERE
MED.Med_Type=3
or MED.MED_type=4
or MED.MED_type=2
or MED.MED_type=7
order by CDO.CDO_COD_id
If I add following code
(select MAX(CDX_version)from CDX_CompanyDataXX
where CDX_COM_id=294) I get an answer but then version is based on the max for all the company values but I need it to be selected unique for each mnemonic. Is that possible to code?
September 30, 2005 at 1:22 pm
My order table, the table that distinguise which mnemonics should be included is, the CDO_CompanyDataXXOrder. That table is affecting all companies so no CompanyId can be found there. only MED_id(s) for all companies. Then MED_metadata table has all the metadata with the name and other metadata specific data. Then the actual values is stored in the CompanyDataXX table that has reference to MED_metadata by MED_id. As I wrote I always want to get as many rows as there are in the order table (CDO_CompanyDataXXOrder) even if no values exists in the Data table (CompanyDataXX). And if there are more than ONE value for each mnemonic in the (CompanyDataXX) take the highest version for that mnemonic, So for each value for each mnemonic will depend on how many versions there are for that mnemonic in the CompanyDataXX table for a specific company. Little bit complicated I know and expecially to write down.
So I need to check in CompanyDataXX for each mnemonic which version is the highest and then chose the value corresponding to that in CompanyDataXX.
September 30, 2005 at 2:37 pm
Schema is not clear (Atleast for me). Can you post your table design and some sample data with your expected results.
Regards,
gova
October 1, 2005 at 11:25 am
An example of the result
Headers:
CompanyId,MetadataId,Mnemonic,Short,format,MetaData,Type,lngValue,charValue
results:
294,888,XX_InvestmentCase,Investment,20,3,NULL,NULL
294,924,XX_OwnershipStructureFieldPeriod,OwnershipDate.20,3 ,NULL,NULL
294,949,XX_OwnershipFieldB7,%ofvotes 7,20,3,NULL,NULL
294,1198,XX_CreditRecommendation,CreditRecommendation,0,7,240,NULL
Here we can see that only the last row has a value in CDX_CompanyDataXX table, the value 240. The others has no value and NULL is instead shown.
The table CDX_CompanyDataXX has following columns
CDX_COM_id
CDX_MED_idC
CDX_intValue
CDX_decValue
CDX_charValue
CDX_dateValue
CDX_bolValue
CDX_version
CDX_sOrder
CDX_createdDate
Depending on type of values (type in mnemonic table) either of the bold columns is used to store a value for a mnemonic. The rest of the columns is NULL for that mnemonic. See the above example where I've only included 2 columns. Then I added the version column that should be incremeted by one if a new value is added to the database for a company and that mnemonic. In a SP I want to only retrive one value for each mnemonic per comoepany so I want to choose the value that has the highest version number for EACH mnemonic. If only one value is stored version number =0 then if a new value is added for that company for that mnemonic the version number is adding up 1 for that row.
CDO_CompanyDataXXOrder table:
CDO_MED_id
A number of MetadataIds is stored here (not all). Those that should be shown in the SP I would like to fix even if no value is in the value table CDX_CompanyDataXX.
MED_Metadata
MED_id and several columns with definitions for all metadata
October 2, 2005 at 8:02 pm
Do your query this way:
SELECT .....
FROM CDX_CompanyDataXX CCD
INNER JOIN .....
INNER JOIN (select MAX(CDX_version) as Max_CDX_version, CDX_COM_id
from CDX_CompanyDataXX
group by CDX_COM_id) M on M.Max_CDX_version = CCD.CDX_version and M.CDX_COM_id = CCD.CDX_COM_id
_____________
Code for TallyGenerator
October 2, 2005 at 10:36 pm
If you are willing to add a column to table CDX_CompanyDataXX, you can add a column that indicates the current version for each CDX_COM_Id row. New column is bit or char(1) flag, and can be maintained via triggers.
I use this method quite a bit for tables that maintain history when I need to query the latest valued row. Whether or not this is more efficient than Sergiy's method depends on the ratio of reads vs writes to the table. Best test with your actual data volumes.
Also, you may want to include the new column in any index on CDX_COM_id. Depends if your queries are doing index scans/seeks or not.
Your queries then look something like:
SELECT ...
FROM CDX_CompanyDataXX
...
WHERE CDX_COM_id = @value
and CurrentVer = 'Y'
Scott
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply