Refering to a table with one column and one row

Hi all,
I wish to set a reference date for age calculations in various places.
My thought was to have a table with one column and one row, with the
date, say 2007/04/01(yyyy/mm/dd)
Then I would like to calculate ages as on that date with a computed
column.
 How does one link the tables? I tried it with a cross join on a
simple test table. I suppose a cross join is OK as there is only one
row?


0
John
4/10/2008 7:03:44 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

6 Replies
703 Views

Similar Articles

[PageSpeed] 56

John,

In this case, the cross join is fine.
Another possibility would be to use a subselec in the places where you  
want to use the valuet, i. e. to use

(select "date" from my_table)

including the parentheses.

Frank

On Thu, 10 Apr 2008 21:03:44 +0200, John Le Brasseur  
<lebrasseur@btinternet.com> wrote:

> Hi all,
> I wish to set a reference date for age calculations in various places.
> My thought was to have a table with one column and one row, with the
> date, say 2007/04/01(yyyy/mm/dd)
> Then I would like to calculate ages as on that date with a computed
> column.
>  How does one link the tables? I tried it with a cross join on a
> simple test table. I suppose a cross join is OK as there is only one
> row?
>
>

0
Frank
4/10/2008 9:28:56 PM
Sorry,
I knew this would happen.
I have looked at examples to solve this but my logic in the following
statement isn't correct:

Select   
    (select refdate from _Ref_Date),
    Plantations.Region,
    Plantations.Subregion,
    Plantations.District,
    Plantations.Plantation, 
    C_Base_Data.Compt,
    cast((DATEDIFF(day, c_Base_Data.PLDate ,
_Ref_Date.refdate)/365.25)as Decimal(5,1)) as Current_Age, 
   Thinningregimes.Th_ID
   
 from  (C_Base_Data join Thinningregimes  on C_Base_Data.Th_ID =
Thinningregimes.TH_id
join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID) cross
join _Ref_Date
order by 
Plantations.Region,
    Plantations.Subregion,
    Plantations.District,
    Plantations.Plantation,
    C_Base_Data.Compt

It is duplicating rows where C_Base_Data.compt are identical.
Please just have a look at it.
Thanks.


On 10 Apr 2008 14:28:56 -0700, "Frank Ploessel"
<fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:

>John,
>
>In this case, the cross join is fine.
>Another possibility would be to use a subselec in the places where you  
>want to use the valuet, i. e. to use
>
>(select "date" from my_table)
>
>including the parentheses.
>
>Frank
>
>On Thu, 10 Apr 2008 21:03:44 +0200, John Le Brasseur  
><lebrasseur@btinternet.com> wrote:
>
>> Hi all,
>> I wish to set a reference date for age calculations in various places.
>> My thought was to have a table with one column and one row, with the
>> date, say 2007/04/01(yyyy/mm/dd)
>> Then I would like to calculate ages as on that date with a computed
>> column.
>>  How does one link the tables? I tried it with a cross join on a
>> simple test table. I suppose a cross join is OK as there is only one
>> row?
>>
>>
0
John
4/11/2008 11:57:24 PM
Sorry,
I knew this would happen.
I have looked at examples to solve this but my logic in the following
statement isn't correct:

Select   
    (select refdate from _Ref_Date),
    Plantations.Region,
    Plantations.Subregion,
    Plantations.District,
    Plantations.Plantation, 
    C_Base_Data.Compt,
    cast((DATEDIFF(day, c_Base_Data.PLDate ,
_Ref_Date.refdate)/365.25)as Decimal(5,1)) as Current_Age, 
   Thinningregimes.Th_ID
   
 from  (C_Base_Data join Thinningregimes  on C_Base_Data.Th_ID =
Thinningregimes.TH_id
join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID) cross
join _Ref_Date
order by 
Plantations.Region,
    Plantations.Subregion,
    Plantations.District,
    Plantations.Plantation,
    C_Base_Data.Compt

