any thoughts on how to compare a number field with a date field 2

[mwhalen]

where my date field is formatted to just show the year. I'm using Crystal Version 7 and I look at trying to either compare it in my SQL or in a formula straight on my report.

 

[synapsevampire]

What does compare it to mean?

This would check to see if the year field is equal to todays year:

{MyTable.MyYearField} = year(currentdate)

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[mwhalen]

i have a field which is in a date format and I have another field which is in a number format. In my SQL I want to bring in just the records where the value from my date field is less than or equal to the value from my number field. It doesn't like the different data types.

 

[Naith]

If you're talking about writing your own SQL and wanting to convert the values before your report gets it hands on the SQL, then convert the date using something like TO_CHAR to 'yyyy'. (That's just Oracle, other db's will utilise a different function, but semantics aside, it's possible in all of 'em.)

If, however, you're talking about having Crystal convert the values, and then pass the conversion to the database in the auto-SQL, then that isn't an option. You have to drive the conversion either in your own pre-written SQL or stored procedure, or locally to Crystal - where it stays local to Crystal, and the database remains unaware of any conversion requirements.

Naith

 

[mwhalen]

THANK YOU! Just one question - if I convert it to CHAR - will it still let me compare it to a number or will it treat the new CHAR as a string

 

[Naith]

Don't get carried away by the TO_CHAR function, if indeed you are querying an Oracle database. That doesn't convert it to a character string, as the name of the function seems to imply. It just allows you to specify the format that you want your date to fall in.

Like

TO_NUMBER(TO_CHAR(YourDate,'yyyy'))

= 2002 can then be compared to a the number field containing 2002.

I'm just assuming your working against Oracle here, but if you're using anything else, it's still plausible, you just need to check out the local syntax where appropriate.

All the best,

Naith

 

[mwhalen]

thank you thank you thank you

Further...

what I'm trying to accomplish (and I think you helped me with max date once before) is

plan.eff date =
(select max (plan.eff date) from plan
where plan.type = studentdegree.type AND
plan.eff date less than or equal to studentdegree.yr)

