Member Avatar for doraemon

I am tring to recreate my oracle database -> sql server 2008. I never used sql server before, so I am using sql server management studio. I could create tables and views. The views I created using sql server management studio GUI has data in it.
Now, I tried to use a script to create a view. The view was created but no data in it... I am sure that I'm missing some basic things.
Anybody please tell me what am I missing?

USE[DB101] 
GO
CREATE VIEW TEST 
AS
SELECT     dbo.EMPLOYEE.EMPID, dbo.EMPLOYEE.LASTNAME, dbo.EMPLOYEE.MIDDLE, dbo.EMPLOYEE.FIRSTNAME, dbo.EMPLOYEE.STREET, 
                      dbo.EMPLOYEE.CITY, dbo.EMPLOYEE.STATE, dbo.EMPLOYEE.ZIP, dbo.EMPLOYEE.TERMS, dbo.EMPLOYEE.RATE, dbo.EMPLOYEE.QTY, 
                      dbo.EMPLOYEE.VAC_RATE, dbo.EMPLOYEE.QTY_PAID_VAC, dbo.EMPLOYEE.QTY_OVER_TIME, dbo.EMPLOYEE.[GROSS-PAY], 
                      dbo.EMPLOYEE.IDTAX, dbo.TAX.TAX_TOTAL, dbo.EMPLOYEE.OT_RATE, dbo.EMPLOYEE.NET_PAY
FROM         dbo.EMPLOYEE INNER JOIN
                      dbo.TAX ON dbo.EMPLOYEE.IDTAX = dbo.TAX.IDTAX
GO

Regards,

  • 2 Contributors
  • 4 Replies
  • 252 Views
  • 5 Days Discussion Span
  • Latest Post Latest Post by Member #949455

Dani AI

Generated

Nice catch — the symptom (GUI-created view returned rows but the scripted view did not) is a classic join/context problem, and switching to an outer join is a common fix when some left-side rows lack matching right-side rows. Below are practical debugging steps and short migration notes that complement the thread.

  • Quick checklist to isolate the problem:

    • Confirm base data exists: SELECT COUNT(*) FROM left_table; and SELECT COUNT(*) FROM right_table;.
    • Run the view's SELECT as a one-off query (not the CREATE VIEW) to see what rows are returned and which are filtered out.
    • Compare INNER JOIN vs LEFT JOIN to spot missing matches (LEFT keeps all left_table rows; INNER requires matches on both sides).
    • Check join-key compatibility: data types, collation, trailing spaces (CHAR padding) and implicit conversions that can prevent matches.
    • Verify the view definition and schema context: use sp_helptext 'schema.viewname' or SELECT OBJECT_DEFINITION(OBJECT_ID('schema.viewname')); to confirm the SQL that was actually created.
    • If underlying objects changed after creating the view, run sp_refreshview 'schema.viewname'.
    • Confirm permissions and the database/schema in which the script executed; GUI tools sometimes create objects under a different default schema.

  • Small examples to try quickly:

    -- see rows missing on the right-side match
SELECT L.* 
FROM left_table L
LEFT JOIN right_table R ON L.key = R.key
WHERE R.key IS NULL;

  • Oracle-to-SQL Server notes for migrations:

    • Watch data-type mappings (NUMBER, VARCHAR2, NVARCHAR2, DATE -> decimal, varchar/nvarchar, datetime2).
    • Replace NVL with ISNULL or COALESCE; SUBSTR -> SUBSTRING; SYSDATE -> GETDATE.
    • Be mindful of Oracle CHAR semantics (space padding) versus SQL Server VARCHAR.
    • Use ANSI JOIN syntax in translations (it is clearer and portable).

Performance/caution: large views with multiple outer joins can be expensive; test the standalone query plans and consider materializing results (ETL table) if the view is repeatedly queried. Thanks to for the migration-resource pointer earlier in the thread.

Recommended Answers

All 4 Replies

Member Avatar for doraemon

Thank you for your reply.
I will try.

Member Avatar for doraemon

I hate to say this but it was my mistake using inner join. I had to use left outer join in order to create this view....I'm sorry.

Member Avatar for Member #949455

I hate to say this but it was my mistake using inner join. I had to use left outer join in order to create this view....I'm sorry.

I don't have a database at home to test the query you have. The query you created looks right. It's all about using which word (INNER JOIN or OUTER JOIN) to make the query work. I'm glad you solve the issue and figuring out what word (INNER JOIN or OUTER JOIN) to used for the query to work the way you wanted.

Edited by Member #949455 because: grammer
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.