It is duplicating rows where C_Base_Data.compt are identical.
Please just have a look at it.
Thanks.

On 10 Apr 2008 14:28:56 -0700, "Frank Ploessel"
<fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:

>John,
>
>In this case, the cross join is fine.
>Another possibility would be to use a subselec in the places where you  
>want to use the valuet, i. e. to use
>
>(select "date" from my_table)
>
>including the parentheses.
>
>Frank
>
>On Thu, 10 Apr 2008 21:03:44 +0200, John Le Brasseur  
><lebrasseur@btinternet.com> wrote:
>
>> Hi all,
>> I wish to set a reference date for age calculations in various places.
>> My thought was to have a table with one column and one row, with the
>> date, say 2007/04/01(yyyy/mm/dd)
>> Then I would like to calculate ages as on that date with a computed
>> column.
>>  How does one link the tables? I tried it with a cross join on a
>> simple test table. I suppose a cross join is OK as there is only one
>> row?
>>
>>
0
John
4/12/2008 12:07:37 AM
This join condition is likely the cause of the problem:

 > join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID)

since it will be true for any non-NULL value of Ptn_ID.

I must confess that I am confused about your requirements to add an 
additional table to each (every?) one of your queries for this date 
arithmetic - adding joins to a query is never free.

Glenn

John Le Brasseur wrote:
> Sorry,
> I knew this would happen.
> I have looked at examples to solve this but my logic in the following
> statement isn't correct:
> 
> Select   
>     (select refdate from _Ref_Date),
>     Plantations.Region,
>     Plantations.Subregion,
>     Plantations.District,
>     Plantations.Plantation, 
>     C_Base_Data.Compt,
>     cast((DATEDIFF(day, c_Base_Data.PLDate ,
> _Ref_Date.refdate)/365.25)as Decimal(5,1)) as Current_Age, 
>    Thinningregimes.Th_ID
>    
>  from  (C_Base_Data join Thinningregimes  on C_Base_Data.Th_ID =
> Thinningregimes.TH_id
> join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID) cross
> join _Ref_Date
> order by 
> Plantations.Region,
>     Plantations.Subregion,
>     Plantations.District,
>     Plantations.Plantation,
>     C_Base_Data.Compt
> 
> It is duplicating rows where C_Base_Data.compt are identical.
> Please just have a look at it.
> Thanks.
> 
> On 10 Apr 2008 14:28:56 -0700, "Frank Ploessel"
> <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:
> 
>> John,
>>
>> In this case, the cross join is fine.
>> Another possibility would be to use a subselec in the places where you  
>> want to use the valuet, i. e. to use
>>
>> (select "date" from my_table)
>>
>> including the parentheses.
>>
>> Frank
>>
>> On Thu, 10 Apr 2008 21:03:44 +0200, John Le Brasseur  
>> <lebrasseur@btinternet.com> wrote:
>>
>>> Hi all,
>>> I wish to set a reference date for age calculations in various places.
>>> My thought was to have a table with one column and one row, with the
>>> date, say 2007/04/01(yyyy/mm/dd)
>>> Then I would like to calculate ages as on that date with a computed
>>> column.
>>>  How does one link the tables? I tried it with a cross join on a
>>> simple test table. I suppose a cross join is OK as there is only one
>>> row?
>>>
>>>


-- 
Glenn Paulley
Director, Engineering (Query Processing)
Sybase iAnywhere

Blog: http://iablog.sybase.com/paulley

EBF's and Patches: http://downloads.sybase.com
  choose SQL Anywhere Studio >> change 'time frame' to all

To Submit Bug Reports: http://case-express.sybase.com

SQL Anywhere Studio Supported Platforms and Support Status
http://my.sybase.com/detail?id=1002288

