Bhaurao Devkar Bhaurao Devkar - 4 months ago 10
SQL Question

how to display display 1 record IF 2 same id to lrid & refno to search on 2nd ID record in sql

Example:

Lr_id
11
12
13

Ref_no
11


IN Table if match found in ref_no so display record of 12 otherwise 11.
Same Id like lr_id = 11 & Ref_no=11 so i want display record of lr_id =12 because ref_no = 11 assign to lr_id. Otherwise display as it is lr_id record.

SQL QUERY:

ALTER Procedure [dbo].[Track_LR_history]
@GCno as varchar(100),
@reference_no as varchar(100)
AS
declare @qry as varchar(max)
declare @cond as varchar(max)
declare @finalqry as varchar(max)
set @qry = 'SELECT l.lr_id AS GCno, l.creation_dt AS Date, Party.ConsignorName AS Consignor, Party.ConsigneeName AS Consignee, offi.branch_name AS Bookingbranch, '
set @qry = @qry + 'dbo.TBL_BRANCH.branch_name AS CurrentBranch, tost.branch_name AS DestinationBranch, TBL_APP_LIST_ELM_1.name AS CollType, '
set @qry = @qry + ' dbo.TBL_APP_LIST_ELM.name AS Deltype, TBL_APP_LIST_ELM_2.name AS PayType, l.reporting_date AS ReportingDate, case when isnull(l.stax_applicable_to2app_elm,0) =268435545 then isnull(chgamt.Booking,0) + isnull(l.ser_tax,0) else isnull(chgamt.Booking,0) end AS BookingAmt, '
set @qry = @qry + ' l.invoice_no AS Invno, l.lr_total_qty AS Qty, l.lr_total_weight AS Weight, l.entry_date, l.reference_no,dbo.TBL_BUS_CUST.cust_name AS FranchiseName, l.challan_no '
set @qry = @qry + ' FROM dbo.TBL_LR_HEAD AS l INNER JOIN dbo.TBL_BUS_CUST AS cr ON l.lr_cor2bc_cust = cr.row_id INNER JOIN '
set @qry = @qry + ' dbo.TBL_BUS_CUST AS ce ON l.lr_con2bc_cust = ce.row_id INNER JOIN dbo.TBL_BRANCH AS offi ON l.lr_frm2branch = offi.row_id INNER JOIN '
set @qry = @qry + ' dbo.TBL_BRANCH AS tost ON l.lr_to2branch = tost.row_id INNER JOIN dbo.TBL_BRANCH ON l.lr_current_br2site = dbo.TBL_BRANCH.row_id INNER JOIN '
set @qry = @qry + ' dbo.TBL_APP_LIST_ELM ON l.lr_del_typ2app_elm = dbo.TBL_APP_LIST_ELM.row_id INNER JOIN dbo.TBL_APP_LIST_ELM AS TBL_APP_LIST_ELM_1 ON l.lr_coll_typ2coll_typ = TBL_APP_LIST_ELM_1.row_id INNER JOIN '
set @qry = @qry + ' dbo.TBL_APP_LIST_ELM AS TBL_APP_LIST_ELM_2 ON l.lr_payment_type2app_elm = TBL_APP_LIST_ELM_2.row_id LEFT OUTER JOIN '
set @qry = @qry + ' dbo.TBL_BUS_CUST ON l.lr_franchise2bus_cust = dbo.TBL_BUS_CUST.row_id Left outer join'
set @qry = @qry + ' dbo.TBL_USER WITH (NOLOCK) ON l.lr_create2user = dbo.TBL_USER.row_id left outer join (select SUM(dbo.TBL_LR_CHARGES.amount) aS Booking,lr_chrg_item2lr_head from TBL_LR_CHARGES WITH (NOLOCK) where '

set @qry = @qry + ' dbo.TBL_LR_CHARGES.lr_chrg_bp2app_elm in ( 268435565) '
set @qry = @qry + ' AND lr_chrg_hd2chrg_hd <> 268435605 and lr_chrg_hd2chrg_hd <> 268435606 '
set @qry = @qry + ' and lr_chrg_hd2chrg_hd <> 268435607 and lr_chrg_hd2chrg_hd <> 268435608 and '
set @qry = @qry + ' lr_chrg_hd2chrg_hd <> 268435609 group by lr_chrg_item2lr_head) as chgamt on chgamt.lr_chrg_item2lr_head = l.row_id '
set @qry = @qry + ' left outer join View_LrConsrConse Party with (nolock) ON Party.Row_id = L.Row_id '


if @GCno<>''
begin

set @cond= ' where l.lr_id = '''+ @GCno +''''
--set @cond= ' where l.lr_id = '''+ @GCno +'+ OR +'+(select lr_id from TBL_LR_HEAD where reference_no=@reference_no)+''
end


if @reference_no<>''
begin
if @cond='' or @cond is null
begin
set @cond= ' where l.reference_no = '''+ @reference_no +''''
end
else
begin

set @cond= @cond + ' and l.reference_no = '''+ @reference_no +''''
end
end


exec (@qry + @cond)


Thank you.

Answer

I will go out on a limb and attempt an answer. It looks like you want to use the value from the Ref_no column if present (not NULL), otherwise use the value from the lr_id column. The COALESCE function does precisely this. Consider this query:

SELECT COALESCE(Ref_no, lr_id)
FROM yourTable