bfuchs
asked on
Build SQL statement from a list of fields.
Hi Experts,
I have a list of required fields, and trying to construct a SQL statement out of it as follows.
ListOfFields.
SQL
PS. Note I need each field name listed twice and not all have the same properties, as shown above.
What is the easiest way to get it?
I have a list of required fields, and trying to construct a SQL statement out of it as follows.
ListOfFields.
[input#InsertRecordVisit_Date.cbFormTextField, input#InsertRecordClient_Last_Name.cbFormTextField, input#InsertRecordClient_First_Name.cbFormTextField, input#InsertRecordDate_Of_Birth.cbFormTextField, select#InsertRecordShift_From_Hour.cbFormSelect, select#InsertRecordShift_From_Minute.cbFormSelect, select#InsertRecordShift_From_AMPM.cbFormSelect, select#InsertRecordShift_To_Hour.cbFormSelect, select#InsertRecordShift_To_Minute.cbFormSelect, select#InsertRecordShift_To_AMPM.cbFormSelect, textarea#InsertRecordPurpose_Of_Visit_Goal.cbFormTextArea, input#InsertRecordTemp.cbFormTextField, input#InsertRecordPulse.cbFormTextField, input#InsertRecordRR.cbFormTextField, input#InsertRecordBP.cbFormTextField, input#InsertRecordO2_Saturation.cbFormTextField, input#InsertRecordO2_In_Use0, input#InsertRecordO2_In_Use1, input#InsertRecordRespiratory_Lung_Sounds.cbFormTextField, input#InsertRecordRespiratory_Breathing_Pattern.cbFormTextField, input#InsertRecordBipap0, input#InsertRecordBipap1, input#InsertRecordCpap0, input#InsertRecordCpap1, input#InsertRecordTrach0, input#InsertRecordTrach1, input#InsertRecordVent0, input#InsertRecordVent1, input#InsertRecordCardiovascular_Skin_Color.cbFormTextField, input#InsertRecordSkin_Temp.cbFormTextField, select#InsertRecordCardiovascular_Edema.cbFormSelect, input#InsertRecordNeuro_Alert0, input#InsertRecordNeuro_Alert1, input#InsertRecordNeuro_Oriented0, input#InsertRecordNeuro_Oriented1, input#InsertRecordNeuro_Self_Directing0, input#InsertRecordNeuro_Self_Directing1, input#InsertRecordNeuro_Dev_Delay0, input#InsertRecordNeuro_Dev_Delay1, input#InsertRecordNeuro_Deficits0, input#InsertRecordNeuro_Deficits1, textarea#InsertRecordNeuro_Notes.cbFormTextArea, input#InsertRecordGI_Oral_Tube_Feeding0, input#InsertRecordGI_Oral_Tube_Feeding1, input#InsertRecordCurrent_Feeds.cbFormTextField, input#InsertRecordGastric_Residuals_Present0, input#InsertRecordGastric_Residuals_Present1, input#InsertRecordAbdominal_Assessment.cbFormTextField, input#InsertRecordFeeding_Tolerance.cbFormTextField, input#InsertRecordGU_Incontinent0, input#InsertRecordGU_Incontinent1, textarea#InsertRecordUrine_Stool_Volume_Description.cbFormTextArea, input#InsertRecordBedbound0, input#InsertRecordBedbound1, input#InsertRecordWheelChair0, input#InsertRecordWheelChair1, input#InsertRecordAmbulatory0, input#InsertRecordAmbulatory1, input#InsertRecordMobility_Aids.cbFormTextField, input#InsertRecordContractures0, input#InsertRecordContractures1, input#InsertRecordMusculoskeletal_Notes.cbFormTextField, select#InsertRecordPain_Score.cbFormSelect, input#InsertRecordPain_Notes.cbFormTextField, input#InsertRecordMedication_Changes0, input#InsertRecordMedication_Changes1, textarea#InsertRecordIntegument_Skin_Assessment.cbFormTextArea, textarea#InsertRecordSkin_Care_Measures_Taken.cbFormTextArea, textarea#InsertRecordHome_Environment_Safety.cbFormTextArea, textarea#InsertRecordTreatments_Administered.cbFormTextArea, textarea#InsertRecordPatient_Response.cbFormTextArea, input#InsertRecordInstructions.cbFormTextField, textarea#InsertRecordUnderstanding.cbFormTextArea, textarea#InsertRecordPlan_Goal.cbFormTextArea, input#InsertRecordMD_Called0, input#InsertRecordMD_Called1, input#InsertRecordTime_1.cbFormTextField, textarea#InsertRecordPN_Note_1.cbFormTextArea, input#InsertRecordTime_2.cbFormTextField, textarea#InsertRecordPN_Note_2.cbFormTextArea, input#InsertRecordTime_3.cbFormTextField, textarea#InsertRecordPN_Note_3.cbFormTextArea, input#InsertRecordTime_4.cbFormTextField, textarea#InsertRecordPN_Note_4.cbFormTextArea, input#InsertRecordNurse_Signature_Last_Name.cbFormTextField, input#InsertRecordNurse_Signature_First_Name.cbFormTextField, input#InsertRecordAcknowledge_Accuracy,
SQL
case when [@field:Skilled_Nursing_Visit_Note_Shift_From_Hour] is null then 'Shift_From_Hour' else null end
+
case when [@field:Skilled_Nursing_Visit_Note_Shift_From_Minute] is null then 'Shift_From_Minute' else null end
PS. Note I need each field name listed twice and not all have the same properties, as shown above.
What is the easiest way to get it?
ASKER
ASKER
Hi Jim,
Any hope for this...
Thanks,
Ben
Any hope for this...
Thanks,
Ben
ASKER
What is it that you are trying to achieve?Actually a VBA function would be the idel solution, something like
MyFunction(OrigStr as string,FirstStringToReplace as string, FirstReplaceWith as string, SecondStringToReplace as string, SecondReplaceWith as string..) as string
Where I pass the original list and receive the expected SQL above.Thanks,
Ben
So the basic problem is that you want to come up with this:
Yes?
Jim.
I want to create a calculated field (or formula) that will show which of the field/s on the table is null for that record.
So for example if FieldA, FieldC and FieldE are null, the value of that calculated field should display "FieldA, FieldC, FieldE".
Yes?
Jim.
ASKER
Correct.
Thanks,
Ben
Thanks,
Ben
ASKER
Function should be something like the following.
Where I pass "input#InsertRecordVisit_D ate.cbForm TextField, input#InsertRecordClient_L ast_Name.c bFormTextF ield"
and receive
Thanks,
Ben
Public Function myReplace(s As String) As String
Dim str As String
str = Replace(s, "input#InsertRecord", "case when [@field:Skilled_Nursing_Visit_Note_")
'....
myReplace = str
End Function
Where I pass "input#InsertRecordVisit_D
and receive
case when [@field:Skilled_Nursing_Visit_Note_Visit_Date] is null then 'Visit_Date' else null end
+
case when [@field:Skilled_Nursing_Visit_Note_Client_Last_Name] is null then 'Client_Last_Name' else null end
Thanks,
Ben
Ben,
I can't think of anyway of doing this in straight SQL. Certainly you can build the string, something along the lines of:
DECLARE @tb nvarchar(512) = N'dbo.[tblmytable]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ ' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM dbo.tblRequiredColumns
WHERE [TableName] = @tb;
EXEC sys.sp_executesql @sql;
but the tough part is getting the result field. It seems to me that you'd need to walk the result set checking each of the fields and then build up an output field.
So this would either need to be a stored procedure or VBA.
Jim.
I can't think of anyway of doing this in straight SQL. Certainly you can build the string, something along the lines of:
DECLARE @tb nvarchar(512) = N'dbo.[tblmytable]';
DECLARE @sql nvarchar(max) = N'SELECT * FROM ' + @tb
+ ' WHERE 1 = 0';
SELECT @sql += N' OR ' + QUOTENAME(name) + ' IS NULL'
FROM dbo.tblRequiredColumns
WHERE [TableName] = @tb;
EXEC sys.sp_executesql @sql;
but the tough part is getting the result field. It seems to me that you'd need to walk the result set checking each of the fields and then build up an output field.
So this would either need to be a stored procedure or VBA.
Jim.
ASKER
Hi Jim,
Not so familiar with t-SQL, would prefer working with VBA...
I manager to extract just the field names, like this
Would only need now an Excel formula to convert each field to a SQL line like below.
case when [@field:Skilled_Nursing_Vi sit_Note_Client_First_Name] is null then 'Client_First_Name' else null end
+
Thanks,
Ben
Not so familiar with t-SQL, would prefer working with VBA...
I manager to extract just the field names, like this
Client_First_Name.
Date_Of_Birth.
Shift_From_Hour
Shift_From_Minute
Shift_From_AMPM
Shift_To_Hour
Shift_To_Minute
Shift_To_AMPM
Purpose_Of_Visit_Goal
Temp.
Would only need now an Excel formula to convert each field to a SQL line like below.
case when [@field:Skilled_Nursing_Vi
+
Thanks,
Ben
ASKER
Looks like I got this formula working by the following
However would still prefer a function.
Thanks,
Ben
SELECT Table1.Field1, " case when [@field:Skilled_Nursing_Visit_Note_" & Trim([Field1]) & "] is null then '" & [field1] & "' else null end + " AS Expr1
FROM Table1;
However would still prefer a function.
Thanks,
Ben
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi Jim,
I am not in the office today, would need to test this thoroughly, hopefully on Sun.
Thanks very much for putting this together.
Have a nice weekend!
Ben
I am not in the office today, would need to test this thoroughly, hopefully on Sun.
Thanks very much for putting this together.
Have a nice weekend!
Ben
ASKER
Excellent!!!
Jim.