emi_sastra
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.
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
Your both code are great.
Let me try first which one is suitable in practice.
Thank you very much for your help.
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 :)
Thanks for the assist, and will see you around :)
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.
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
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.
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.
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.
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.
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.
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
create view vw_INVPAY
as
SELECT
A.PayId
, A.ChqId
, A.ChqDate
, dbo.udf_get_chngduedate(a.
, 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
ASKER
Ok.
Thank you again for your help.
Thank you again for your help.
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 ?