asked on

Querying dynamic file name base on field content.

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.

ASKER CERTIFIED SOLUTION

Binuth

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mark Wills

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 ?

SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

emi_sastra

ASKER

Hi All,

Your both code are great.

Let me try first which one is suitable in practice.

Thank you very much for your help.

Mark Wills

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 :)

emi_sastra

ASKER

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)
returns datetime <----How about this?
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.

Mark Wills

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.

emi_sastra

ASKER

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.

Mark Wills

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

emi_sastra

ASKER

Ok.

Thank you again for your help.