Solved

# Querying dynamic file name base on field content.

Posted on 2008-11-18
189 Views
Hi,

Let's see table structure:

Table TDCHQRCVyyyy (Cheque Receive) where yyyy is year.

1. ChqId
2. ChqDate
3. ChqAmount
4. ChgDueDate

Table  TDINVPAYyyyy (Invoice Payment) where yyyy is year.

1. PayId.
2. PayDate.
3. ChqId.
4. ChqDate
5. Invoice.
6. InvoiceDate.
7. PayAmount.

SELECT A.PayId, A.ChqId, A.ChqDate, (SELECT  ChgDueDate FROM TDCHQRCVyyyy AS B WHERE A.ChqId = B.ChqId )
A.Invoice, A.PayAmount
FROM TDINVPAY2007.

Note : TDCHQRCVyyyy is get from TDCHQRCV + YEAR(A.ChqDate)

This a very challenging query.

I don't know if it is possible.

Thank you.

0
Question by:emi_sastra

LVL 14

Accepted Solution

i think you need dynamic sql to solve this problem
try this sample
CREATE TABLE #TmpTDCHQRCV(ChqId INT,ChgDueDate DATETIME,chkYear INT)

DECLARE @SQL AS VARCHAR(MAX)

SET @SQL = NULL

SELECT

@SQL =

CASE WHEN @SQL IS NULL THEN 'SELECT DISTINCT ' + CAST(A.ChqId AS VARCHAR) + ' AS ChqId,ChgDueDate,' + CAST(YEAR(A.ChqDate) AS VARCHAR) + ' AS chkYear FROM TDCHQRCV' + CAST(YEAR(A.ChqDate) AS VARCHAR)

ELSE @SQL + ' UNION ALL SELECT DISTINCT ' + CAST(A.ChqId AS VARCHAR) + ' AS ChqId,ChgDueDate,' + CAST(YEAR(A.ChqDate) AS VARCHAR) + ' AS chkYear FROM TDCHQRCV' + CAST(YEAR(A.ChqDate) AS VARCHAR) END

FROM(

SELECT

ChqId,

ChqDate

FROM

TDINVPAY2007

) AS A

INSERT INTO #TmpTDCHQRCV

EXEC(@SQL)

SELECT

A.PayId,

A.ChqId,

A.ChqDate,

