Click here to Skip to main content
Search within:




Next

where is problem in query ? help me with this, thanX
Sign Up to vote bad
good
See more: SQL 
LOAN_ID	Customer_CNIC	Installment_no	Date	        Amount
1234-55	12345-1234567-8	        1	2013-02-04	648700
	12345-1234567-8	        2	2013-03-04	648700
	12345-1234567-8	        3	2013-04-04	648700
	12345-1234567-8	        4	2013-05-04	648700
 
but i want as
 
LOAN_ID    Customer_CNIC      Installment_no  Date        Amount
1234-55    12345-1234567-8          1         2013-02-04  648700
                                    2         2013-03-04  648700
                                    3         2013-04-04  648700
                                    4         2013-05-04  648700
 
here is query
 
DECLARE @MinInstallments VARCHAR(100)
 
SELECT @MinInstallments = MIN(Installment_No)
            FROM Installments WHERE Loan_id IN 
            (SELECT Loan_id FROM Customer WHERE Customer_CNIC = '12345-1234567-8');
             Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID,c.Customer_CNIC, i.Installment_no, i.Date, i.Amount from Installments i,Loan l,Customer c where l.Loan_id = c.Loan_id and c.Loan_id = i.Loan_id and c.Customer_CNIC = '12345-1234567-8' order by l.Loan_id,i.Installment_no;
Posted 9-Jan-13 10:40am
zeshanazam630
Add a Solution

Comments
richcb - 9-Jan-13 16:46pm
What is the error you are getting?
zeshanazam - 9-Jan-13 16:53pm
no error but i m getting results as LOAN_ID Customer_CNIC Installment_no Date Amount 1234-55 12345-1234567-8 1 2013-02-04 648700 12345-1234567-8 2 2013-03-04 648700 12345-1234567-8 3 2013-04-04 648700 12345-1234567-8 4 2013-05-04 648700 instead of LOAN_ID Customer_CNIC Installment_no Date Amount 1234-55 12345-1234567-8 1 2013-02-04 648700 2 2013-03-04 648700 3 2013-04-04 648700 4 2013-05-04 648700
Sergey Alexandrovich Kryukov - 9-Jan-13 16:52pm
First, you are not explaining what do you want to achieve. Not a valid question. —SA

2 solutions

It should works, but it's not optimized:
DECLARE @MinInstallments VARCHAR(100)
DECLARE @CustCNIC VARCHAR(100)
 
SET @CustCNIC = '12345-1234567-8'
 
SELECT @MinInstallments = MIN(Installment_No)
FROM Installments AS i 
WHERE Loan_id IN (SELECT Loan_id FROM Customer WHERE Customer_CNIC = @CustCNIC);
 
SELECT CASE i.Installment_no
           WHEN @MinInstallments THEN i.Loan_id
           ELSE ''
           END AS LOAN_ID, 'Customer_CNIC' = CASE 
                                 WHEN i.Installment_no = @MinInstallMents THEN @CustCNIC 
                                ELSE ''
                                END, i.Installment_no, i.Date, i.Amount
FROM Installments i, Loan l, Customer c
WHERE l.Loan_id = c.Loan_id and c.Loan_id = i.Loan_id and c.Customer_CNIC = @CustCNIC
ORDER BY l.Loan_id,i.Installment_no;
 
In my opinion, never use CASE ... WHEN ... END to make output query more 'readible' or for 'visualize' data. There are many tools to do it, for example: ReportViewer[^]. Final resultset should looks like:
LOAN_ID	Customer_CNIC	Installment_no	Date	        Amount
1234-55	12345-1234567-8	        1	2013-02-04	648700
1234-55 12345-1234567-8	        2	2013-03-04	648700
1234-55 12345-1234567-8	        3	2013-04-04	648700
1234-55 12345-1234567-8	        4	2013-05-04	648700
and it's totally corect! There is nothing wrong with your query.
  Permalink  
Posted 9-Jan-13 11:46am
Maciej Los60.3K
Edited 9-Jan-13 12:02pm
v3
Comments
zeshanazam - 9-Jan-13 17:49pm
SELECT CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID, CASE 'Customer_CNIC' WHEN i.Loan_id = @MinInstallMents THEN @CustCNIC Incorrect syntax near '='.
Maciej Los - 9-Jan-13 17:54pm
Check it now! Replace CASE 'Customer_CNIC' = with 'Customer_CNIC' = CASE Sorry, my mistake.
zeshanazam - 9-Jan-13 17:57pm
Customer_CNIC column is appearing but blank..
Maciej Los - 9-Jan-13 18:01pm
Replace: WHEN Loan_id = @MinInstallMents THEN @CustCNIC with WHEN i.Installment_no = @MinInstallMents THEN @CustCNIC statement.
zeshanazam - 9-Jan-13 18:05pm
its working, thanks a lot :)
Maciej Los - 9-Jan-13 18:15pm
You're welcome.
zeshanazam - 9-Jan-13 18:26pm
but what if i want to retrieve all records...
Maciej Los - 10-Jan-13 16:38pm
How about PIVOT tables? Example data: LOAN_ID Customer_CNIC 2013-02-04 2013-03-04 2013-04-04 2013-05-04 1234-55 12345-1234567-8 648700 648700 648700 648700
Mika Wendelius - 11-Jan-13 13:22pm
Good answer, 5.
Maciej Los - 11-Jan-13 17:12pm
Thank you, Mika ;)
Quote:
Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID
Probably (I am not an expert) you have to replicate the above logic on Customer_CNIC field. Namely replace
c.Customer_CNIC
with
Select CASE i.Installment_no WHEN @MinInstallments THEN c.Customer_CNIC ELSE '' END AS CUSTOMER_CNIC
  Permalink  
Posted 9-Jan-13 11:11am
CPallini250K
Comments
zeshanazam - 9-Jan-13 17:13pm
i have done it. but only c.customer_CNIC appears only once not loan_id, i want both Customer_CNIC,Loan_id should appear once
CPallini - 9-Jan-13 17:34pm
Did you enter two SELECT CASE statements, namely: Select CASE i.Installment_no WHEN @MinInstallments THEN i.Loan_id ELSE '' END AS LOAN_ID, Select CASE i.Installment_no WHEN @MinInstallments THEN c.Customer_CNIC ELSE '' END AS CUSTOMER_CNIC ?
zeshanazam - 9-Jan-13 17:39pm
when i enter two select, two tables appears, but i want to get data only in one table...
Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Your Filters
Interested
Ignored
     
0 Christian Graus 419
1 Michael Haephrati 390
2 Prasad_Kulkarni 390
3 OriginalGriff 370
4 Sergey Alexandrovich Kryukov 270
0 Sergey Alexandrovich Kryukov 6,889
1 Prasad_Kulkarni 3,671
2 OriginalGriff 3,359
3 _Amy 3,312
4 CPallini 2,925


Advertise | Privacy | Mobile
Web01 | 2.6.130617.1 | Last Updated 9 Jan 2013
Copyright © CodeProject, 1999-2013
All Rights Reserved. Terms of Use
Layout: fixed | fluid