CODEWITHSUNDEEP
sql-server
samji
samji

Reputation: 31

how to use result of function as column name in where clause sql

Table structure like this

select * from TimeTable;

        userid | 1am|2am|3am| 4am| 5am
        1002   | 1  |1  |1  | 1  | 1
        1003   | 1  |1  |1  | 1  | 1 
        1004   | 1  |1  |1  | 1  | 1
        1005   | 1  |1  |1  | 1  | 1

I want select users that have column value 1 of specific timecolumn I have used following query but it is throwing error

com.microsoft.sqlserver.jdbc.SQLServerException: Conversion failed when converting the varchar value '[ 3PM]' to data type int.

select * from UserDetail u,TimeTable t  
where u.userid=t.userid
and CONCAT(SUBSTRING(CONVERT(VARCHAR, getdate(), 100), 13, 2) ,'',RIGHT(CONVERT(VARCHAR, GETDATE(), 100),2)) = 1

Like this when I use hardcoded column name then it works fine I want to select a column name dynamically.

select * from UserDetail u,TimeTable t  
where u.userid = t.userid
and [3AM] = 1

Upvotes: 0

Views: 2720

Answers (3)

RnP
RnP

Reputation: 400

Option 1

Use a long list of short-circuited WHERE clauses like below which seems fine given the table design and quite easy to understand

DECLARE @currentHour int = DATEPART(HOUR, getdate())
SELECT * FROM TimeTable
WHERE
    (@currentHour = 1 AND [1am] = 1) OR
    (@currentHour = 2 AND [2am] = 1) OR
    (@currentHour = 3 AND [3am] = 1) OR
    (@currentHour = 4 AND [4am] = 1) OR
    (@currentHour = 5 AND [5am] = 1) -- Etc

Option 2

Use UNPIVOT or VALUES to rotate the hour based columns into rows. As part of the rotate you can translate the column into a number indicating the hour. This you can compare with the current time’s hour component.

Option 3

Use dynamic sql which might or might not be ok for your environment or usage.

Below is the UNPIVOT approach (Option 2) which is a bit more complex to understand.

create table Test (id int, [1am] int, [2am] int, [3am] int, [4am] int)

insert Test values
  (1, 1, 2, 3, 4)
  , (2, 11, 12, 13, 14)
  , (3, 1, 21, 23, 24)
  , (4, 31, 32, 33, 34)

declare @time datetime = '2018-01-01 01:10:00' -- change this to getdate()

;WITH MatchingIds (id) AS
(
  SELECT id
  FROM
    (SELECT 
       id,  
       [1am] AS [1], [2am] AS [2], [3am] AS [3], [4am] AS [4] --etc 
     FROM Test) AS Source
     UNPIVOT
     (val FOR hour IN 
      ([1], [2], [3], [4]) --etc
     ) AS asRows
  WHERE 
      hour = CONVERT(VARCHAR, DATEPART(HOUR, @time)) 
      AND val = 1
) 
SELECT * FROM MatchingIds
-- MatchingIds now contains the rows that match your criteris
-- This can be joined with other tables to generate your full result

Intermediate output from MatchingIds for above example with time param set to around 1am

| id |
|----|
|  1 |
|  3 |

http://sqlfiddle.com/#!18/85c9c/5

Upvotes: 1

Imre Pühvel
Imre Pühvel

Reputation: 4994

select CONCAT(SUBSTRING(CONVERT(VARCHAR, getdate(), 100), 13, 2) ,'',RIGHT(CONVERT(VARCHAR, GETDATE(), 100),2)) is a value of type varchar, not the syntax of a query. What you are currently doing is for sql similar to:

select * from UserDetail u,TimeTable t  
where u.userid=t.userid
and '12PM' = 1

Even if parser accepted your query the condition would always be false as '12PM' is not the same as 12PM or [12PM]. You cannot dynamically modify a query within the query itself to make it work.

Yes, you could do tricks like dynamic execution (build entire query as string and then execute it), but please -don't. The real problem in you code is that you have a rather bad table design. Consider redesigning your TimeTable table to have timeOfDay values a single column and actual time values as data in that column. You'll save yourself a lot of headache later.

Upvotes: 0

ARr0w
ARr0w

Reputation: 1731

Try: 

select * from UserDetail u,TimeTable t  
where u.userid=t.userid
and 
(select COLUMN_NAME 
 from INFORMATION_SCHEMA.COLUMNS 
 where    TABLE_NAME = 'tblName' 
      AND TABLE_CATALOG = 'DBNAME'
      AND COLUMN_NAME = CONCAT(SUBSTRING(CONVERT(VARCHAR, getdate(), 100), 13, 2) ,'',RIGHT(CONVERT(VARCHAR, GETDATE(), 100),2))) = 1

Upvotes: 0

Related Questions