[where the degree year is the number that I'm going to convert to char for the purposes of comparison] - does this make sense Naith? Do you think it's possible or is there an easier way to do it?

 

[Naith]

You have the conversion idea the wrong way around. You don't want to convert StudentDegree.Yr. You want to convert the year portion of Plan.EffDate.

plan.eff date = 
      (SELECT MAX (plan.eff date) FROM plan,studentdegree
       WHERE plan.type = studentdegree.type AND
       TO_NUMBER(TO_CHAR(plan.effdate,'yyyy')) <= studentdegree.yr)

Naith

 

[mwhalen]

Naith - I believe it is working. I love you - I truly truly love you. I don't work in SQL often enough to get a grasp yet on the logic of it and to know all the functions so thank you from the depths of my heart!!!! I spent a day and a half on this and sometimes you get so blinded by your frustration.

 

[Naith]

Any time, my man. Now you can focus on having a delirious weekend.

 

[mwhalen]

one more thing - if you have time

I have to add in yet another max effective date subselect and it's not working, I get an error that says "view does not exist"

Here's my code


SELECT
PS_UWO_HEAV_DEG_VW."EMPLID",
PS_UWO_HE_APLNT_VW."ACAD_PLAN", PS_UWO_HE_APLNT_VW."EFFDT", PS_UWO_HE_APLNT_VW."DESCR",
PS_UWO_HEAV_DEG_VW."AV_CLASS_YR",
PS_UWO_HE_A_GRP_VW."EFFDT", PS_UWO_HE_A_GRP_VW."DESCR"
FROM
"SYSADM"."PS_UWO_HEAV_DEG_VW" PS_UWO_HEAV_DEG_VW,
"SYSADM"."PS_UWO_HE_APLNT_VW" PS_UWO_HE_APLNT_VW,
"SYSADM"."PS_UWO_HE_A_GRP_VW" PS_UWO_HE_A_GRP_VW
WHERE
PS_UWO_HEAV_DEG_VW."ACAD_PLAN" = PS_UWO_HE_APLNT_VW."ACAD_PLAN" AND
PS_UWO_HEAV_DEG_VW."ACAD_ORG" = PS_UWO_HE_A_GRP_VW."ACAD_GROUP" AND
(PS_UWO_HEAV_DEG_VW."EMPLID" = '300012881' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000313286' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000282513' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000268861' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000137739' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000117578' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000065946' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000052189' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000018062' OR
PS_UWO_HEAV_DEG_VW."EMPLID" = '000005178') AND
PS_UWO_HE_APLNT_VW."EFFDT" =
(SELECT MAX(PS_UWO_HE_APLNT_VW."EFFDT&quot FROM PS_UWO_HE_APLNT_VW
WHERE PS_UWO_HEAV_DEG_VW."ACAD_PLAN" = PS_UWO_HE_APLNT_VW."ACAD_PLAN" and
TO_NUMBER(TO_CHAR(PS_UWO_HE_APLNT_VW.&quot;EFFDT&quot;,'yyyy'))<=PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot AND
PS_UWO_HE_A_GRP_VW.&quot;EFFDT&quot; =
(SELECT MAX(PS_UWO_HE_A_GRP_VW.&quot;EFFDT&quot FROM PS_UWO_HE_A_GRP_VW
WHERE PS_UWO_HEAV_DEG_VW.&quot;ACAD_ORG&quot; = PS_UWO_HE_A_GRP_VW.&quot;ACAD_GROUP&quot; and
TO_NUMBER(TO_CHAR(PS_UWO_HE_A_GRP_VW.&quot;EFFDT&quot;,'yyyy'))<=PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot
ORDER BY
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; ASC

 

[Naith]

PS_UWO_HEAV_DEG_VW is not in the FROM statement of your subselects.

 

[Naith]

I presume you're running this as SYSADM (which would be an interesting choice of user). If not, you will most likely need to declare the owner of the tables at each reference, unless you use aliases. (You've done this in the main FROM, but not anywhere else.)

If you are running this as a different user and don't share the select rights on SYSADM's tables, then you may get a 'doesn't exist' error because it's looking in the wrong schema.

Naith

 

[mwhalen]

I'm not sure what you mean that PS_UWO_HEAV_DEG_VW is not in my subselects -it wasn't in my first subselect and that worked. It was when I added my second subselect that it couldn't find the view.

 

[mwhalen]

I think I got it:


SELECT
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot;,
PS_UWO_HE_APLNT_VW.&quot;ACAD_PLAN&quot;, PS_UWO_HE_APLNT_VW.&quot;EFFDT&quot;, PS_UWO_HE_APLNT_VW.&quot;DESCR&quot;,
PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot;,
PS_UWO_HE_A_GRP_VW.&quot;EFFDT&quot;, PS_UWO_HE_A_GRP_VW.&quot;DESCR&quot;
FROM
&quot;SYSADM&quot;.&quot;PS_UWO_HEAV_DEG_VW&quot; PS_UWO_HEAV_DEG_VW,
&quot;SYSADM&quot;.&quot;PS_UWO_HE_APLNT_VW&quot; PS_UWO_HE_APLNT_VW,
&quot;SYSADM&quot;.&quot;PS_UWO_HE_A_GRP_VW&quot; PS_UWO_HE_A_GRP_VW
WHERE
PS_UWO_HEAV_DEG_VW.&quot;ACAD_PLAN&quot; = PS_UWO_HE_APLNT_VW.&quot;ACAD_PLAN&quot; AND
PS_UWO_HEAV_DEG_VW.&quot;ACAD_ORG&quot; = PS_UWO_HE_A_GRP_VW.&quot;ACAD_GROUP&quot; AND
(PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '300012881' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000313286' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000282513' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000268861' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000137739' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000117578' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000065946' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000052189' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000018062' OR
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; = '000005178') AND
PS_UWO_HE_APLNT_VW.&quot;EFFDT&quot; =
(SELECT MAX(PS_UWO_HE_APLNT_VW.&quot;EFFDT&quot FROM PS_UWO_HE_APLNT_VW
WHERE PS_UWO_HEAV_DEG_VW.&quot;ACAD_PLAN&quot; = PS_UWO_HE_APLNT_VW.&quot;ACAD_PLAN&quot; and
TO_NUMBER(TO_CHAR(PS_UWO_HE_APLNT_VW.&quot;EFFDT&quot;,'yyyy'))<=PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot AND
PS_UWO_HE_A_GRP_VW.&quot;EFFDT&quot; =
(SELECT MAX(PS_UWO_HE_A_GRP_VW2.&quot;EFFDT&quot FROM &quot;SYSADM&quot;.&quot;PS_UWO_HE_A_GRP_VW&quot; PS_UWO_HE_A_GRP_VW2
WHERE PS_UWO_HEAV_DEG_VW.&quot;ACAD_ORG&quot; = PS_UWO_HE_A_GRP_VW2.&quot;ACAD_GROUP&quot; and
TO_NUMBER(TO_CHAR(PS_UWO_HE_A_GRP_VW2.&quot;EFFDT&quot;,'yyyy'))<=PS_UWO_HEAV_DEG_VW.&quot;AV_CLASS_YR&quot
ORDER BY
PS_UWO_HEAV_DEG_VW.&quot;EMPLID&quot; ASC

 

[Naith]

So it was looking at the wrong schema. I thought you couldn't be running this as SYSADM.

 

[mwhalen]

further addition.... I came across an id that will be in my PS_UWO_HEAV_DEG_VW but WILL NOT be in PS_UWO_HE_APLNT_VW. Now my problem is nothing related to that ID is coming through. I do have a left outer join in my Visual Linking Expert but I must have to modify my SQL?

Much appreciated.

 

[Naith]

I don't see a left outer join in your SQL. But, assuming you've modified the SQL since your penultimate post, you want to remember that any equal joins imposed on PS_UWO_HE_APLNT_VW after the left outer join on the same table, will in affect be nullified by the equal join.

In the example below:

TableA.A = TableB.A and
TableB.B = TableC.B (+) and
TableC.B = TableD.B

the left outer join on TableC will be made redundant by the fact that the next clause has TableC involved in an equal join. So, ultimately, the left outer join gets ignored.

If this is what you're doing, either handle the problem directly in the FROM statement - not in the WHERE statement, or use a view of the TableC equivalent only where it's referenced for the outer join.

All the best,

Naith

 

[mwhalen]

Actually my left outer join is done in my visual linking - does that make a difference?

 

[mwhalen]

actually in my visual linking both the plantype and the group tables are left outer joins. I know I'm close to getting exactly what I need and Naith you have been a Godsend to me! Thanks so much for your continued help - hope you had a great holiday.

Anyways, as I said, I'm using left outer joins in the visual linking. So I'm not sure how to modify the SQL?