Link to home
Start Free TrialLog in
Avatar of bfuchs
bfuchsFlag for United States of America

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.
[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, 

Open in new window


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

Open in new window


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?
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

What is it that you are trying to achieve?

Jim.
Avatar of bfuchs

ASKER

Hi Jim,

See here where I'm coming from...

Thanks,
Ben
Avatar of bfuchs

ASKER

Hi Jim,

Any hope for this...

Thanks,
Ben
Avatar of bfuchs

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

Open in new window

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:

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.
Avatar of bfuchs

ASKER

Correct.

Thanks,
Ben
Avatar of bfuchs

ASKER

Function should be something like the following.

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

Open in new window


Where I pass "input#InsertRecordVisit_Date.cbFormTextField, input#InsertRecordClient_Last_Name.cbFormTextField"
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

Open in new window


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.
Avatar of bfuchs

ASKER

Hi Jim,

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.

Open in new window


Would only need now an Excel formula to convert each field to a SQL line like below.

case when [@field:Skilled_Nursing_Visit_Note_Client_First_Name] is null then 'Client_First_Name' else null end
       +

Thanks,
Ben
Avatar of bfuchs

ASKER

Looks like I got this formula working by the following

SELECT Table1.Field1, " case when [@field:Skilled_Nursing_Visit_Note_" & Trim([Field1]) & "] is null then '" & [field1] & "' else null end  + " AS Expr1
FROM Table1;

Open in new window


However would still prefer a function.

Thanks,
Ben
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of bfuchs

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
Avatar of bfuchs

ASKER

Excellent!!!