• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 187
  • Last Modified:

Need SQL Syntax Expert

I have 2 tables.

Tbl_Data_Call_Questions has these fields
ID
Sequence_Number
Static_Question
Modifiable_Question

Tbl_Data_Call_Variables  has these fields
ID
Q01_Data
Q02_Data
Q03_Data
etc.
etc.
Q60_Data

I need to have a query that, if Modifiable_Question is NULL, then use Static_Question, which I have below.

However, if Modifiable_Question is not null, then it will contain a place Holder which must be filled in by the corresponding field in Tbl_Data_Call_Variables.

So, if Modifiable_Question contained this text: "Were you born in $$Q03_Data$$ in $$Q04_Data$$?"
And the corresponding row in Tbl_Data_Call_Variables contained:
Q03_Data:     1953
Q04_Data:     Milwaukee
The query would result in Question being "Were you born in 1953 in Milwaukee?"

Here is my current SQL syntax.  How do I modify it to incorporate the variable data?

I CANNOT predict which question will hold which place holder or how many place holders it will have.

SELECT     dbo.Tbl_Data_Call_Questions.Sequence_Number, CASE WHEN Modifiable_Question IS NULL
                      THEN Static_Question ELSE Modifiable_Question END AS Question
FROM         dbo.Tbl_Data_Call_Questions INNER JOIN
                      dbo.Tbl_Data_Call_Variables ON dbo.Tbl_Data_Call_Questions.ID = dbo.Tbl_Data_Call_Variables.ID
0
wsturdev
Asked:
wsturdev
  • 2
2 Solutions
 
dqmqCommented:
Undoubedtly, we need to know what database.  Also, this may be easier with a different table design; are you at liberty to change it?

In the meantime, does this work to simplify dealing with nulls?

SELECT     dbo.Tbl_Data_Call_Questions.Sequence_Number, ISNULL(
Modifiable_Question
,Static_Question) AS Question
FROM         dbo.Tbl_Data_Call_Questions INNER JOIN
                      dbo.Tbl_Data_Call_Variables ON dbo.Tbl_Data_Call_Questions.ID = dbo.Tbl_Data_Call_Variables.ID


Then, does this work for one of the variables:

SELECT     dbo.Tbl_Data_Call_Questions.Sequence_Number, ISNULL(
Replace(Modifiable_Question,'$$Q01_Data$$',Q01_Data)
,Static_Question) AS Question
FROM         dbo.Tbl_Data_Call_Questions INNER JOIN
                      dbo.Tbl_Data_Call_Variables ON dbo.Tbl_Data_Call_Questions.ID = dbo.Tbl_Data_Call_Variables.ID



0
 
JimBrandleyCommented:
One more approach is to create a function. Only needs to compile once and is only transmitted to the DB server once, so it should be more efficient than embedding the same sort of thing in a select.

CREATE FUNCTION dbo.PopulateModifiableQuestion
  @pID INT,
  @pQuestionTemplate VARCHAR(400)
  RETURNS VARCHAR(4000)
AS

DECLARE
  @pQuestion     VARCHAR(4000),

BEGIN
   SET @pQuestion = REPLACE(@pQuestionTemplate, '$$Q01_Data$$', SELECT Q01_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.ID)
   SET @pQuestion = REPLACE(@pQuestion, '$$Q02_Data$$', SELECT Q02_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.ID)
   SET @pQuestion = REPLACE(@pQuestion, '$$Q03_Data$$', SELECT Q03_Data FROM dbo.Tbl_Data_Call_Variables WHERE dbo.Tbl_Data_Call_Variables.ID)
   -- etc.
   RETURN @pQuestion
END
GO

Jim
0
 
JimBrandleyCommented:
It would also be possible to create a big CASE inside a loop, so you only need to run the replacement selects for the number of variables present in the statement. Use CHARINDEX(@pQuestion, '$$') to decide when to terminate the loop. Then select the complete string including the $$ delimiters. Match the result with string constants for the cases and run that REPLACE only.
0
 
wsturdevAuthor Commented:
Both suggestions had their good points, but I like the idea of a UDF better.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now