Whitepapers, TechDocs, and bug fixes are all available through the
Sybase iAnywhere pages at 
http://www.sybase.com/products/databasemanagement/sqlanywhere/technicalsupport
0
Glenn
4/12/2008 12:27:20 AM
Sorry, it was very late :))
That solved it.
Thanks Glen

On 11 Apr 2008 17:27:20 -0700, Glenn Paulley <paulley@ianywhere.com>
wrote:

>This join condition is likely the cause of the problem:
>
> > join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID)
>
>since it will be true for any non-NULL value of Ptn_ID.
>
>I must confess that I am confused about your requirements to add an 
>additional table to each (every?) one of your queries for this date 
>arithmetic - adding joins to a query is never free.
>
>Glenn
>
>John Le Brasseur wrote:
>> Sorry,
>> I knew this would happen.
>> I have looked at examples to solve this but my logic in the following
>> statement isn't correct:
>> 
>> Select   
>>     (select refdate from _Ref_Date),
>>     Plantations.Region,
>>     Plantations.Subregion,
>>     Plantations.District,
>>     Plantations.Plantation, 
>>     C_Base_Data.Compt,
>>     cast((DATEDIFF(day, c_Base_Data.PLDate ,
>> _Ref_Date.refdate)/365.25)as Decimal(5,1)) as Current_Age, 
>>    Thinningregimes.Th_ID
>>    
>>  from  (C_Base_Data join Thinningregimes  on C_Base_Data.Th_ID =
>> Thinningregimes.TH_id
>> join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID) cross
>> join _Ref_Date
>> order by 
>> Plantations.Region,
>>     Plantations.Subregion,
>>     Plantations.District,
>>     Plantations.Plantation,
>>     C_Base_Data.Compt
>> 
>> It is duplicating rows where C_Base_Data.compt are identical.
>> Please just have a look at it.
>> Thanks.
>> 
>> On 10 Apr 2008 14:28:56 -0700, "Frank Ploessel"
>> <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:
>> 
>>> John,
>>>
>>> In this case, the cross join is fine.
>>> Another possibility would be to use a subselec in the places where you  
>>> want to use the valuet, i. e. to use
>>>
>>> (select "date" from my_table)
>>>
>>> including the parentheses.
>>>
>>> Frank
>>>
>>> On Thu, 10 Apr 2008 21:03:44 +0200, John Le Brasseur  
>>> <lebrasseur@btinternet.com> wrote:
>>>
>>>> Hi all,
>>>> I wish to set a reference date for age calculations in various places.
>>>> My thought was to have a table with one column and one row, with the
>>>> date, say 2007/04/01(yyyy/mm/dd)
>>>> Then I would like to calculate ages as on that date with a computed
>>>> column.
>>>>  How does one link the tables? I tried it with a cross join on a
>>>> simple test table. I suppose a cross join is OK as there is only one
>>>> row?
>>>>
>>>>
0
John
4/12/2008 6:58:06 AM
Have you considered creating a variable, possibly in a login procedure, and 
referencing the variable in your queries? It would be less expensive and 
less cumbersome than a join or subquery. See CREATE VARIABLE.

Whitepapers, TechDocs, bug fixes at http://www.ianywhere.com/developer

