I have 2 tables, Members and AwardsMember in a Coldfusion 8 project.
AwardsMember
--------------------------
---
MemberNumber(blank),
AwardID(data - int),
JBFaward(data - nvarchar)
Members
--------------------------
---
MemberNumber(data - nvarchar),
AwardID(blank),
JBFaward(blank)
I want to create a form that opens to show the AwardID, and JBFaward values. I want this form to allow editing of the values, in any field.
Each table currently has MemberNumber fields, but AwardsMember has no data for the MemberNumber field so I cannot use MemberNumber as a relation. Each table has AwardID, and JBFaward. I want to match and fill in all 'data' in AwardsMember.AwardID and AwardsMember.JBFaward with Members.AwardID and Members.JBFaward.
I had an ealier issue where I had multiple values in a field and I need to run a query to match on all entries from 2 different tables. For my similar issue and fix, I show the steps I used below. I tried using the same function/query on my current query design but am getting errors: 'Ambiguous column name 'JBFaward'
This is my current function:
--------------------------
-
USE [jbbnttt@u]
GO
/****** Object: UserDefinedFunction [dbo].[GetJBFawards] Script Date: 08/03/2008 00:47:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetJBFawards](@JBFa
ward nvarchar(255))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @res nVARCHAR(MAX)
SELECT @res = COALESCE(@res + ',', '') + JA.JBFaward
FROM dbo.AwardsMember JA
WHERE ',' + replace(@JBFaward, ' ', '') + ',' LIKE '%,' + CAST(JA.AwardID as nvarchar(100)) + ',%'
AND JA.JBFaward IS NOT NULL
RETURN @res
END
Current query
------------------------
SELECT AM.AwardID,
CASE WHEN JBFaward IS NOT NULL THEN dbo.GetJBFawards(JBFaward)
ELSE 'N/A' END AS JBFaward
FROM AwardsMember AM
JOIN Members Mem ON AM.AwardID = Mem.AwardID
WHERE Mem.membernumber = ('#member#')
==========================
==========
==========
========
[Previous issue fix]
Function
----------------
USE [jbbnttt@u]
GO
/****** Object: UserDefinedFunction [dbo].[GetMemberTypes] Script Date: 08/03/2008 01:26:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[GetMemberTypes](@Me
mberType nvarchar(30))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @res nVARCHAR(MAX)
SELECT @res = COALESCE(@res + ',', '') + MT.MemberType
FROM MemberTypes MT
WHERE ',' + replace(@MemberType, ' ', '') + ',' LIKE '%,' + CAST(MT.ID as nvarchar(100)) + ',%'
AND MT.MemberType IS NOT NULL
RETURN @res
END
Query:
-----------------
SELECT Prof.membernumber,
Prof.Program,
Programs.ProgramName,
Prof.Dues,
Prof.Term,
CASE WHEN Prof.MemberType IS NOT NULL THEN dbo.GetMemberTypes(MemberT
ype) ELSE 'N/A' END AS MemberType,
Prof.status,
Prof.isgift,
Prof.giftterm,
Prof.sourceCode,
Prof.JoinMethod,
Prof.Joindate,
Prof.expirationdate,
Prof.CompReason,
Prof.Last_pay_date,
Prof.CertRedeem
FROM Profiles Prof
JOIN Programs ON Prof.Program = Programs.id
WHERE Prof.membernumber = '#member#'
You can see my previous issue for more details:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_23546801.html#a21963778Start Free Trial
