Compairing dta in two tables

[RSmithwvsf]

i use psql for my dbase i have created a view that gives reside id numbers fnames lnames admit and lvl of care
i need to use crystal to get a list od numbers form the meal table for a singal date to be entered by the user and compare them to the list in the view and display the ones in the list that are not in the meal table for the date entered

 

[Turkbear]

Hi,
If you have access to creating views and Stored procedures in your database then creating a stored procedure that does a query to compare the two tables' data and which accepts a parameter from the CR user should do the trick as efficiently as possible.

The query would be a MINUS type ( if supported by you database) someting like:

select id numbers from view
MINUS
Select id numbers from mealtable
where date = (PassedParameter)






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[RSmithwvsf]

i am new to this so i will need a bit of hand holding

 

[Turkbear]

Hi,
It may be easier than using the SP but can you tell me:

What database is used?

What are the structures of the view and the meal table?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[RSmithwvsf]

i use pervasive sql
here is how i created my view


select P.Profile_ID ID, p.First_Name Fname, p.Last_Name Lname,p.Admitted_To_Facility admit,Ast_Facility_Type FType

from PA_Profile_Master__1 p

Where admit = 1 and FType = 'IL'

 

[Turkbear]

Hi again,
I hit submit before adding:

If you can use a command object with your database type, a single query should work for this:

Select ID from view where
ID NOT IN
( Select ID from mealtable where Date = {?UserInputDate})






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[RSmithwvsf]

ill give it a shot

 

[RSmithwvsf]

i have had to ajust the date feild in crystal useing the formula below
how dose ths affect the above example

StringVar DT := {RM_AAOD_POS_In___295.Date};
Date (
Val (DT [ 1 to 4 ]),
Val (DT[ 5 to 6 ]),
Val (DT[ 7 to 8 ]))

 

[Turkbear]

Hi,
If

RM_AAOD_POS_In___295.Date

Is an actual DATE type field in the database then just create an date parameter that can be used in the command like:

Select ID from view where
ID NOT IN
( Select ID from mealtable where RM_AAOD_POS_In___295.Date
= {?UserInputDate})


If you need to change the format do it in the command not in Crystal.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[RSmithwvsf]

sorry but no the date feild is a numarical feild that must be coverted in to a date i am un sure how t do this in the
pcc