question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked LiHongMSFT-3908 edited

'SDAAAKLL001' expected result

I want, before inserting into table #Containerno ,check Contno against (Level_Four_ID and Loc_ID column) ,if value exist against (Level_Four_ID and Loc_ID column) then add 1 else SDAAAKLL001,from table #Containerno


 create table  #ConCatagory (Cat_ID int ,Cat_Name varchar(50),Alias_Name  varchar(50)) 
        
  Insert into #ConCatagory values (1,'Akber','AAA')
        
    
   create table  #tbl_location (Loc_ID int ,Loc_Name varchar(50),Alias_Name  varchar(50)) 
        
  Insert into #tbl_location values (47,'Internal','SD')
    
  create table  #tbl_Account_L_Four (Level_Four_ID int ,Level_Four_Name varchar(50),Sup_code  varchar(50),Cat_ID int) 
        
  Insert into #tbl_Account_L_Four values (2111006  ,'Kend Lal','KLL',1)
        
  Create table #Containerno  (CID int,Contno varchar(50),Level_Four_ID int,Loc_ID int)
        
  Insert into #Containerno values (1111,'SDAAAKLL001',2111006  ,47)


Output

SDAAAKLL001
SD =will take from #tbl_location column Alias_Name.
AAA = will take from #ConCatagory column Alias_Name
KLL =will take from tbl_Account_L_Four column Sup_code
001 = will be auto generated

I tried below query which is not giving me output when i pass Loc_ID parameter

  select concat(c.Alias_Name,t.Sup_code,  L.Alias_Name,
  format(isnull(cast(substring(n.Contno,  len(c.Alias_Name) +len(t.Sup_code) + len(L.Alias_Name) + 1, len(n.Contno)) as int), 0) + 1, '000')) as Barcode
  from #tbl_Account_L_Four t
  left outer join #ConCatagory c on c.Cat_ID = t.Cat_ID
  left outer join #Containerno n on n.Level_Four_ID = t.Level_Four_ID 
  left outer  join #tbl_location L on L.Loc_ID=n.Loc_ID and n.Contno like concat( c.Alias_Name,t.Sup_code, L.Alias_Name, '[0-11]%') 
    
  where t.Level_Four_ID =  2111006  and L.Loc_ID=47
sql-server-generalsql-server-transact-sql
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
1 Vote"
Viorel-1 answered Viorel-1 edited

Check one of possible solutions:

 select 
    concat(p, format(isnull(max(try_cast(stuff(n.Contno, 1, len(p), '') as int)), 0) + 1, '000')) as NewContno
 from #ConCatagory c
 inner join #tbl_Account_L_Four a on a.Cat_ID = c.Cat_ID
 cross join #tbl_location L
 cross apply (values (concat(L.Alias_Name, c.Alias_Name, a.Sup_code))) P(p)
 left join #Containerno n on n.Level_Four_ID = a.Level_Four_ID and n.Loc_ID = L.Loc_ID and left(n.Contno, len(p)) = p
 where a.Level_Four_ID = 2111006 and L.Loc_ID = 47
 group by p

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
1 Vote"
LiHongMSFT-3908 answered LiHongMSFT-3908 edited

Hi @akhterhussain-3167
The reason why your query not give you output is: you have written the wrong sequence of string concat.
This is the wrong sequence of Concat in your query: concat( c.Alias_Name,t.Sup_code, L.Alias_Name) and it gives you 202089-image.png
Obviously,there is no data like 'AAAKLLSD%',and that's why you got nothing from your query.
So, what you need to do is just modify it to the true sequence: concat(L.Alias_Name,c.Alias_Name,t.Sup_code) both in the WHERE clause and SELECT clause.
Check this:

 select concat( L.Alias_Name,c.Alias_Name,t.Sup_code
               ,format(isnull(cast(substring(n.Contno,  len(c.Alias_Name) +len(t.Sup_code) + len(L.Alias_Name) + 1, len(n.Contno)) as int), 0) + 1, '000')) as Barcode
 from #tbl_Account_L_Four t
 left outer join #ConCatagory c on c.Cat_ID = t.Cat_ID
 left outer join #Containerno n on n.Level_Four_ID = t.Level_Four_ID 
 left outer  join #tbl_location L on L.Loc_ID=n.Loc_ID and n.Contno like concat(L.Alias_Name,c.Alias_Name,t.Sup_code,'[0-11]%')   
 where t.Level_Four_ID =  2111006  and L.Loc_ID=47

Output:
202113-image.png

Best regards,
LiHong


If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


image.png (791 B)
image.png (1.4 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

@LiHongMSFT-3908

 IF **SDAAKLL001** does not exist in #Container table ,then it is not giving output
0 Votes 0 ·

Hi @akhterhussain-3167
Does 'Level_Four_ID = 2111006 and Loc_ID=47 ' exist in #Container table?

IF SDAAKLL001 does not exist in #Container table ,then it is not giving output

Of course, because there is and n.Contno like concat(L.Alias_Name,c.Alias_Name,t.Sup_code,'[0-11]%') in your join on conditions.
How about delete this and n.Contno like concat(L.Alias_Name,c.Alias_Name,t.Sup_code,'[0-11]%') ?

 select concat(L.Alias_Name,c.Alias_Name,t.Sup_code,format(isnull(cast(substring(n.Contno,  len(c.Alias_Name) +len(t.Sup_code) + len(L.Alias_Name) + 1, len(n.Contno)) as int), 0) + 1, '000')) as Barcode
 from #tbl_Account_L_Four t
 left outer join #ConCatagory c on c.Cat_ID = t.Cat_ID
 left outer join #Containerno n on n.Level_Four_ID = t.Level_Four_ID 
 left outer  join #tbl_location L on L.Loc_ID=n.Loc_ID 
 where t.Level_Four_ID =  2111006  and L.Loc_ID=47
1 Vote 1 ·