Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Coldfusion: Need MS SQL statement to return last(single) record where AID = variable

Posted on 2011-04-29
28
Medium Priority
?
454 Views
Last Modified: 2012-08-14
I am working with a coldfusion application that I did not build. I am not very familiar with CF. I have several tables in a view and need to return a single record based on the "AID" that is a session variable that gets created when the logs in to the application. I want to return the last check number written to the user, whereas now I am seeing thousands of records. Does anyone have an idea of how I could accomplish this within the view?

0
Comment
Question by:J C
  • 12
  • 11
  • 4
  • +1
28 Comments
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35495713
Use the MAX function in SQL Server

Select MAX(aid) as aid
from tablename
where comewherestatement
0
 

Author Comment

by:J C
ID: 35495731
SELECT     MAX(CheckNumber) AS checknumber
FROM         dbo.UnconditionalLienRequestView
WHERE     (AID = 760953892)

This works but I need to return all columns in the view UnconditionalLienRequestView whereas my statement only displays the checknumber. How can I return all rows with the MAX function?
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35495753
I cannot remember if you can select other columns or not.

Try this

SELECT     MAX(CheckNumber) AS checknumber, *
FROM         dbo.UnconditionalLienRequestView
WHERE     (AID = 760953892)

If that does not work do two queries. The Above and

