have an assignment of extending an existing asp application with new functionality, including adding support for a new database. The sql code in the existing app is generated using xsl transformations. The new database looks something like this:METADATA TABLEKEYS A, B and C <- PKMETADATA <- lots and lots of columnsISSUE_TYPE TABLEFK A, B and CTYPE <-- different kinds of data (pic, vid, bin etc)PATH <--- file pathPLease note that a unique foreign key combination can exist multiple times if they have more than one media type. For example one A, B and C can have as many records in ISSUE_TYPE as the number of different types of data.When the user selects some search parameters (A B or C) and click submitI want to present a list that looks like the followingRESULT_SETA B C VID PIC BIN DOC23 2 4 x x x6 3 3 xDo you understand? The x corresponds to links to the files (if they exist). If there (for example) doesnt exist a video or doc for 6,3,3 then null should be returned.My main problem is how to make this sql query to work with xslt. As I normally would write it is a procedure where the vid, pic, bin and doc columns would be different subquerys to the ISSUE_TYPE table. And the parameters A, B and C as @params to the procedure.SOmething like:select METADATA.A, METADATA.B, METADATA.C, (select path from ISSUE_TYPE where TYPE='VID' and ISSUE_TYPE.A = @A and ISSUE_TYPE.B = @B and ISSUE_TYPE.C = @C) as vid... etc etc for all media types FROM METADATA, ISSUE_TYPE WHERE METADATA.A = @A and METADATA.B = @B and METADATA.C = CPerhaps a join should be helpful, im not sure...? jeezzBut now I somehow have to make this work in xsl. PLease look below for the existing xslt file for the old query<?xml version="1.0" encoding="utf-8"?><xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" xmlns:user="http://www.test.com" extension-element-prefixes="msxsl" exclude-result-prefixes="user" version="1.0"><xslutput method="text"/><xsl:template match="/parameters">distinct substring(cadsh.d1,1,1) as hyd_type, substring(cadsh.d1,2,1) as i_type_rat, cadsh.d2 as sales_code, cadsh.imp_type as press_char, cadsh.d3, cadsh.dwgnr, cadsh.mech_front, cadsh.mech_side, cadsh.mech_top cadsh (cadsh.validto is NULL or cadsh.validfrom < GetDate()) and (cadsh.validto is NULL or cadsh.validto > GetDate()) and cadsh.d2 is not NULL and cadsh.remarks is NULL<xsl:apply-templates select="SALES_CODE"/><xsl:apply-templates select="HYD_TYPE"/><xsl:apply-templates select="I_TYPE_RAT"/><xsl:apply-templates select="dimdraw_drawingnumber"/>by substring(cadsh.d1,1,1),substring(cadsh.d1,2,1),cadsh.d2 for xml auto</xsl:template><xsl:template match="SALES_CODE[.!='']"><xsl:if test=".!='*'"> and cadsh.d2='<xsl:value-of select="."/>'</xsl:if></xsl:template><xsl:template match="HYD_TYPE[.!='']"><xsl:if test=".!='*'"> and substring(cadsh.d1,1,1)='<xsl:value-of select="."/>'</xsl:if></xsl:template><xsl:template match="I_TYPE_RAT[.!='']"><xsl:if test=".!='*'"> and substring(cadsh.d1,2,1)='<xsl:value-of select="."/>'</xsl:if></xsl:template><xsl:template match="dimdraw_drawingnumber[.!='']">and dwgnr like '<xsl:value-of select="."/>%'</xsl:template></xsl:stylesheet>
As you can see the parameters are called SALES_CODE, HYD_TYPE and I_TYPE_RAT. There is in teh above code a fourth param dimdraw_drawingnumber. But that should not be used anymore.As yu can see all they do is adding the SQL AND statements at the end if there is a value in the parameters <> '*'.But how to do that if the above sql was to be written with the subqueries I suggested??? I really stuck on this. Anyone, please help me. If you dont understand xslt, please try to solve the sql problem...Henrik - who is hoping you haven't fallen asleep of the LONG post.
I need help with complicated sql issue
Posted - 2005-11-08 : 09:18:37