(SELECT ChgDueDate FROM #TmpTDCHQRCV AS B WHERE A.ChqId = B.ChqId AND chkYear = YEAR(A.ChqDate)),

A.Invoice,

A.PayAmount

FROM TDINVPAY2007 AS A

DROP TABLE #TmpTDCHQRCV

0

LVL 51

Expert Comment

Dynamic SQL is the only way to do it, unless, there is a limit to the number of years, then you could do something like :

SELECT A.PayId, A.ChqId, A.ChqDate
, case
when year(a.chqdate) = 2006 then (SELECT  ChgDueDate FROM TDCHQRCV2006 AS B WHERE A.ChqId = B.ChqId )
when year(a.chqdate) = 2005 then (SELECT  ChgDueDate FROM TDCHQRCV2005 AS B WHERE A.ChqId = B.ChqId )
when year(a.chqdate) = 2004 then (SELECT  ChgDueDate FROM TDCHQRCV2004 AS B WHERE A.ChqId = B.ChqId )
else '19000101' end as A.Invoice
, A.PayAmount
FROM TDINVPAY2007.

or as a join... and then select distinct, or use as a subquery.

What do you prefer ?
0

LVL 51

Assisted Solution

think the above "else" line should be :  else '19000101' end as chngduedate, A.Invoice

Or, as a function might be interesting...

SELECT A.PayId, A.ChqId, A.ChqDate, dbo.udf_get_chngduedate(a.chqid,year(a.chqdate)) as chngduedate,  A.Invoice, A.PayAmount
FROM TDINVPAY2007

-- but create the function first...

create function udf_get_chngduedate(@chqid int, @year int)
returns datetime
as
begin

return (select isnull((case when @year = 2000 then (SELECT  ChgDueDate FROM TDCHQRCV2000 WHERE ChqId = @chqid)
when @year = 2001 then (SELECT  ChgDueDate FROM TDCHQRCV2001 WHERE ChqId = @chqid)
when @year = 2002 then (SELECT  ChgDueDate FROM TDCHQRCV2002 WHERE ChqId = @chqid)
when @year = 2003 then (SELECT  ChgDueDate FROM TDCHQRCV2003 WHERE ChqId = @chqid)
when @year = 2004 then (SELECT  ChgDueDate FROM TDCHQRCV2004 WHERE ChqId = @chqid)
when @year = 2005 then (SELECT  ChgDueDate FROM TDCHQRCV2005 WHERE ChqId = @chqid)
when @year = 2006 then (SELECT  ChgDueDate FROM TDCHQRCV2006 WHERE ChqId = @chqid)
when @year = 2007 then (SELECT  ChgDueDate FROM TDCHQRCV2007 WHERE ChqId = @chqid)
when @year = 2008 then (SELECT  ChgDueDate FROM TDCHQRCV2008 WHERE ChqId = @chqid)
when @year = 2009 then (SELECT  ChgDueDate FROM TDCHQRCV2009 WHERE ChqId = @chqid)
when @year = 2010 then (SELECT  ChgDueDate FROM TDCHQRCV2010 WHERE ChqId = @chqid)
else '19000101'
end),'19000101'))

end
GO

0

LVL 1

Author Comment

Hi All,

Let me try first which one is suitable in practice.

Thank you very much for your help.
0

LVL 51

Expert Comment

Just in case... The create function is a once off event - don't have to do it every time, once it is there, it is done and then you can use it in place of a column name...

Thanks for the assist, and will see you around :)
0

LVL 1

Author Comment

Hi mark_wills,

Yes, the function offer flexibility in calling column.

Since you get in again, I will ask more regarding this question.

Could we pass the return columns (one or more than one)?

create function udf_get_chngduedate(@chqid int, @year int, @returnFields char)
as
begin

when @year = 2001 then (SELECT  @returnFields FROM TDCHQRCV2001 WHERE ChqId = @chqid)

If we could do it then we just have one function to get the entire fields.

Thank you.

0

LVL 51

Expert Comment

Yes, could return a table instead of a variable, but then it would look a lot like that original query I posted with a big case statement inside the actual query.

Could encapsulate the query as a VIEW if you want it to look "cleaner", or, if the function had other things to do, then might be good in the function, but from what you have described so far, the function is really just a more convenient method to encapsulate that case statement.
0

LVL 1

Author Comment

Ok.

Could encapsulate the query as a VIEW if you want it to look "cleaner".

I am happy to learn it. Should I post a new question for this?

Thank you.
0

LVL 51

Expert Comment

only if it goes beyond this... Besides - the dynamic SQL got the accepted answer on this one ;)

create view vw_INVPAY
as
SELECT
A.PayId
, A.ChqId
, A.ChqDate
, dbo.udf_get_chngduedate(a.chqid,year(a.chqdate)) as chngduedate
,  A.Invoice
, A.PayAmount
FROM TDINVPAY2007 A

go

or, you can put that case statement in the above. Once created - a once off event - you can use it just like any other table (for selections - not for inserts or updates)

e.g. select * from vw_INVPAY where chngduedate > getdate() - 90

0

LVL 1

Author Comment

Ok.

Thank you again for your help.

0

## Featured Post

### Suggested Solutions

Introduction Hopefully the following mnemonic and, ultimately, the acronym it represents is common place to all those reading: Please Excuse My Dear Aunt Sally (PEMDAS). Briefly, though, PEMDAS is used to signify the order of operations (http://en.…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…