SELECT      *
FROM         dbo.UnconditionalLienRequestView
WHERE     (CheckNumber= #queryname.CheckNumber#)
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 52

Expert Comment

by:_agx_
ID: 35495798
Assuming checkNumber is unique you use a subquery  OR a derived table

SELECT     (ColumnsYouWant)
FROM        UnconditionalLienRequestView
WHERE     CheckNumber = ( SELECT MAX(CheckNumber)
                                            FROM  UnconditionalLienRequestView
                                            WHERE AID = 760953892 )
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35495804
Oops I see my suggestion is basically the same as what RickEpnet already suggested.  Just with 1 query instead of 2.

As an aside, do use cfqueryparam instead of the raw values.  Just change the cfsqltype to match your data type.

ie .... WHERE AID = <cfqueryparam value="#yourValue#" cfsqltype="cf_sql_integer">
0
 

Author Comment

by:J C
ID: 35495836
Ok, this makes sense. So here is my next question...Those solutions work to return the last check number tied to the AID. What if I have child records I need to return in the query? For example, I have one checknumber but that checknumber has multiple PO's attached to it. Is there a way to return all records that have the same MAX(checknumber) being returned from the query above?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35495871
I'm sure it's possible. Without knowing the actual columns/relationships involved it sounds like an additional join. Not tested, but something like ..

SELECT  (ColumnsYouWant)
FROM     UnconditionalLienRequestVi t1
                  INNER JOIN YourPOTable t2 ON t1.CheckNumber = t2.CheckNumber

WHERE     t1.CheckNumber = ( SELECT MAX(CheckNumber)
                                            FROM  UnconditionalLienRequestView
                                            WHERE AID = 760953892 )


0
 

Author Comment

by:J C
ID: 35495909
I tried this and it still only returns one record. Any other suggestions?

SELECT     *, t2.*
FROM         dbo.UnconditionalLienCheckNumber t1 INNER JOIN
                      dbo.UnconditionalLienRequestView t2 ON t1.checknumber = t2.CheckNumber
WHERE     (t1.checknumber =
                          (SELECT     MAX(CheckNumber)
                            FROM          UnconditionalLienRequestView
                            WHERE      AID = 760953892))
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35495913
The field CheckNumber in both tables right? That is the relationship in both tables right? The record you are trying to pull up for sure has multiple POs right?
0
 

Author Comment

by:J C
ID: 35495919
Checknumber is in both tables. There are multiple PO's, I did verify that.
0
 
LVL 14

Expert Comment

by:RickEpnet
ID: 35495925
What happens when you do this? See the bold that is the differance.

SELECT     *, t2.*
FROM         dbo.UnconditionalLienCheckNumber t1 INNER JOIN
                      dbo.UnconditionalLienRequestView t2 ON t1.checknumber = t2.CheckNumber
WHERE     (t2.checknumber =
                          (SELECT     MAX(CheckNumber)
                            FROM          UnconditionalLienRequestView
                            WHERE      AID = 760953892))
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35495926
It would be easier if we knew the columns and relationships.

But do a select on the PO table only. Just hard code the check number for now. Does it return multiple records? If not, that's your problem.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35495929
...                    dbo.UnconditionalLienRequestView t2 ON t1.checknumber = t2.CheckNumber
WHERE     (t2.checknumber =


Given that it's an INNER JOIN and the ON clause says "t1.checknumber = t2.CheckNumber" the results *must* be the same whether you use t1 or t2  ;-)
0
 

Author Comment

by:J C
ID: 35495933
agx,

Yes it returns multiple records.

Here is the SQL for UnconditionalLienRequestView. Copied and pasted from Design mode inside of the SQL enterprise manager:

SELECT     dbo.APItem.CompCode, dbo.APHeader.BankCode, dbo.Payment.DocNumber AS CheckNumber, dbo.APHeader.PostingDate,
                      dbo.APHeader.InvNo AS CheckRef, dbo.APItem.PONo, dbo.APDetail.Amount, dbo.APDetail.TransNo, dbo.APItem.LicWithAmt, dbo.APDetail.Retention,
                      dbo.APItem.PaymentCode, dbo.APHeader.IsCurrent, dbo.APHeader.PUMemo, dbo.strGetString(dbo.APHeader.RefAlloc, ',', 3) AS Project,
                      dbo.APItem.Phase AS Lot, dbo.PUHeader.RuleID, dbo.APHeader.AID, dbo.Project.Name AS ProjectName, dbo.APItem.Memo,
                      dbo.PUHeader.PONo AS PONumNoRuleid, dbo.Address.Name AS Sub, dbo.ProjectInfoTable.Owner
FROM         dbo.PUHeader LEFT OUTER JOIN
                      dbo.APDetail LEFT OUTER JOIN
                      dbo.Payment ON dbo.APDetail.GroupNo = dbo.Payment.GroupNo AND dbo.APDetail.UnitNo = dbo.Payment.UnitNo RIGHT OUTER JOIN
                      dbo.ProjectInfoTable INNER JOIN
                      dbo.Project INNER JOIN
                      dbo.APItem ON dbo.Project.ProjID = dbo.APItem.ProjID ON dbo.ProjectInfoTable.ProjCode = dbo.Project.ProjCode ON
                      dbo.APDetail.CompCode = dbo.APItem.CompCode AND dbo.APDetail.Voucher = dbo.APItem.Voucher AND
                      dbo.APDetail.[LineNo] = dbo.APItem.[LineNo] ON dbo.PUHeader.POID = dbo.APItem.POID LEFT OUTER JOIN
                      dbo.Address INNER JOIN
                      dbo.APHeader ON dbo.Address.AID = dbo.APHeader.AID ON dbo.APItem.CompCode = dbo.APHeader.CompCode AND
                      dbo.APItem.Voucher = dbo.APHeader.Voucher
WHERE     (dbo.Payment.DocNumber IS NOT NULL)

I created a second view named UnconditionalLienCheckNumber where I use the SQL below:

SELECT     MAX(CheckNumber) AS checknumber
FROM         dbo.UnconditionalLienRequestView
WHERE     (AID = 760953892)

Now finally I am creating a third view:

SELECT     *, t2.*
FROM         dbo.UnconditionalLienCheckNumber t1 INNER JOIN
                      dbo.UnconditionalLienRequestView t2 ON t1.checknumber = t2.CheckNumber
WHERE     (t1.checknumber =
                          (SELECT     MAX(CheckNumber)
                            FROM          UnconditionalLienRequestView
                            WHERE      AID = 760953892))
0
 

Author Comment

by:J C
ID: 35495938
I am a database newb so I could definitely be making a stupid mistake.
0
 

Author Comment

by:J C
ID: 35495942
All of the information I need for the query is in the first view but I need to be able to return all the records for the last check number cut for the AID that will be fed in from a session variable. Hope that makes sense.
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35495948
I wouldn't create extra views for now until we figure out the issue.  

I'm still not sure which one is your "PO" table :) But are you saying that ..?

     1) Your original view returns 1 record if you hard code the check number (xxxxx)
      ie   SELECT  *
            FROM     UnconditionalLienRequestView
            WHERE  CheckNumber = xxxxxx

... and

      2) You get *multiple* records if you use that same hard coded number on your PO table
       ie   SELECT *
             FROM   YourPOTable WHERE CheckNumber = xxxxxx

