January 27, 2025 at 11:14 pm
I inherited a mess. The previous developer did not understand how to set up a database properly.
Beside not using proper data types, he did not use normalization,
One of the things I need to do now is query on a table, see below, that has 4 columns now (it was multiple tables, 1 for each language)
USE [IHD]
GO
/****** Object: Table [dbo].[NeedsAssessment] Script Date: 1/27/2025 5:02:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[NeedsAssessment](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Need] [nvarchar](500) NOT NULL,
[LanguageId] [int] NOT NULL,
[Sequence] [int] NULL,
PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
My problem is he stored the answer in a table with other items the UM* columns shown below
USE [IHD]
GO
/****** Object: Table [dbo].[Assessments] Script Date: 1/27/2025 5:06:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Assessments](
[RecordID] [int] IDENTITY(1,1) NOT NULL,
[Master] [nvarchar](50) NULL,
[Secondary] [nvarchar](50) NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Gender] [nvarchar](1) NULL,
[Position] [nvarchar](50) NULL,
[Company] [nvarchar](50) NULL,
[Email] [nvarchar](50) NULL,
[Phone] [nvarchar](50) NULL,
[Fax] [nvarchar](50) NULL,
[Answers] [nvarchar](56) NULL,
[DateTaken] [datetime] NULL,
[TimeTaken] [nvarchar](50) NULL,
[AssessmentIP] [nvarchar](50) NULL,
[Viewed] [bit] NOT NULL,
[DateViewed] [datetime] NULL,
[TimeViewed] [nvarchar](50) NULL,
[ViewingIP] [nvarchar](50) NULL,
[Comments] [nvarchar](50) NULL,
[TallyBoxEntry] [nvarchar](1) NULL,
[TallyG1D] [int] NULL,
[TallyG1I] [int] NULL,
[TallyG1S] [int] NULL,
[TallyG1C] [int] NULL,
[TallyG2D] [int] NULL,
[TallyG2I] [int] NULL,
[TallyG2S] [int] NULL,
[TallyG2C] [int] NULL,
[RecDeleted] [bit] NOT NULL,
[Password] [nvarchar](50) NULL,
[UM1] [int] NULL,
[UM2] [int] NULL,
[UM3] [int] NULL,
[UM4] [int] NULL,
[UM5] [int] NULL,
[UM6] [int] NULL,
[UM7] [int] NULL,
[UM8] [int] NULL,
[UM9] [int] NULL,
[UM10] [int] NULL,
[CRIAssessment] [nvarchar](50) NULL,
[Notes] [nvarchar](255) NULL,
[SentToBennett] [nvarchar](1) NULL,
[RequesterId] [int] NULL,
[Score3_D] [real] NULL,
[Score3_I] [real] NULL,
[Score3_S] [real] NULL,
[Score3_C] [real] NULL,
[Graph3_Desc] [nvarchar](50) NULL,
[BillingCode] [nvarchar](255) NULL,
[ApplicantNumber] [nvarchar](50) NULL,
CONSTRAINT [PK_Assessments] PRIMARY KEY CLUSTERED
(
[RecordID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
I am wanting to write a stored procedure that gets the sequence and need from the NeedsAssessment table based on what is in the UM* columns here is what I have tried that throws an error.
CREATE PROCEDURE ListNeedsResult
@RecordID int
AS
SET NOCOUNT ON
SELECT
Sequence,
Need
FROM
dbo.NeedsAssessment
WHERE
Sequence IN(SELECT UM1, UM2, UM3, UM4, UM5, UM6, UM7, UM8, UM9, UM10 from Assessments WHERE RecordID = @RecordID)
AND LanguageId = 1
ORDER BY
Sequence
GO
The error I am getting is Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
How do I write this to get the results I need?
January 28, 2025 at 1:51 am
I think you're looking for CROSS APPLY. Something like this will work:
/* create the dummy Assessment table with the repeating fields (UM#) */
CREATE TABLE Assessments(recordID INT, UM1 int, UM2 int, UM3 int, UM4 int, UM5 int, UM6 INT, UM7 int, UM8 int, UM9 int, UM10 int);
GO
/* add some records */
INSERT INTO Assessments(recordID, UM1, UM2, UM3, UM4, UM5, UM6, UM7, UM8, UM9, UM10)
VALUES(1,1,2,3,4,5,6,7,8,9,10),
(2,2,3,4,5,6,7,8,9,10,11);
/* unpivot the UM columns */
SELECT recordID, ca.UM
FROM Assessments
CROSS APPLY ( VALUES (UM1), (UM2), (UM3), (UM4), (UM5), (UM6), (UM7), (UM8), (UM9), (UM10) ) ca(UM);
Any columns you do not want unpivoted go in the SELECT statement, but notice the "ca" table prefix for the other columns. Kenneth Fisher wrote a really good article on Using Cross Apply to unpivot columns like 15 years ago... http://www.sqlstudies.com
He actually explains it all... =) unlike some people... <g>
January 28, 2025 at 2:30 pm
I believe that this also works. I have no idea which is more efficient.
CREATE PROCEDURE ListNeedsResult
@RecordID int
AS
SET NOCOUNT ON
SELECT
Sequence,
Need
FROM
dbo.NeedsAssessment
WHERE EXISTS
(
SELECT * from Assessments
WHERE RecordID = @RecordID
AND Sequence IN (UM1, UM2, UM3, UM4, UM5, UM6, UM7, UM8, UM9, UM10)
)
AND LanguageId = 1
ORDER BY
Sequence
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply