April 20, 2015 at 5:44 am
Hello Friends
I have written a query that have a sub query. That works fine for fetching single column value.
But i want to fetch more than one column from the nested, it gives me error like " You have written a subquery that can return more than one field without using the EXISTS reserved word in the main query's FROM clause. Revise the SELECT statement of the subquery to request only one field. "
Below is the query the works fine.
That retrieves the "invoice_no" from "tbl_invoices".
"SELECT tr_id AS [Tr Id], tr_date AS [Tr Date], tr_type AS [Tr Type], tr_no AS [Tr No], item_id AS [Item Id], item_no AS [Item No], item_des AS [Description], item_unit AS [Item Unit], sale_rate AS [Sales Rate], pur_rate_on_sale AS [Pur Rate], discount_amount AS [Discount], tax_amount AS [Tax], cr_qty AS [Qty], acc_no AS [Account No], item_location AS [Location], (SELECT invoice_no FROM tbl_invoices WHERE tbl_invoices.invoice_no = tbl_items_trans.invoice_no) AS [Invoice No]FROM tbl_items_trans WHERE (tr_type = 'SALE' AND item_des LIKE '%" + textbox_search.Text.Trim() + "%' )"
But when i retrieves the "invoice_date" from "tbl_invoices".
It prompts the error.
Below is the query for accessing two columns values.
"SELECT tr_id AS [Tr Id], tr_date AS [Tr Date], tr_type AS [Tr Type], tr_no AS [Tr No], item_id AS [Item Id], item_no AS [Item No], item_des AS [Description], item_unit AS [Item Unit], sale_rate AS [Sales Rate], pur_rate_on_sale AS [Pur Rate], discount_amount AS [Discount], tax_amount AS [Tax], cr_qty AS [Qty], acc_no AS [Account No], item_location AS [Location], (SELECT invoice_no, invoice_date FROM tbl_invoices WHERE tbl_invoices.invoice_no = tbl_items_trans.invoice_no) AS [Invoice No] FROM tbl_items_trans WHERE (tr_type = 'SALE' AND item_des LIKE '%" + textbox_search.Text.Trim() + "%' )"
Please guide me resolve this issue.
Regards
Hasnain Ahmad
April 20, 2015 at 6:00 am
do an outer apply onto the subquery and then pass the columns from the outer apply into 2 different columns in the select.
ACtually, you could probably just do a straight left join onto the subquery table.
Anyway, that subquery, in my opinion, needs to be taken out of the select.
April 20, 2015 at 10:46 am
Dear MadAdmin
Can you write query and explain it step by step.
So i can learn from it.
Thanks in advance
Regards
Hasnain Ahmad
April 20, 2015 at 11:56 am
declare @Description varchar(50) = "textbox_search.Text.Trim()"
SELECT IT.tr_id AS [Tr Id], IT.tr_date AS [Tr Date], IT.tr_type AS [Tr Type]
, IT.tr_no AS [Tr No], IT.item_id AS [Item Id], IT.item_no AS [Item No]
, IT.item_des AS [Description], IT.item_unit AS [Item Unit], IT.sale_rate AS [Sales Rate]
, IT.pur_rate_on_sale AS [Pur Rate], IT.discount_amount AS [Discount]
, IT.tax_amount AS [Tax], IT.cr_qty AS [Qty], IT.acc_no AS [Account No]
, IT.item_location AS [Location]
, INV.invoice_no AS [Invoice No]
, INV.invoice_date AS [Invoice Date]
FROM tbl_items_trans IT
left join tbl_invoices INV on IT.invoice_no = INV.invoice_no
WHERE (IT.tr_type = 'SALE' AND IT.item_des LIKE '%' + @Description + '%' )
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply