1

I am using MS SQL Server 2008 using Management Studio. I am running a query(below) to extract dates and value based on the 'ticker' column from the parent table, the attribute column (child) , and dates (child) within a date range.

When I run the query, I get no values returned, but when I use primary key (param_id) from the parent or child table, I get the values returned that I want. However, I would like to use the column "ticker" in the parent table as the reference in the where clause.

I have 2 tables.

  1. Parent Table PK - param_id - int
    FK - fed_id - int (references different fed reserve ids from other table)
    description - varchar(50)
    ticker - varchar(50)

  2. Child Table - holds the data
    (PK, FK) param_id - int
    (PK, FK) attrib_id - int (references different attributes from attribute table)
    (PK) [update] - datetime
    value - decimal(19, 6)

SQL Query below:

  SELECT [update], [value]   
  FROM m_econ_fred_source 
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id   
  WHERE m_econ_fred_source.ticker = 'PHILINFLEXP3M' AND
        attrib_id = 7 AND   
        [update] BETWEEN '2014-01-01' AND '2016-03-01'  
  ORDER BY [update] ASC

If I do this, it works:

  SELECT [update], [value]  
  FROM m_econ_fred_source 
  JOIN s_econ_fred on m_econ_fred_source.param_id = s_econ_fred.param_id   
  WHERE m_econ_fred_source.param_id = 2 AND
        attrib_id = 7 AND   
        [update] BETWEEN '2014-01-01' AND '2016-03-01'  
  ORDER BY [update] ASC

I have also made the ticker column unique as well to see if that would help to no avail. I also created the tables using the wizard instead of create statements.

Would anyone be able to tell me what may be wrong with my query or probable causes/solutions to my problem? Thank you. I have looked everywhere for an answer to this problem. I was unable to find a problem similar to this.

  • Add ticker column to your SELECT statement, then execute the second query. Do you see values "PHILINFLEXP3M" in the result set? – Greg Jun 20 '16 at 14:42
  • Yes, I see the values. The ticker column, 'PHILINFLEXP3M', is now included, but I do not want the ticker column. If I use the ticker in the where clause, it still does not work. – jbosch Jun 20 '16 at 15:34
  • If the param_id filter gets the row you want but the ticker filter doesn't, then the row simply doesn't have the ticker value you are filtering on. Perhaps the value looks very similar but still different or there are some hidden characters in it. Or perhaps the value is stored in lower case and the column's collation is case-sensitive (so matching against an all-upper-case string would be the same as matching against a different value). – Andriy M Jun 20 '16 at 16:05
  • The column's collation is 'database default' (for the ticker column, the collation says 'SQL_Latin1_General_CP1_CI_AS'. I copied the ticker row from its table to be put in the query. I imported the information from excel for the table, but I am not aware of any hidden characters in it. [Update] - the query now randomly seems to work in SQL Management Studio, but when I try using python pyodbc library, I now have the same problem as before. I have run queries on different tables with the python odbc and they worked. Only for these two tables, which I just created, are not working. – jbosch Jun 20 '16 at 16:27
  • [Update 2] - I added the parent.param_id =child_param_id in the where clause and removed the ticker column from the Select query and the query ran successful in python with pyodbc. Problem solved, but would you or anyone know why that happened? – jbosch Jun 20 '16 at 16:34
1

Thank you @Greg for answering the question. The problem required that the SQL statement fully qualify the columns in the where clause by referencing the tables for the columns. In this case. I needed m_econ_fred_source=attrib_id.

The complete solution is:

SELECT [update], [value] 
FROM m_econ_fred_source JOIN s_econ_fred 
ON m_econ_fred_source.param_id = s_econ_fred.param_id   
WHERE m_econ_fred_source.param_id = 2 AND
      m_econ_fred_source.attrib_id = 7 AND   
      [update] BETWEEN '2014-01-01' AND '2016-03-01'  
ORDER BY [update] ASC

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.