Jump to content

The multi-part identifier "DL.uid" could not be bound.


Recommended Posts

I'm trying to create a query but I got an error and I'm not sure how to solve this problem I have.SQL Query

SELECT I4.Description_E I4_Desc_E, I4.ISIC4_CodeFROM Document_Link DL, ISIC4 I4INNER JOIN 	(SELECT GD_uid, Description_E GD_Desc_E	FROM GuidanceDocument	WHERE Deleted = 0) GDON DL.uid = GD.uidWHERE I4.ISIC4_Code = DL.CodeGROUP BY I4.ISIC4_Code, I4.Description_EORDER BY I4.ISIC4_Code

Output<ul> <li>I4_Desc_E (ISIC4_Code) <ul> <li>GD_Desc_E (GD_uid)</li> </ul> </li></ul>In one I4_Desc_E there could be many GD_Desc_E/GD_uid.Let me know if there is a better way of coding this query.

Link to post
Share on other sites

One thing I want to mention is to use the AS keyword when you're defining aliases. If you don't use AS, it will actually make a temporary copy of the table as use that, so it adds a bunch of overhead. Using AS just references the original table with a new name.

SELECT I4.Description_E AS I4_Desc_E, I4.ISIC4_CodeFROM Document_Link AS DL, ISIC4 AS I4INNER JOIN 	(SELECT GD_uid, Description_E AS GD_Desc_E	FROM GuidanceDocument	WHERE Deleted = 0) AS GD

Link to post
Share on other sites

Is there anything else that DL.uid could refer to? Is there a view or stored procedure or anything with the same name?It might be an issue with the join. I see that you refer to GD_uid and GD.uid, I'm not sure if those are supposed to be the same or not. Try this version:

SELECT I4.Description_E AS I4_Desc_E, I4.ISIC4_Code, GD.uid, GD.Description_E AS GD_Desc_EFROM Document_Link AS DL, ISIC4 AS I4, GuidanceDocument AS GDWHERE I4.ISIC4_Code = DL.Code AND DL.uid = GD.uidGROUP BY I4.ISIC4_Code, I4.Description_EORDER BY I4.ISIC4_Code

Link to post
Share on other sites

I get an error with the query which is "Column 'GuidanceDocument.uid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause."If I add GD.uid, GD.Description_E it works but not grouped the way I want to.Here is part of the data I get: * Other animal farming; production of animal products n.e.c. (0122) o This Manual describes the procedures and recommended approaches for estimating emissions from facilities engaged in rubber product manufacturing. (217) * Other animal farming; production of animal products n.e.c. (0122) o This Manual describes the procedures and recommended approaches for estimating emissions from incineration operations. (220)I want them grouped under the same category, like so: * Other animal farming; production of animal products n.e.c. (0122) (category is I4.Description_E and I4.ISIC4_Code) o This Manual describes the procedures and recommended approaches for estimating emissions from facilities engaged in rubber product manufacturing. (217) (GD.Description_E and GD.uid) o This Manual describes the procedures and recommended approaches for estimating emissions from incineration operations. (220)

Link to post
Share on other sites

It looks like they are grouped under the same category. Each record from the database is it's own record, you can't have one record that has the category title and then 10 records "under" that record. You need 10 records, each with the same category, with the details for each record. You can keep track of which category you are on and any time it changes just write a new bullet out.

$catname = "";while ($row = mysql_fetch_assoc($result)){  if ($catname != $row['category'])  {	if ($catname != "") //end the previous list	  echo "</ul></ul>";	$catname = $row['category'];	echo "<ul><li>{$catname}</li><ul>";  }  echo "<li>" . /* whatever else */ . "</li>";}

I'm not sure if you're using PHP but hopefully you get the idea.

Link to post
Share on other sites

I got it working in CF. Here is the CF equivalent:

<cfset catcode = "">	<cfoutput query="Links">		<cfif catcode NEQ ISIC4_Code>			<cfif catcode NEQ "">				</ul>				</li>			</ul>			</cfif>						<cfset catcode = ISIC4_Code>			<ul>				<li>#I4_Desc_E# (<strong>#catcode#</strong>)				<ul>		</cfif>		<li>#GD_Desc_E# (#uid#)</li>	</cfoutput>

I only 1 small problem. The last sub <li> is not closed with a </ul></li></ul>.

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...