... but you DON'T get multiple records if you use

            SELECT  *
            FROM     UnconditionalLienRequestView t1 INNER JOIN YourPOTable t2
                         ON t1.CheckNumber = t2.CheckNumber
            WHERE  t1.CheckNumber = xxxxxx

?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35495976
Our posts crossed...

All of the information I need for the query is in the first view

Oh ...  now I'm confused. If all of the data is in the 1st view, and you say there are multiple PO records per check number, I don't see how your original query *didn't* return multiple records in the 1st place.  Then again it's late and my brain is tired.
0
 

Author Comment

by:J C
ID: 35498344
This is the view that holds all of the information that I need. If I hardcode the checknumber it does return multiple records without using the MAX function.

SELECT     dbo.APItem.CompCode, dbo.APHeader.BankCode, dbo.Payment.DocNumber AS CheckNumber, dbo.APHeader.PostingDate,
                      dbo.APHeader.InvNo AS CheckRef, dbo.APItem.PONo, dbo.APItem.Amount, dbo.APDetail.TransNo, dbo.APItem.LicWithAmt, dbo.APDetail.Retention,
                      dbo.APItem.PaymentCode, dbo.APHeader.IsCurrent, dbo.APHeader.PUMemo, dbo.strGetString(dbo.APHeader.RefAlloc, ',', 3) AS Project,
                      dbo.APItem.Phase AS Lot, dbo.PuHeaderWithProjcodePhaseMajorMinor_View.Project AS projCode,
                      dbo.PuHeaderWithProjcodePhaseMajorMinor_View.Phase, dbo.Project.ProjID, dbo.APHeader.AID, dbo.Project.Name AS ProjectName,
                      dbo.Project.OUnitCode, dbo.PuHeaderWithProjcodePhaseMajorMinor_View.RuleID,
                      dbo.PuHeaderWithProjcodePhaseMajorMinor_View.Phase AS firstofphase, dbo.PuHeaderWithProjcodePhaseMajorMinor_View.POID
FROM         dbo.APDetail LEFT OUTER JOIN
                      dbo.Payment ON dbo.APDetail.GroupNo = dbo.Payment.GroupNo AND dbo.APDetail.UnitNo = dbo.Payment.UnitNo RIGHT OUTER JOIN
                      dbo.Project INNER JOIN
                      dbo.PuHeaderWithProjcodePhaseMajorMinor_View ON
                      dbo.Project.ProjCode = dbo.PuHeaderWithProjcodePhaseMajorMinor_View.Project LEFT OUTER JOIN
                      dbo.APItem ON dbo.PuHeaderWithProjcodePhaseMajorMinor_View.POID = dbo.APItem.POID ON dbo.APDetail.CompCode = dbo.APItem.CompCode AND
                      dbo.APDetail.Voucher = dbo.APItem.Voucher AND dbo.APDetail.[LineNo] = dbo.APItem.[LineNo] LEFT OUTER JOIN
                      dbo.APHeader ON dbo.APItem.CompCode = dbo.APHeader.CompCode AND dbo.APItem.Voucher = dbo.APHeader.Voucher
WHERE     (dbo.Payment.DocNumber IS NOT NULL)

Can you tell me how I might be able to pull the records I need based on the last check number that was created for the AID I am feeding it?
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35498618
The previous suggestion should work.  So I think your *actual* sql is different than we're thinking.  

Here's what you *seem* to be saying

1) This sql returns a check number like: 12345 .... ? (Yes/No)

      SELECT    MAX(CheckNumber) AS checknumber
      FROM       ViewThatHoldsAllInformationYouNeed
      WHERE    (AID = 760953892)

2) ... And when you hard code that SAME check number 12345 it returns multiple records ? (Yes/No)

      SELECT     *
      FROM       ViewThatHoldsAllInformationYouNeed
      WHERE    CheckNumber = 12345

3) ... But when you combine the 2 it does NOT return multiple records?   (Yes/No)

      SELECT     *
      FROM       ViewThatHoldsAllInformationYouNeed
      WHERE    CheckNumber = (SELECT    MAX(CheckNumber) AS checknumber
                                                 FROM       ViewThatHoldsAllInformationYouNeed
                                                 WHERE    AID = 760953892 )

0
 

Author Comment

by:J C
ID: 35498730
This is the query that is getting me closest to where I need to be as far as the results go. It is based off of the queries you've provided. I was using MAX(CheckNumber)  but found that it was not returning the most recent record. So I started using MAX(PostingDate) which gives me the most recent record but now the issue is that it seems to be ignoring the where aid = 760953892 statement  and appears to be pulling back checks for that date that were cut to other users with different AID's. If I could find a way to limit it to the AID I think I will have the fix I need. The query below does list all of the records I need, I just need the AID filter to work.

SELECT     *
      FROM       paymentInfobylot_view
      WHERE    PostingDate = (SELECT    MAX(PostingDate) AS maxPostingDate
                                                 FROM       paymentInfobylot_view
                                                 WHERE    AID = 760953892 )
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35506448
but found that it was not returning the most recent record.

Well in terms of business rules what DOES uniquely identify the latest check number/recent record?
Is it the greatest number, a check with the latest post date, can there be more than 1 check with the same post date, how do related to AID, etc ... ??  Like I said initially, we need to know the relationships between the data, in plain english.  You haven't provided that info yet, and without it ... we can only guess ...
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35692405
Maybe another set of eyes / different perspective would help here? (Unfortunately I'm just not seeing the info needed to come up w/the right sql)
0
 
LVL 25

Expert Comment

by:Ron Malmstead
ID: 35692481
SELECT     *
      FROM       paymentInfobylot_view
      WHERE    PostingDate = (SELECT    MAX(PostingDate) AS maxPostingDate
                                                 FROM       paymentInfobylot_view
                                                 WHERE    AID = 760953892 )


.....................

Try

SELECT top 1    *
       FROM       paymentInfobylot_view
 WHERE    AID = 760953892 ORDER BY PostingDate DESC
0
 

Author Comment

by:J C
ID: 35692642
To give a little more information. The AID is what is used to identify the vendor who logs in. It is the unique identifier for the user.

The check number would've been the perfect solution but the issue there is that the company pays from multiple accounts so the MAX(CheckNumber) does not always return the record's we are looking for. So I started using PostingDate. The problem I am running into is this...

Say that the latest check cut for our vendor who has an AID of 7588225 was April 1st but the last PostingDate in the system was for April 15th...The query returns 0 records if the check was cut on the PostingDate of Apriil 1st. So the query doesn't seem to be able to return the MAX(PostingDate) based on the last check cut to each individual vendor. I need if it's possible for the query to base the MAX(PostingDate) on whatever the last PostingDate is for each vendor w hich I thought the following statement would accomplish where AID = session.aid . That is not how it's behaving though. It will only return records for vendors who had a check cut to them in the last PostingDate the system contains which is April 15th currently. Does that make sense?
0
 
LVL 52

Accepted Solution

by:
_agx_ earned 2000 total points
ID: 35693791
the latest check cut for our vendor who has an AID of 7588225 was April 1st
but the last PostingDate in the system was for April 15th...


Sounds like you accidentally eliminated the WHERE filter on AID.  

Anyway, I'd go back to your other query from #35498730. That one seemed the closest.  Just add the AID to the outer filter too

ie
SELECT     *
      FROM       paymentInfobylot_view
      WHERE    AID = 760953892 
      AND         PostingDate = (SELECT    MAX(PostingDate) AS maxPostingDate
                                                 FROM       paymentInfobylot_view
                                                 WHERE    AID = 760953892 )

Open in new window

0
 

Author Comment

by:J C
ID: 35695003
Thanks for sticking with me man. I was feeding the AID to it but instead of trying to run the max function inside of the view I inserted this directly into the coldfusion page and it works great. You are awesome!
0
 
LVL 52

Expert Comment

by:_agx_
ID: 35701917
Great, glad we finally got there :)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question