EOF

[arpan]

Suppose I have the following 3 SQL queries in an ASP code where the 3rd query makes use of the 1st two queries:

strSQL1="SELECT DISTINCT(adet.ErnCode) FROM ADET AS adet,ERNMST AS ern WHERE adet.ErnCode=ern.ErnCode AND ern.ErnDed='E'"

strSQL2="SELECT DISTINCT(adet.ErnCode) FROM ADET AS adet,ERNMST AS ern WHERE adet.ErnCode=ern.ErnCode AND ern.ErnDed='D'"

This is the 3rd query that makes use of the above 2 queries:

strSQL3="SELECT DISTINCT(em.ECode),em.EName,"
Do Until(objRS1.EOF AND objRS2.EOF)
strSQL3=strSQL3 & "(SELECT adet.Amt FROM ADET AS adet
WHERE adet.ErnCode='" & objRS1("ErnCode&quot & "' AND
adet.ECode=em.ECode) AS '" & objRS1("ErnCode&quot & "1E',
(SELECT adet.Amt FROM ADET AS adet WHERE adet.ErnCode=
'" & objRS2("ErnCode&quot & "' AND adet.ECode=em.ECode)
AS '" & objRS2("ErnCode&quot & "2D',"
objRS1.MoveNext
objRS2.MoveNext
Loop
strSQL3=strSQL3 & " FROM.........................."
strSQL3=Replace(strSQL3,", FROM"," FROM",1)

Now as long as the RecordCount of strSQL1 & strSQL2 remains the same, there is no problem but if they are not the same, the 3rd query will throw an error since I have used the keyword AND in Do Until(objRS1.EOF AND objRS2.EOF). For eg. if the RecordCount of strSQL1 is 7 & that of strSQL2 is 8, then an error will be thrown. One way of avoiding this is by replacing AND with OR but if I do so, the last field in strSQL2 will be neglected since objRS1 has already reached its EOF. Is there some way I can ensure that even if the RecordCount of strSQL1 & strSQL2 are not the same, I can still make use of AND in the statement Do Until(objRS1.EOF AND objRS2.EOF) so that none of the fields in strSQL2 get neglected & at the same time, an error isn't thrown?

Thanks,

Arpan

 

[Tarwn]

Could you print a sample of your SQL statement, it looks a little odd to me so i I can't see where your aiming with it.
It appears your going for some like:
SELECT DISTINCT(em.ECode),em.EName,(SELECT adet.Amt FROM ADET AS adet WHERE adet.ErnCode='value'AND adet.ECode=em.ECode) AS 'value1E' ...

But in this case unless you have table fieldnames returning from your inner selects... I'm a bit confused

Could you post some of your db info and a sample sql string?

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[arpan]

Hi Tarwn,

Thanks for your response. Actually, the table field names are the alias column names which I am using. For eg. as you have shown in your reply, value1E is the field name. So if I want to display the record contained in this field, I will use its ordinal no. to do so something like this:

