How should this query be written
I need a query that I don't know how should be written. I pass in an AdmissionKey. if it is null then I want that line of the sql to be omited how can this be done? I have tried IIF ,IF/THEN and CASE when. but nothing will insert/omit the portion of the SQL Statement.
SELECT DateService,ProgName,AdmDa
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=:CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,Date
HAVING AdmissionKey=:AdmissionKey
SELECT DateService,ProgName,AdmDa
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=:CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,Date
HAVING AdmissionKey=:AdmissionKey
This should help you out:
SELECT DateService,ProgName,AdmDa
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=:CLIENTKEY
AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey)
GROUP BY PgmDocID,AdmissionKey,Date
SELECT DateService,ProgName,AdmDa
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=:CLIENTKEY
AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey)
GROUP BY PgmDocID,AdmissionKey,Date
have a look.
declare @CLIENTKEY int
declare @AdmissionKey int
declare @sql nvarchar(max)
set @clientkey=1
set @Admissionkey =null
set @sql=
'SELECT DateService,ProgName,AdmDate,MedStaff,OP__DOCID,AdmissionKey,PgmDocId
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,DateService,ProgName,AdmDate,MedStaff,OP__DOCID'
if (@admissionkey is not null)
begin
set @sql = @sql + ' HAVING AdmissionKey=:AdmissionKey '
end
print (@sql)
exec (@sql)
ASKER
aneeshattingal: Sorry didn't work
rrjegan17: isn't what I asked for. I need for the query to omit/insert the whole criteria (ie AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey)...this line would have to be omitted if the results were null).
RiteshShah: still trying to see if it will work.
Basically I need the query to be dynamic ,if possible
rrjegan17: isn't what I asked for. I need for the query to omit/insert the whole criteria (ie AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey)...this line would have to be omitted if the results were null).
RiteshShah: still trying to see if it will work.
Basically I need the query to be dynamic ,if possible
my query is dynamic, isn't it?
>> I need for the query to omit/insert the whole criteria (ie AND AdmissionKey= isnull(:AdmissionKey, AdmissionKey).
Then you need to have a dynamic query as mentioned by RiteshShah above.
Then you need to have a dynamic query as mentioned by RiteshShah above.
ASKER
RiteshShah: Yes, I am getting and error "Must declare the scalar varaible '@CLIENTKEY'
well, I assumed that you are passing clientkey as a variable so I have declared it and you can use your own.
can you show me your full snippet of code?
ASKER
I tried but what am I doing wrong:
:ClientKey and :AdmissionKey are the way this app passes in variables.
declare @CK int
declare @AdmissionKey int
declare @sql nvarchar(max)
set @clientkey=:ClientKey
set @Admissionkey =:AdmissionKey
set @sql=
'SELECT DateService,ProgName,AdmDa
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CK
GROUP BY PgmDocID,AdmissionKey,Date
if (@admissionkey is not null)
begin
set @sql = @sql + ' HAVING AdmissionKey=:AdmissionKey
end
exec (@sql)
:ClientKey and :AdmissionKey are the way this app passes in variables.
declare @CK int
declare @AdmissionKey int
declare @sql nvarchar(max)
set @clientkey=:ClientKey
set @Admissionkey =:AdmissionKey
set @sql=
'SELECT DateService,ProgName,AdmDa
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CK
GROUP BY PgmDocID,AdmissionKey,Date
if (@admissionkey is not null)
begin
set @sql = @sql + ' HAVING AdmissionKey=:AdmissionKey
end
exec (@sql)
The HAVING clause is only needed when you're selecting on an aggregate result of a Group By. Move the AdmissionKey criteria to the Where clause as follows:
WHERE CLIENTKEY=@CLIENTKEY
And ((AdmissionKey=@AdmissionK
GROUP BY PgmDocID,AdmissionKey,Date
Effectively the criteria 'Or (@AdmissionKey Is Null)' means that the AdmissionKey is ignored in the query
WHERE CLIENTKEY=@CLIENTKEY
And ((AdmissionKey=@AdmissionK
GROUP BY PgmDocID,AdmissionKey,Date
Effectively the criteria 'Or (@AdmissionKey Is Null)' means that the AdmissionKey is ignored in the query
what language are you using in your application?
ASKER
Its called Tier
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I was hoping that I wouldn't have to do it that way. Oh well. Thanks
FROM FD__HISTORY_PHYS
WHERE CLIENTKEY=@CLIENTKEY
GROUP BY PgmDocID,AdmissionKey,Date
HAVING @AdmissionKey is null or AdmissionKey=@AdmissionKey