"John Le Brasseur" <lebrasseur@btinternet.com> wrote in message 
news:s9n00496sv1sbpnpdast65gk4c40vfgaf1@4ax.com...
>
> Sorry, it was very late :))
> That solved it.
> Thanks Glen
>
> On 11 Apr 2008 17:27:20 -0700, Glenn Paulley <paulley@ianywhere.com>
> wrote:
>
>>This join condition is likely the cause of the problem:
>>
>> > join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID)
>>
>>since it will be true for any non-NULL value of Ptn_ID.
>>
>>I must confess that I am confused about your requirements to add an
>>additional table to each (every?) one of your queries for this date
>>arithmetic - adding joins to a query is never free.
>>
>>Glenn
>>
>>John Le Brasseur wrote:
>>> Sorry,
>>> I knew this would happen.
>>> I have looked at examples to solve this but my logic in the following
>>> statement isn't correct:
>>>
>>> Select
>>>     (select refdate from _Ref_Date),
>>>     Plantations.Region,
>>>     Plantations.Subregion,
>>>     Plantations.District,
>>>     Plantations.Plantation,
>>>     C_Base_Data.Compt,
>>>     cast((DATEDIFF(day, c_Base_Data.PLDate ,
>>> _Ref_Date.refdate)/365.25)as Decimal(5,1)) as Current_Age,
>>>    Thinningregimes.Th_ID
>>>
>>>  from  (C_Base_Data join Thinningregimes  on C_Base_Data.Th_ID =
>>> Thinningregimes.TH_id
>>> join Plantations on C_Base_Data.Ptn_ID = C_Base_Data.Ptn_ID) cross
>>> join _Ref_Date
>>> order by
>>> Plantations.Region,
>>>     Plantations.Subregion,
>>>     Plantations.District,
>>>     Plantations.Plantation,
>>>     C_Base_Data.Compt
>>>
>>> It is duplicating rows where C_Base_Data.compt are identical.
>>> Please just have a look at it.
>>> Thanks.
>>>
>>> On 10 Apr 2008 14:28:56 -0700, "Frank Ploessel"
>>> <fpl...@d_e.i_m_s_h_e_a_l_t_h.c_o_m> wrote:
>>>
>>>> John,
>>>>
>>>> In this case, the cross join is fine.
>>>> Another possibility would be to use a subselec in the places where you
>>>> want to use the valuet, i. e. to use
>>>>
>>>> (select "date" from my_table)
>>>>
>>>> including the parentheses.
>>>>
>>>> Frank
>>>>
>>>> On Thu, 10 Apr 2008 21:03:44 +0200, John Le Brasseur
>>>> <lebrasseur@btinternet.com> wrote:
>>>>
>>>>> Hi all,
>>>>> I wish to set a reference date for age calculations in various places.
>>>>> My thought was to have a table with one column and one row, with the
>>>>> date, say 2007/04/01(yyyy/mm/dd)
>>>>> Then I would like to calculate ages as on that date with a computed
>>>>> column.
>>>>>  How does one link the tables? I tried it with a cross join on a
>>>>> simple test table. I suppose a cross join is OK as there is only one
>>>>> row?
>>>>>
>>>>> 


0
Bruce
4/14/2008 2:07:31 PM
Reply:

Similar Artilces:

SQL: mutiple rows one column to multiple columns one row
In InfoMaker SQL can you take a table with multiple rows for the same key and take a column from the rows and create mutiple columns with one report line with the key(no duplicate keys) on a report? For example Table books (key) (book title) 123 misery 123 titanic 123 roots 456 war 456 1984 456 giant The report I need would look like this Key title1 title2 title 3 123 misery titanic roots 456 war 1984 giant Thanks in advance. -- Kaye Hendry HealthInsight email:kaye.hendry@healthinsight.org ...

selecting one row and inserting it into another table one by one
i ve a table named "Employee" in wh i ve fields "Emp_Id","Name" and "Email". i want to select each employee "Emp_Id" one by one from "Employee" table and insert it into another table named "Concerned_Department". like if i ve three rows in "Employee" table with "Emp_Id" 1,2 and 3 respectively,in this case i want to first select "Emp_Id"=1 then "Emp_id"=2 and lastly "Emp_id"=3 and insert them into "Concerned_Department"table.  after that the process star...

insert multiple values from one table into one column from another table?
Hi, I'm attempting to create an insert statement that gets the values from multiple columns and insert them into one column from another table, is there a simple solution to do this?Here is something similar to what i'm wanting done, just it's not the correct way to do it, i want value1, value2 and value3 from one table to be combined and put into the value1 column of another table, what am i doing wrong? INSERT INTO tablename2 (value1 + value2 + value3)SELECT value1FROM tablename1 thanks in advance! INSERT INTO tablename2 (SELECT val1 + val2 + val3 FROM tabl...