<%
Do Until(objRS3.EOF)
For iCol=2 To objRS3.Fields.Count-1 'since the first
'field has a ordinal
'of 0. ECode & EName
'can be displayed
'by objRS3(&quot;ECode&quot
'& objRS3(&quot;EName&quot.
'That's why I am
'starting iCol from 2
'& not from 0.
Response.Write(objRS3(iCol).Name & &quot;<br>&quot
Response.Write(objRS3(iCol))
Next
objRS3.MoveNext
Loop
%>

I hope you understand what I have done above. Instead of using the conventional way of displaying the records using the field name like objRS3(&quot;Value1E&quot, I am using objRS3(ColumnNumber) since the alias column names will be assigned dynamically.

Now here's a brief idea of the DB. The DB records are actually the different ErnCodes & their corresponding amounts that make up the entire salary package of employees in a firm. Some of the ErnCodes could be his House Rent Allowance (HRA), Provident Fund (PF), Income Tax (IT) etc. In the above 3 categories I have provided, HRA will come under the 'E' category (strSQL1) where as PF & IT will come under the 'D' category (strSQL2). This is because the HRA amount is usually added to the pay package (EARNINGS) where as PF & IT are usually deducted from the salary package (DEDUCTION). The records in the ERNMST table are like this;

ERNCODE--------ERNNAME--------------------ERNDED
HRA---------House Rent Allowance--------------'E'
IT-----------Income Tax--------------------------'D'
PF-----------Provident Fund----------------------'D'

& the records are stored in the ADET table like this:

ECODE-------ERNCODE-----------AMT
1--------------HRA---------------300
1--------------PF-----------------200
1--------------IT-----------------100
2--------------HRA---------------600
2--------------PF-----------------500
2--------------IT-----------------400

Now w.r.t to the above 3 categories, strSQL3 would look like this:

SELECT DISTINCT(em.ECode),em.EName,(SELECT adet.Amt FROM ADET AS adet WHERE adet.ErnCode='HRA'AND adet.ECode=em.ECode) AS 'HRA1E', (SELECT adet.Amt FROM ADET AS adet WHERE adet.ErnCode='PF'AND adet.ECode=em.ECode) AS 'PF1D', (SELECT adet.Amt FROM ADET AS adet WHERE adet.ErnCode='IT'AND adet.ECode=em.ECode) AS 'IT1D' FROM....

Now it is possible that one particular company has introduced something new , say, for eg. Special Bonus which will be entered in the ERNMST table with the acronym say, SBONUS, under the ErnCode column. Now if an employee gets a Special Bonus, then his record will be entered in the ADET table with SBONUS under the ErnCode column. So if I am directly referencing the column names (HRA or IT or PF) to display the records, then how will I display those records which come under this Special Bonus category? This is precisely the reason why I am referencing the column numbers (& not the column names) to display the records.

I hope I am transparent enough to make you understand what exactly I am up to.

Thanks once again for your help,

Regards,

Arpan

 

[Tarwn]

Hmm, one last piece of confusion:
If a record is entered into the ADET table with SBONUS as the ERNCODE, SBONUS is not the column name, only the value in that column for a specific record.

I would think that the SQL statement would looki more like:
'SELECT ERNMST.ERNCODE as erncode, ERNMST.ERNNAME as ernname, ERNDED as ernded, ADET.ECODE AS ecode, ADET.AMT as amt WHERE ERNMST.ERNCODE = ADET.ERNCODE AND ECODE = '&whatever

The whatever is the employee Id (i assume)
For employee 1, this would return (from the above tables):
erncode ernname ernded ecode amt
HRA House Rent Allowance 'E' 1 300
PF Provident Fund 'D' 1 200
IT Income Tax 'D' 1 100

I understand what you were doing above, and my only problem is that I dont see why you are going to such an extreme. IA possible solution to the above problem would be to change strSQL1 to:
strSQL1=&quot;SELECT DISTINCT(adet.ErnCode),ErnDed FROM ADET AS adet,ERNMST AS ern WHERE adet.ErnCode=ern.ErnCode AND (ern.ErnDed='E' OR ern.ErnDed='D')&quot;

Then remove strSQl2 and in your loop for strSQL3:
Do Until(objRS1.EOF AND objRS2.EOF)
strSQL3=strSQL3 & &quot;(SELECT adet.Amt FROM ADET AS adet
WHERE adet.ErnCode='&quot; & objRS1(&quot;ErnCode&quot & &quot;' AND
adet.ECode=em.ECode) AS '&quot; & objRS1(&quot;ErnCode&quot & &quot;1&quot; & objRS1(&quot;ErnDed&quot & &quot;',&quot;
objRS1.MoveNext
Loop


That would loop through the single recordset using the ErnCode & &quot;1&quot; & ErnDed to add all the records to the 3rd (now 2nd) select stmt.

-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[arpan]

Tarwn,

I feel your suggestion of having only strSQL1 & omitting strSQL2 is a good one. You know the biggest problem I am having here is not in retrieving the records from the backend but in displaying them. & that's the main reason why I have strSQL1 for 'E' & strSQL2 for 'D' unlike what you have suggested where you have merged 'E' & 'D' together.

Actually I want to display the records in this way:

<table border=1>
<tr>
<th valign=top>1st 'E' Erncode<br>1st 'D' ErnCode</th>
<th valign=top>2nd 'E' Erncode<br>2nd 'D' ErnCode</th>
<th valign=top>3rd 'E' Erncode<br>3rd 'D' ErnCode</th>
<th valign=top>4th 'E' Erncode<br>4th 'D' ErnCode</th>
</tr>
<tr>
<!-- Display the records here -->
</tr>
</table>

To get an exact picture of how the records should be displayed, please visit

http://www26.brinkster.com/arpande/tables.html.

You will find, starting from the 5th column, CCA & AD.SAL in the 1st <td>, COM.ALL & EFP in the 2nd <td>, CRE & ESIC in the 3rd <td>, FALL & REC in the 4th <td> so on & so forth. Here CCA, COM.ALL, CRE & FALL come under the 'E' category where as AD.SAL, EFP, ESIC & REC come under the 'D' category. Similarly HRA, SP.ALL & V.ALL fall under the 'E' category & ITDS, PF & PT fall under the 'D' category.

So if I implement your suggestion, then how do I display the records as how I want it. What I am doing now is (i.e. using strSQL1 & strSQL2):

<table border=1>
<tr>
<th>S.NO.</th>
<th>E.CODE</th>
<th>EMPLOYEE NAME</th>
<th>WDAYS<br>PDAYS</th>
<th valign=top>BASIC</th>
<%
Do Until(objRS1.EOF OR objRS2.EOF)
Response.Write(&quot;<th>&quot
Response.Write(objRS1(&quot;ErnCode&quot)
Response.Write(&quot;<br>&quot
Response.Write(objRS2(&quot;ErnCode&quot)
Response.Write(&quot;</th>&quot
objRS2.MoveNext
objRS1.MoveNext
Loop
objRS1.Close
Set objRS1=Nothing
objRS2.Close
Set objRS2=Nothing
%>
<th>TOTAL</th>
<th>NET SALARY</th>
</tr>
<tr>
<%
'display the records here
%>

So can you suggest me how do I achieve the same if strSQL1 & strSQL2 are merged, as you have shown it? In fact, merging strSQL1 & strSQL2 into one will be more effecient since that will save one round trip to the database & the server.

Thanks,

Regards,

Arpan

 

[Tarwn]

The first solution I would suggest is to use an ORDER BY and order by ErnDed.

Than you could set all of the th's that will only have one row in them to

<table border=1>
<tr>
<th rowspan=&quot;2&quot;>S.NO.</th>
<th rowspan=&quot;2&quot;>E.CODE</th>
<th rowspan=&quot;2&quot;>EMPLOYEE NAME</th>
<th rowspan=&quot;2&quot;>WDAYS<br>PDAYS</th>
<th valign=top rowspan=&quot;2&quot;>BASIC</th>

Then for your loop:

<%
objRS1.MoveFirst
Dim lastErnded
lastErnded = objRS1(&quot;ernded&quot;)
Do Until objRS1.EOF
    If objRS1(&quot;ernded&quot;) <> lastErnded Then
        Response.Write &quot;</tr><tr>&quot;
        lastErnded = objRS1(&quot;ernded&quot;)
    End If 
    Response.Write(&quot;<th>&quot;)
    Response.Write(objRS1(&quot;ErnCode&quot;))
    Response.Write(&quot;</th>&quot;)
    objRS1.MoveNext
Loop

This way the loop will write the first row (D's) and then the second row (E's), the only differance is that they will have there own cells, which will be exactly the same as now (due to the rowspans in the single entry cells) except there will be a line between the upper and lower entry for the dual entry cells.

Hope that helps,
-Tarwn --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- --- ---
For my next trick I will pull a hat out of a rabbit (if you think thats bad you should see how the pigeon feels...)

 

[arpan]

Hi Tarwn,

I am really thankful to you for all your help. Your latest suggestion indeed displays the column names as I want it except for a minor difference which can very well be neglected. Now after incorporating your suggestion for changes in strSQL3, this is the query:

SELECT DISTINCT(em.ECode),em.EName,adet.Type,
(SELECT TOP 1 adet.Amt FROM ADET AS adet WHERE adet.Type IS NULL AND adet.ErnCode='CCA'.......) AS CCA1E,
(SELECT TOP 1 adet.Amt FROM ADET AS adet WHERE adet.Type='A' AND adet.ErnCode='CCA'.......) AS CCA2E,
...................................
(SELECT TOP 1 adet.Amt FROM ADET AS adet WHERE adet.Type IS NULL AND adet.ErnCode='ADSAL'.......) AS ADSAL1D,
(SELECT TOP 1 adet.Amt FROM ADET AS adet WHERE adet.Type='A' AND adet.ErnCode='ADSAL'.......) AS ADSAL2D,
FROM..................

So how do I display the records now? Please note that in strSQL3, I am first retrieving all the 'E' records & then the 'D' records. Also you will find one significant change in strSQL3 which is field named Type. Type can be either NULL or 'A' (meaning arrears). If an employee has arrears in a particular month, then his records should appear twice - once for the usual records & the other one for his arrears. Please visit

http://www26.brinkster.com/arpande/tables.html

if you have any doubts in how the records should be displayed if an employee has any arrears. As you will find in that site, the employee having ECode=7 is displayed twice - the 1st one are his usual records & the 2nd one are his arrear records. Same is the case with employee having ECode=25, 29, 31 etc. but the employee having ECode=2 has only 1 row of records since he didn't have any arrears in the month of May. So could you please help me out in displaying the records as shown in the above URL?

Thanks you once again,

Regards,

Arpan

 

[arpan]

Hi Tarwn,

I incorporated your suggestions. To have a look at how the records get displayed, please visit

http://www26.brinkster.com/arpande/tables1.html.

The look of the page is OK except for the way the TOTAL & the NET SALARY (at the extreme right of the page) get displayed. It looks very odd; that's my opinion. What do you say? I guess you didn't foresee this minor drawback but it indeed doesn't give a good picture. Is there any work around so as to make the page look decent especially the TOTAL & the NET SALARY part?

Arpan

 

[arpan]

Hi Tarwn,

I would like to seek your help in another matter as well with respect to this application. You must have already seen how the records are displayed in the web page. Now how do I add up the different amounts? I want to add up the amounts vertically (& not horizontally), for eg. HRA of ECode=1+HRA of ECode=2+HRA of ECode=3 etc. i.e. the HRA of all the employees, CRE of ECode=1+CRE of ECode=2+CRE of ECode=3 etc. i.e. the CRE of all the employees. I have already got the solution for adding up the amounts horizontally i.e HRA of ECode=1+CRE of ECode=1+CCA of ECode=1 etc.

Anticipating a concrete solution from your end since I desperately need one,

Thanks,

Arpan