I want to know if this query method is possible ?

Question

I have this Query

    SELECT     PaymentDetails_ID AS Pay_ID, Type, Description, Details, Due_Date,    PaymentDetails_Cleared, Amount, Mode,
                          (SELECT     Bill_ID
                            FROM          Bill_Payment_Records
                            WHERE      (Payment_ID = Pay_ID)) AS Bill_No
FROM         Payment_Details
WHERE     (Mode = 1) AND (PaymentDetails_Cleared = 0) AND (Due_Date BETWEEN @Start_Date AND @End_Date)

over here i want to add a variable which can be used in an inside query. Like i want Bill_ID which can be determined in the query.

As i studied i don't think its possible but is there any way i can do that.

Answer 1

Not sure about your question but according to me you can use variable inside select statement like this added @billid in query

SELECT     PaymentDetails_ID AS Pay_ID, Type, Description, Details, Due_Date,    PaymentDetails_Cleared, Amount, Mode,
     (SELECT     Bill_ID
          FROM          Bill_Payment_Records
          WHERE      (Payment_ID = @billid)) AS Bill_No
         FROM         Payment_Details
    WHERE     (Mode = 1) AND (PaymentDetails_Cleared = 0) AND (Due_Date BETWEEN @Start_Date AND @End_Date)

Answer 2

That is possible but not right way to do because it's like you put fixed value in the Query unless you use loop or Cursor for running Query.

Answer 3

I'm guessing that you're having trouble with the calculation for [Bill_No]. There are three possible issues I can think of, the first of which I'm quite sure is the main problem:
1. [Pay_ID] is actually [PaymentDetails_ID]. You can't use the alias inside the same SELECT statement where you assign the alias.
2. You don't specify the table names in the correlated sub-query.
3. You may get more than 1 record back from the correlated sub-query.

Try this?

SELECT
   PaymentDetails_ID AS Pay_ID,
   Type,
   Description,
   Details,
   Due_Date,
   PaymentDetails_Cleared,
   Amount,
   Mode,
   (
      SELECT
         TOP 1
         Bill_ID
      FROM
         Bill_Payment_Records
      WHERE
         [Bill_Payment_Records].Payment_ID = [Payment_Details].PaymentDetails_ID)
   ) AS Bill_No
FROM
   Payment_Details
WHERE
   (Mode = 1)
   AND (PaymentDetails_Cleared = 0)
   AND (Due_Date BETWEEN @Start_Date AND @End_Date)

(Only the "TOP 1" and table names were added to the correlated sub-query. "Pay_ID" was changed to "PaymentDetails_ID".)