Accessing one column in one row
Every thing I have researched on accessing data is to put it into a multi row and multi column control.  If you are already using a sqldatasource to fill a listbox, how can you use the same sqldatasource to obtain a single column out of a single row in the same database?  The rows contain an identy column and only one row in the database has a specific identy.  The row has three columns.  The first column is "nvchar" and I want to put its contents into a textbox.text.  I tried setting the select parameter to the desired identy ...

select all row one more than one column
   I add checkbox control in header template to select all row. if one column have select all propert there is no problem. However, when add second column and add select all another column there occur a problem. When ı select first column select all checkbox, second colum checkbox checked. How can ı solve this problem.          Hi,Following links will help you out ...http://programming.top54u.com/post/ASP-Net-GridView-Checkbox-Select-All-using-C-sharp.aspxhttp://aspnet.4guysfromrolla.com/articles/053106-1.aspxhttp://aspnet.4guy...

Update a rows in one table for each row in other table
Hello all, i am a newbie in SQL and i want to ask for your help in order to do the following update statement. I have a table tblUsers which holds all the users, one of the fields in their GroupId. In the UserGroup table i have a field "UsersCount" and in that field i want to keep the number of the users in that group. Basically i want everytime i am saving a user to recalculate the users in all usergroups. I am trying to have something like:SELECT User_GroupId, count(User_GroupId) as UsersCount FROM tblUsersWHERE DeletedFlag = 0 GROUP BY User_GroupId and then:"FOR EACH" User_GroupIdUPDAT...

Reading table row one by one on page submit.
I m new in asp.net technology. I m creating one simple application in which i have just one button and one lable on webform. I have one table in database having name of person and adress fields. I want to use it in such a way that when first time page will be loaded it will show button and lable will be blank.when i click the button it should display name in  lable field.Next time when i click on button it will display second name and so on. Here on every click on button.. page is submitted. I m finding it more complicated.. Pls Help.....

One-to-One or Many-to-One?
 Hi Friends,I've made huge database for Learning Management Systems which has around 30 - 40 tables.I was checking all the relationships between tables and everything seems fine and well defined. But, it's 4:22 AM here and I'm bit sleepy but I was thinking which of the following relationship is true between the following two tables:Table1: tbl_users (PK username, password, name, surname, email, address1, address2, phNumb)Table2: tbl_testAttempts (PK Id, username, testId, attempt, timeStart, timeEnd)As you see, the Primary key of tbl_users is foreign key in tbl_testAttempts. ...

One to Many to One to One
I have a kinda small problem; if someone could help me that' would be niceTo keep things simplier, allow me to give the following exampleI have two tables one-to-many relationship,table1: itemtable2: itemhistoryitem has many itemhistorybasically i want retrieve all the records in 'item' sorted by 'name', along with ONE "latest" itemhistory" (date). If there aren't any historyhistory for a particular 'item', just allow null values. egcreate table #item(itemid int, name varchar(10))create table #itemhistory(itemhistoryid int, itemid int, date...

Problem in retrieving data from two table when one column entry in each table are same but no column in these table match with each other
Hello there!,       First of all i have two tables . one acts as a master table and other one acts as a details table. Currently the ptoblem with my procedure is, i have entered two entries for one project in the Master table. and to briefly discribe those two entries in the master table , i have entered two entries in the Details table corresponding to the entries in the master table. Now i need to combine the master table entries and the details table entries for the same project which should give only two entreies. but my procedure gives four entries. Could yo...

populating a column in one table with data in a column from another table?
Hi,I have one main table and multiple smaller tables, the main table consists of all the information gathered from the smaller tables, plus some other data. I've set primary keys for all the smaller tables and created a relationship where the primary key id matches an id field inside the main table eg. there's a smalltableid column in both the smaller table and the main table.some misc info:i'm using a dataset (should the key in the dataset be pointing to the main table, or the smaller one?)both relation and foreign key constraints are created (with no rules set)my problem is, the data from ...

One Table on Remote
Is it better to have logic in SYNC scripts, or have seperate SYNC scripts for two different types of user sets. We have type A worker and type B worker that need different data filtered down into remote DB. All information comes from same consolidated DB. That depends on your schema design. Do the different types of users share the same schema? If so, you want to do horizontal slicing (filtering) with table scripts using the remote ID, or other authentication parameters. If they don't share the same tables, but use different tables, either redesign your database, or us...

How to read a dataset one row by one row?
I know you can use DataSet after close sql connection. I want to something after getting dataset, read from one row by one row, just like while datareader.read() Can I read dataset just like this? Thanks in advance! Justin Is this what you're looking for (from the Tips and Tricks Section of ASPNet101.com):http://aspnet101.com/aspnet101/tips.aspx?id=175David WierMCP/ASPInsiderASPNet101.com - where to look first!Control Grouper - easily control properties for multiple controls with one control!Calendar Express - The Best HTML Calendar Generator on the web!(Please 'Mark as Answer&...

Deleting rows from one table based upon rows in another table
I need to delete rows from table1 based on rows in table2. table1 has 1.5 million rows from which I need to delete 20,000 existing in table2. The table structures are the same and I must exactly match 10 (of the 15) columns in the tables. I also need to limit the number of rows being worked on, in a single transaction, to 500 or so. Hi Mike, try the following: set rowcount 500 DELETE table1 from table1 t1,table2 t2 where t1.col1=t2.col1 and t1.col2=t2.col2 and t1.col3=t2.col3 and ........ t1.col10=t2.col10 HTH. Regards P:S You must re-execute the DML statement above 40 time...

Web resources about - Refering to a table with one column and one row - sybase.sqlanywhere.general

Astronaut Pete Conrad's autograph to my dad - Flickr - Photo Sharing!
On the 25th anniversary of Apollo 11 in 1994, my dad got a magazine article about Pete Conrad signed by the astronaut. My dad worked on the astronaut's ...

Talk:Applet - Wikipedia, the free encyclopedia
This article is within the scope of WikiProject Computing , a collaborative effort to improve the coverage of computers , computing , and information ...

Pop goes the music games
Pop goes the music games

Iran: Ahmadinejad is 'Socrates of third millenium', says president's office - Adnkronos Politics
Iran: Ahmadinejad is 'Socrates of third millenium', says president's office

CMI - AfterDawn: Glossary of technology terms & acronyms
... - Tech Support - Forums - Profiles Home Glossary AfterDawn > Glossary > CMI CMI CMI is a term that means Copy Management Information, refering ...

AMERICAblog
skip to main skip to sidebar About us - Elections - Gay GOP Primary Schedule - Elections - Romney Economic Crisis - Jobs - TSA - Limbaugh - Fun ...

Coronation Traditions in Imperial Ethiopia
The Emperor of Ethiopia was crowned by the Archbishop of the Ethiopian Church appointed by the Patriarch of the Coptic Orthodox Church, the Pope ...

The Modern Financial Asset Pyramid
The lower you are on the pyramid the better off you are. As Max Keiser says, refering to derivatives and exotic paper, they've made cotton candy ...

Gary Sharma’s Highlights from F.ounders NY 2012, the Michael Jordan of Tech Events
... now is on retention" said Rick. They're planning to roll out a new user interface in a couple of weeks. "I've made my money." he said refering ...

Windows 8 from a gas engine to hybrid!
... to developing primarily for the desktop. I, like an old-time mechanic, do feel rightly concerned when I hear terms like "legacy" when refering ...

Resources last updated: 1/1/2016 7:36:52 PM